Mysql LIKE clause and single words in a field

Question

I am currently using a mysql statement like the one below to search for message headers.

select * from table where title like %search_term%

But the problem is that if the title was similar: Acme launches 5 pound burger and the user who searched for Acme will return the result. But if the user searched for Acme burger or Acme 5 pound , it will not return anything.

Is there a way to get it to return results when users search for multiple words? Is LIKE right thing to use here, or is there something else you can use?

Answer 1

You can use REGEXP to match any of the words in the search bar:

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

Please note that this will not be very effective. See here here .

If you need to match each word in your string, you can use this query:

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

Fiddle is here . But the words must be in the correct order (for example, "Acme burger" will match, "Acme burger" will not). There REGEXP matches every word in any order, but it is not supported by MySql unless you install UDF that supports Perl regexp.

Answer 2

To search a string in 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 to search for 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) 

Answer 3

What you need to do is build SQL such that, for example:

 select * from table where title like "%Acme%" and title like "%burger%" 

In short: split the line and create one like for each part. It can also work with replacing spaces with % , but I'm not sure about that.

Answer 4

It’s best to use the use union operation, breaking the search string based on spaces,

FOR Acme 5 pounds,

 SELECT * FROM TABLE WHERE TITLE LIKE '%ACME 5 POUND%' UNION SELECT * FROM TABLE WHERE TITLE LIKE '%ACME%' UNION SELECT * FROM TABLE WHERE TITLE LIKE '%5%' UNION SELECT * FROM TABLE WHERE TITLE LIKE '%POUND%' 

Learn how to make your first request a priority. Or pass the above one of four separate requests with some priority. I think you are using the front end tp database query, so it should be easy for you.

Answer 5

 <?php $search_term = 'test1 test2 test3'; $keywords = explode(" ", preg_replace("/\s+/", " ", $search_term)); foreach($keywords as $keyword){ $wherelike[] = "title LIKE '%$keyword%' "; } $where = implode(" and ", $wherelike); $query = "select * from table where $where"; echo $query; //select * from table where title LIKE '%test1%' and title LIKE '%test2%' and title LIKE '%test3%'