Separating keywords by space and searching MySQL database

Question

I am working with PHP and MySQL. I'll provide a simple table below:

------------------------------------------------------
|  id |                    text                      |
------------------------------------------------------
|  1  | The quick brown fox jumped over the lazy dog | 
------------------------------------------------------

I'd like users to be able to search using keywords separated by spaces. I've got a simple SQL query for the above which is SELECT * FROM table WHERE text LIKE %[$keyword]% which means if I search for "The quick fox", I won't get any results. Is there a way to separate the keywords by spaces so if I search for "The quick fox", it will run 3 searches. One for "The", one for "quick", and one for "fox" - removing all white spaces. Though it should only display one result since it all belongs to the same row instead of 3 since all 3 keywords matched the data in the row.

What's the best way to do this? All suggestions to do this better are always welcome. Thanks!

[EDIT]

Just thought of this now, would separating the keywords by comma (,) be a better option?

Answer 1

You might consider a regular expression via REGEXP to separate the words into an or group.

SELECT * 
FROM tbl
WHERE
  LOWER(`text`) REGEXP '\b(the|quick|fox)\b'

Matches:

• The quick brown fox jumps over the lazy dog
• Quick, get the fox!
• I ate the cake

Doesn't match

• Brown dogs

Inside PHP, you can construct this expression by splitting your search string on the spaces and imploding it back on | after escaping each component.

$str = "the quick brown fox";
$kwds = explode(" ", $str);
$kwds = array_map("mysql_real_escape_string", $kwds);
$regexp = "\b(" . implode("|", $kwds) . ")\b";

Then use REGEXP '$regexp' in your statement.

Addendum:

Since you didn't mention it in the OP, I want to be sure you aware of MySQL's full text searching capabilities on MyISAM tables, in case it can meet your need. From your description, full text doesn't sound like exactly your requirement, but you should review it as a possibility: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html