Full-Text Search

CakePHP’s database abstraction is just one of the reasons to use this powerful framework for agile web development. However, it is not always obvious how to build complex queries. I recently had to implement a MySQL full text search against a music database that searched on titles, descriptions and tags. Full text search is a quick, and not too dirty way to make a pretty decent search, although it does come with a caveat, as described in the MySQL documentation:

    Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXTcolumns.

It did take a little bit of experimentation to get the syntax for the query correct in CakePHP as the query is a little different from the regular “SELECT field FROM table WHERE somevalue = somevalue“.

So, for this example I will use a MyISAM table called pages and search against the title, content and tags fields for the term” jquery web development”. You should substitute that term with your dynamically passed value in the code below.

Firstly I built the conditions for my query into a “params” array.
$params['conditions'] = array(
‘MATCH(Track.title,Track.description,Track.tags)
AGAINST(“‘ . $kw_list . ‘” IN BOOLEAN MODE)’
);

    $params= array(‘conditions’ => array(
    ‘MATCH(Page.title,Page.description,Page.tags)
    AGAINST(“jquery web development” )’ ));

Finally a call to CakePHP’s native find method will perform the search:

    $this->Page->find(‘all’,$params);

One last note: MySQL full text search can be powerful when used correctly and a potential bottleneck when poorly implemented. I suggest reading the documentation for further information and more advanced usage.