Mysql LIKE clause and separate words in a field

Question

I currently use a mysql statement like the one below to search post titles.

select * from table where title like %search_term%

But problem is, if the title were like: Acme launches 5 pound burger and a user searched for Acme, it'll return a result. But if a user searched for Acme burger or Acme 5 pound, it'll return nothing.

Is there a way to get it to return results when a users searches for more than one word? Is LIKE the correct thing to use here or is there something else that can be used?

Answer 1

You could use a REGEXP to match any of the words in your search string:

select *
from tbl
where
  title REGEXP CONCAT('[[:<:]](', REPLACE('Acme burger', ' ', '|'), ')[[:>:]]')

Please notice that this will not be very efficient. See fiddle here.

If you need to match every word in your string, you could use a query like this:

select *
from tbl
where
  title REGEXP CONCAT('[[:<:]]', REPLACE('Acme burger', ' ', '[[:>:]].*[[:<:]]'), '[[:>:]]')

Fiddle here. But words have to be in the correct order (es. 'Acme burger' will match, 'burger Acme' won't). There's a REGEXP to match every word in any order, but it is not supported by MySql, unless you install an UDF that supports Perl regexp.

Answer 2

To search for a string against a text collection use MATCH() and AGAINST()

SELECT * FROM table WHERE MATCH(title) AGAINST('+Acme burger*')

or why not RLIKE

SELECT * FROM table WHERE TITLE RLIKE 'Acme|burger'

or LIKE searching an array, to have a compilation of $keys

$keys=array('Acme','burger','pound');
$mysql = array('0');
foreach($keys as $key){
    $mysql[] = 'title LIKE %'.$key.'%'
}
SELECT * FROM table WHERE '.implode(" OR ", $mysql)