Blog app: full text search

Published on Friday 29 April 2016. Tagged as CSSPHP.

I've opted for a separate search page instead of an all present search box. This is my first implementation of a full text search using MySql and the syntax proved to be quite a challange.

This is a great introduction to MySql full text search: mysql-full-text-search-functions. Since I'm using my MVC system, adding the search functionality has to pass through every aspect: controller, model and view. In this case, also a CSS addition, a new SVG icon and a config change are required, even a change to the MySql database itself. Every step has to be correct.

Preliminary changes

In order to enable full text search, the database itself has to be changed. I will use only the posts table and search through the fields 'subject', 'description' and 'body'. Note: older versions of MySql only accept the full text search syntax on MyIsam tables:

ALTER TABLE posts ADD FULLTEXT( subject, description, body );

Next, the search page has to be added to the menu in content/config.php:

'menu'=>array(
	'index'=>array('link'=>'/', 'label'=>'<svg><use xlink:href="{{path}}/inc/icons.svg#home-icon"/></svg>Home'),
	'search'=>array('link'=>'/search', 'label'=>'Search'),
	'about'=>array('link'=>'/page/about', 'label'=>'About')
),

The controller change

To process the search page, a new method getSearch has to be added. The search itself will use an HTML form to capture the search term, but it's method will be set to GET rather than POST. Unfortunately, my solution for friendly URLs gobbles up any query parameters. Luckily, PHP allows for the request URL to be retrieved before it is transformed, so I can manually extract the 'for' search term:

function getSearch(){
	$url=$_SERVER['REQUEST_URI'];
	$searchTerm=substr( $url, stripos( $url.'?for=', '?for=' )+5 );

If a search term is present, $obj is filled with a list of results and its pagination, asked from the model. If there's no search term yet, $obj is set to an array with dummy variables. Finally, the 'data' object is updated:

	$obj=( $searchTerm>'' )?$this->model->getSearch( $searchTerm, (int) $this->data['request'][2] ):array( 'list'=>array(), 'pagination'=>null );
	$this->extendData( array( 'type'=>'search', 'title'=>'Search', 'searchTerm'=>$searchTerm, 'content'=>$obj['list'], 'pagination'=>$obj['pagination']  ) );	
}

The model change

There are 2 types of full text search in MySql: IN NATURAL LANGUAGE MODE and IN BOOLEAN MODE. The drawback of the first is that matches that occur in more than 50% of the rows are considered stopwords and these rows are omitted from the results. So I opted for IN BOOLEAN MODE. Because the match syntax has to be repeated 3 times, I've put it in a separate string variable. With the pagination code added, the getSearch method is a variant of getIndex:

function getSearch( $term, $thisPage, $perPage=10 ){
	$match='MATCH ( subject, description, body ) AGAINST ( :term IN BOOLEAN MODE )';
	$r=$this->db->prepare( 'SELECT COUNT(*) FROM posts WHERE '.$match );
	$r->execute( array( ':term'=>$term ) );
	$count=$r->fetch()[0];
	$pagination=$this->getPagination( $thisPage, $perPage, $count );
	$r=$this->db->prepare( 'SELECT slug, subject, publishdate, description, body, '.$match.' AS score FROM posts WHERE '.$match.' ORDER BY score DESC'.$pagination['queryAdd'] );
	$r->execute( array( ':term'=>$term ) );
	return array( 'list'=>$r->fetchAll( PDO::FETCH_ASSOC ), 'pagination'=>$pagination );
}

The view change

Similar to the model changes, the renderSearch method is a variant of the renderIndex method. It just adds a form to capture the search term:

function renderSearch(){
	extract( $this->data );
	out('<form method="get" class="search">');
	out('<input name="for" value="'.$this->data['searchTerm'].'">');
	out('<button type="submit"><svg><use xlink:href="'.ROOT.'/inc/icons.svg#search-icon"/></svg></button>');
	out('</form>');
	if($searchTerm=='') return;
	if( sizeof( $content )==0 ) return out('<p>No items found.</p>');
	out( '<ul class="list">' );
	foreach( $content as $item ) $this->renderItem( $item );
	out( '</ul>' );
	$this->renderPagination( $pagination, '/search/' );
}

The CSS change

Apart from the SVG to display the magnifying glass icon, the search form has to be styled, so I added a few lines to inc/style.css:

/* search */
.search{margin-bottom:20px}
.search input,.search button{display:inline-block;font-family:inherit;font-size:inherit;padding:15px;vertical-align:middle}
.search button{padding:5px}
.search svg{height:32px;width:32px}
The Blog app project on GitHub