Splitting keywords by space and searching MySQL database

I am working with PHP and MySQL. Below is a simple table:

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


I would like users to be able to search for keywords separated by spaces. I have a simple SQL query for the above which is SELECT * FROM table WHERE text LIKE %[$keyword]%

, which means that if I search for "Bull-fox" I won't get any results. Is there a way to separate the keywords with spaces, so if I search for "Bull-fox" it will run 3 searches. One for "The", one for "quick" and one for "fox" - removing all white spaces. Although it should only display one result as it belongs to the same row instead of 3, since all 3 keywords match the data in the row.

What's the best way to do this? Any suggestions to make it better are always welcome. Thank!


Just thinking about it, would you separate the keywords by comma (,) the best option?


source to share

2 answers

You can think of regular expressions throughREGEXP

to separate the words in a group or a group.

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



  • The swift brown fox jumps over the lazy dog.
  • Quickly, get the fox!
  • I ate cake

Does not match

  • Brown dogs

Inside PHP, you can build this expression by splitting the search string into spaces and returning it back to |

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 application.


Since you didn't mention this in the OP, I want to make sure you know the MySQL full-text search capabilities of MyISAM tables if it might suit your needs. From your description, the full text doesn't sound like your requirement, but you should consider it as a possibility: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html



I did it with a different approach to query string concatenation.

// this string contains four words that are used for searching. $ str = "quick brown fox"

// the query string is written in small chunks stored in the "$ uquery" variable
$ uquery = "select * from tablename where";

// the new variable "$ keywords" will contain values ​​as an array
$ keywords = explode ("", $ str)
$ keywords = array_map ("mysql_real_escape_string", $ keywords);

// "$ k" will store the total. the elements in the array must contain "4" for this example
$ k = count ($ keywords);

// variable definition "$ i" is equal to zero
$ i = 0;

// start a while loop which will continue 4 times since in this example we only have 4 keywords

while ($ i <= $ k) {

// another new "$ uquery_" variable appears here, the next part will be concatenated with the previous "$ uquery" chunk later on
$ uquery_. = "theme like convert (_utf8 '% $ keywords [$ i]%' USING latin1)";

// for a more detailed explanation, let me tell you how "$ keywords [$ i]" will help in the above code snippet, for each value of "$ i" it will extract a keyword from an array, see below: Vacation Rentals / / $ keywords [0] = "the" for $ i = 0
// $ keywords [1] = "fast" for $ i = 1
// $ keywords [2] = "brown" for $ i = 2
// $ keywords [3] = "fox" for $ i = 3

// now concatenate the boolean && operator after each loop into "$ uquery_", but not for the last round, since the query must be completed after the last keyword
if ($ i! = $ k) {$ uquery_. = "& &"; }

// add from 1 to $ i after each round $ i ++; }

// now our main variable "$ uquery" is concatenated with "$ uquery_"
$ uquery. = "$ uquery _";

The above code snippet will generate the following query:

select * from tablename where topic like convert (_utf8 '% the%' USING latin1) && & && & topic like convert (_utf8 '% quick%' USING latin1) && & & topic like convert (_utf8 '% brown%' USING latin1) && & a theme like convert (_utf8 '% fox%' USING latin1)

Note. "subject" should be the name of a column in the mysql table, you can replace it with the column name as defined in your table.

Hope this helps a few people. If you have any questions, feel free to ask me through the Live Chat feature on my website http://www.79xperts.com


Adnan Said



All Articles