CODEWITHSUNDEEP
mysql
BigDX
BigDX

Reputation: 3547

MySQL wildcard Like query with multiple words

I have a mysql query as follows.

$query="SELECT name,activity FROM appid 
where result > 5 AND name LIKE :term ORDER BY name ASC LIMIT 0,40";

$result = $pdo->prepare($query);

$result->bindvalue(':term','%'.$_GET["q"].'%',PDO::PARAM_STR);
$result->execute();

What i want to do is this.

I have and entry like this that i want to find

'News & Weather'

However when i type

'news weather'

it of course will not find it. How can i be able to type that and retrieve that entry?

Upvotes: 2

Views: 1871

Answers (2)

Barranka
Barranka

Reputation: 21047

Regular expressions can do the trick:

select *
from appid
where name rlike 'news|weather' -- Matches 'news' or 'weather'

Another example:

select *
from appid
where name rlike 'news.*weather' -- Matches 'news' and 'wether' 
                                 -- with any characters in-between or none at all
                                 -- (ordered)

Just one more:

select *
from appid
where name rlike '^news.{1,}weather$' -- Matches any text that starts with 'news'
                                      -- and has at least one character before
                                      -- ending with 'weather'

Regular espressions can be used to create very complicated filters on text fields. Read the link above for more information.

If you can add a full-text index to your table, Full-text search might be the better way to go with this. Specifically, a boolean Full-Text search:

select *
from appid
where match(name) against (+news +weather)

Upvotes: 2

Uueerdo
Uueerdo

Reputation: 15941

I believe the only way possible are through code:

Option A: Replace the spaces in your query parameter with '%' in code, but that of course will make the multiple words ordered

Option B: Split your parameter on spaces and dynamically construct your query with as many LIKEs as needed, adding additional ":termN" parameters for each one.

Upvotes: 0

Related Questions