SQL PHP search asking for at least one word

Question

I am trying to make a search form with php, using the following query :

$this->images('`description`="'.db::escape($mysearch).'"');

It's working great, BUT it returns only the EXACT description that corresponds to $mysearch ! I would like that it returns all the descriptions that contains the keywords (1, 2 or 3) from the $mysearch field. What is the exact SQL query ? I tried with :

'MATCH `description` AGAINST "'.db::escape($mysearch).'"'

But I get an error back:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MATCH title AGAINST "mykeyword"' at line 1

The solution was :

'MATCH(title) AGAINST ("'.db::escape($keyword).'" IN BOOLEAN MODE)'

Answer 1

use explode: http://php.net/manual/en/function.explode.php on a space or comma as your delimiter, and then do a for each on the resulting array to add a line to the query for each string in that array.

For a pseudo example:

//input of the string
$input = "keyword1 keyword2 keyword3";
$inputArray = explode(" ",$input);
foreach ($inputArray as $keyword) {
  $sql .= " OR tableName.ColName like '%$keyword%'";
}

obviously clean it up and validate the input, preferable use prepared statements etc (looks like you are using some sort of ORM?), and hopefully the description column is relatively short and has an index on its full content, otherwise the query will be very slow.

Update: I realize you were attempting the syntax to use a fulltext index. If you have a MyISAM table, or a new enough version of mysql that supports fulltext indexes on InnoDB tables, then you might want to skip this solution I have offered as it WILL become very slow once you have more records in teh table than will fit into memory / cache. Unfortunately, I have never used a fulltext index and cannot help you there. I am switching to using Zend_Search_Lucene vs the solution I listed above. (http://framework.zend.com/manual/en/zend.search.lucene.html)