This site is my playground and I'm getting a kick out of building on it every day. Last night I made a search feature and showed Tim. He asked me about how it's done and then referred to me as "very data programmer" when I said I use stored procedures. :) I'll take it as a compliment. (I later learned about "static site generators" which is what his is and that's why he was wondering. His site has no database! What a concept!)

I'll give you an outline of how I'm doing the search here. Although the Smarty template system integration is new (I like Smarty; it just works), some classes are still using code I wrote over a decade ago.

First I create the stored procedure in the database. Along with search, this stored procedure will take care of displaying the entries as well as filtering and paging records, etc.

CREATE PROCEDURE `up_GetLog2Entries`(EID VARCHAR(255), OST INT, LMT INT, iST VARCHAR(25))
BEGIN
	SET @o = OST * LMT;
	SET @l = LMT;
	SET @s = iST;
	IF iST != '' THEN
		-- commented out the LIMIT clause to show all entries (paging disabled on the site, to mimic Tim's template. Might put back later.
		SET @sql = CONCAT("SELECT * FROM vw_GetLog2Entries WHERE MATCH(Body,Title) AGAINST('",@s,"*' IN BOOLEAN MODE)"); -- LIMIT ",@o,",",@l);
		PREPARE qs FROM @sql;
		EXECUTE qs;
		DROP PREPARE qs;
	ELSE
		IF EID = '' THEN
			IF @o = 0 and @l = 0 THEN -- once again, no limit clause; show all entries
				PREPARE qs FROM 'SELECT * FROM vw_GetLog2Entries';
              			EXECUTE qs;
      			ELSE 
				PREPARE qs FROM 'SELECT * FROM vw_GetLog2Entries LIMIT ?,?'; -- set limit and offset, ie: front page shows top 5 entries
				EXECUTE qs USING @o,@l;
			END IF;
			DROP PREPARE qs;
		ELSE
			SELECT * FROM vw_GetLog2Entries WHERE `Key` = EID OR PermaLink = EID; -- single entry
		END IF;
	END IF;
END

In PHP we start with an SQLWrapper class which sets up the connection and prepares the procedure caller. (This is the entire file, you're welcome to it.)

class SQLWrapper 
{
    private $mysqli;
    private $rs;
	protected $params = array();
    
    function __construct() {
        $this->mysqli = @new mysqli ("host","user","pass","database");
        if (mysqli_connect_errno()) {
			printf("Yes, I'm missing one database. Return it immediately!");
			exit();
        }
		$this->mysqli->set_charset('utf8');
	}

	function __destruct() {
		@$this->mysqli->close();
	}
	
	public function AddParameter($param) {
		$this->params[] = $param;
	}
	
	public function RunDBProcedure($p,$force_quote=false) {
		$pnum = 0;
		$ptot = count($this->params);
		$proc = "SET NAMES UTF8; ";
		$proc .= "CALL " . $p . "(";

		if ($ptot) {
			foreach ($this->params as $key=>$val) {
				if (!is_numeric($val) || $force_quote === true) {
					$quote = chr(34);
				}
				$v = (get_magic_quotes_gpc()==1?$val:addslashes($val));
				$proc .= $quote . $v . $quote;
				if ($pnum != $ptot-1)
				{
					$proc .= ",";
				}
				$pnum++;
			}
		}
		$proc .= ");";
				
		$arr = array();
		
		if ($this->mysqli->multi_query($proc)) {
			do {
				if ($result = $this->mysqli->store_result()) {
					while ($row = $result->fetch_array()) {
						$arr[] = $row;
					}
					$result->close();
				}
			} while ($this->mysqli->more_results() && $this->mysqli->next_result());
		}
		$this->params = array();
		return $arr;
	}
}

Next up, the Log class that uses the SQLWrapper to make calls to the database to retrieve the entries. Most of this class is reused from my old log.

require_once "SQLWrapper.class.php";

class Log
{
	
	/* PRIVATE VARS */
	private $title = "";
	private $base;
	private $sql = "";

	function __construct() {
		$this->base = "/writing";
		$this->sql = new SQLWrapper();
	}

	/* RETREIVE METHODS */
	function get($id='',$page=1,$limit=20,$searchterm='') {
		$this->sql->AddParameter(($id==0?'':$id));
		$this->sql->AddParameter($page-1);
		$this->sql->AddParameter($limit);
		$this->sql->AddParameter($searchterm);
		return $this->sql->RunDBProcedure("up_GetLog2Entries");
	}

There are additional functions in there to save, delete, add entries, etc., which I won't bore you with.

Next, in index.php I call the Log class and the template engine.

require_once "Smarty.class.php";
require_once "Log.class.php";

$smarty = new Smarty();
$log = new Log();

$search_term = strlen($_GET['q'])>2 ? $_GET['q'] : "";

/* WRITING */
if (isset($_GET['writing'])) {
	$smarty->assign("page","writing");
	/* SINGLE ENTRY */
	if (isset($_GET['id'])) {
		if ($search_term)
			$smarty->assign("search_term",$search_term);
		$smarty->assign("next_link",$log->get_next($_GET['id']));
		$smarty->assign("prev_link",$log->get_prev($_GET['id']));
		$smarty->assign("single",1);
		$entries = $log->get($_GET['id']);
		$smarty->assign("site_title", $entries[0]['Title'] . " - " . $page->title());
	/* LIST ENTRIES */
	} else {
		if ($search_term) {
			$smarty->assign("search_term",$search_term);
			$smarty->assign("site_title","Entries containing '" . $search_term . "' - " . $page->title());
		}
		$entries = $log->get('',0,0,$search_term);
	}
	$smarty->assign("entries_array",$entries);

/* so on and so forth */

Finally, the template file to display it all, which is a simple Smarty {section} to loop through the entries.

{if $search_term}
<p class='text-success mt-2'>Entries containing '{$search_term}'</p>
{/if}
{section name=sec1 loop=$entries_array}
<div class='mt-3'><b><a href='/writing/{$entries_array[sec1]['PermaLink']}{if $search_term}/{$search_term}{/if}'>{$entries_array[sec1]['Title']}</a></b> · {$entries_array[sec1]['TimeAdded']|relative_time}</div>
{/section}

That concludes the walk-through of how I do the search on this site. :) Drop me a line if you want to share your thoughts or comments.