CODEWITHSUNDEEP
phpmysqlsql
Norman
Norman

Reputation: 6365

Mysql LIKE clause and separate words in a field

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?

Upvotes: 4

Views: 3895

Answers (5)

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 5

Nik Drosakis
Nik Drosakis

Reputation: 2348

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)

Upvotes: 5

user1646111
user1646111

Reputation: 

<?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%'

Upvotes: 0

Santhosh
Santhosh

Reputation: 1791

The best thing is thing use perform union operation by splitting your search string based on whitespaces,

FOR Acme 5 pound, 

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%'

Find out a way to give the first query a priority. Or pass the above one as four separate queries with some priority. I think you are using front end tp pass query to data bases, so it should be easy for you.

Upvotes: 0

fredrik
fredrik

Reputation: 6638

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

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

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

Upvotes: 0

Related Questions