MySQL wildcard Like query with multiple words

Question

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?

Create an array of the words splitting on the space and have an OR LIKE for each value in the array. — Michael McGriff
– Michael McGriff, Commented Oct 7, 2014 at 17:32

Barranka · Accepted Answer · 2014-10-07 18:12:55Z

2

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)

edited Oct 7, 2014 at 18:12

answered Oct 7, 2014 at 18:05

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BigDX Over a year ago

This looks good but how to i implement this into my query that uses $_GET from user search?

Uueerdo · Accepted Answer · 2014-10-07 17:34:11Z

0

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.

answered Oct 7, 2014 at 17:34

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

4 Comments

Barranka Over a year ago

I recommend you read about regular expressions in MySQL for a better option

Uueerdo Over a year ago

Oh yeah, I forgot it has those; I don't really have a use for them in the data I regularly work with.

Barranka Over a year ago

I said exactly the same when someone introduce them to me... and now I can't leave without them ;)

Uueerdo Over a year ago

Most the time, I am searching for strings "starting with ABC" in indexed fields, so regex would be a detriment unless they've optimized it since the last time I checked so it can use an index when the expression begins with fixed text.

Collectives™ on Stack Overflow

MySQL wildcard Like query with multiple words

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related