php, sql multiple keyword match using LIKE

Question

I'm trying to grab rows from sql using LIKE, where the key matches key array in the column.

$key = '%PDF%';
$q = mysql_query("SELECT * FROM `ads` WHERE key LIKE '$key' ORDER BY RAND() LIMIT 2");

This works fine and show the results but when i change my key to PDF FILES. i.e.:

$key = '%PDF FILES%';

It doesn't show the results as in the db there is an array of keys, please look at my db column:

Here's My sql Key column:

PDF, pdf, PDF SEARCH, pdf search, PDF ENGINE, pdf

So whats the solution? how to get results if the key is pdf files?

I think it can be by splitting the key and then matches the keywords. right?

this can be done by explode(); but how? and how to match with sql?

Matt Healy · Accepted Answer · 2011-07-04 10:06:39Z

3

When the key is '%PDF FILES%', you are actually searching for the string "PDF FILES" and not some combination of the words PDF and FILES.

To achieve what you want, try constructing a query like:

$key = '%PDF%';
$key2 = '%FILES%';

$q = mysql_query("SELECT * FROM `ads` WHERE (key LIKE '$key' OR key LIKE '$key2') ORDER BY RAND() LIMIT 2");

You can use various techniques with arrays and explode to construct a dynamic query with a variable number of keywords.

answered Jul 4, 2011 at 10:06

Matt Healy

18.6k5 gold badges59 silver badges57 bronze badges

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

Comments

sudheshna · Accepted Answer · 2011-07-04 10:27:30Z

2

Try this,

$keyvalues=split(" ",$search_text);//Breaking the string to array of words

// Append the query by the keywords
while(list($key,$val)=each($keyvalues)){
if($val<>" " and strlen($val) > 0)

{$sql .= " name like '%$val%' or ";}

}// end of while

The trailing OR can be removed by

$sql=substr($sql,0,(strlen($sql)-3));

edited Jul 4, 2011 at 10:27

answered Jul 4, 2011 at 10:08

sudheshna

1,1601 gold badge14 silver badges24 bronze badges

Comments

niktrs · Accepted Answer · 2011-07-04 10:07:50Z

0

This is because LIKE works with only one string. You have to use more LIKE statements with OR in case you want to use wildcards.

On the other hand you can use IN which accepts comma seperated quoted strings, but it doesn't accept wild cards.

answered Jul 4, 2011 at 10:07

niktrs

10.1k1 gold badge32 silver badges32 bronze badges

Comments

Rush · Accepted Answer · 2011-07-04 10:36:29Z

-2

You can not enter multiple keywords for Like in One single statement.

You can try for this it will help you.

WHERE key LIKE '%PDF%' OR '%pdf%' OR '%PDF SEARCH%'

and so on.....

edited Jul 4, 2011 at 10:36

answered Jul 4, 2011 at 10:07

Rush

7254 silver badges13 bronze badges

7 Comments

JJJ Over a year ago

You mean WHERE key LIKE '%pdf%' OR key LIKE '%search%' OR key like ...

JJJ Over a year ago

I think you missed my point: I meant that you can't do WHERE key LIKE 'val1' OR 'val2' like you have in your answer, and OR LIKE gives a syntax error. You have to write the conditions in full: WHERE key LIKE 'val1' OR key LIKE 'val2'.

JJJ Over a year ago

It's still wrong. OR LIKE is never allowed, you have to write OR key LIKE every time.

Rush Over a year ago

Ok i have checked in Mysql...now above query run fine...you just need to put as above for keywords

JJJ Over a year ago

I don't want to sound like a broken record, but you really really need to repeat the key LIKE every time. The new query runs without errors, but it doesn't return all the rows. Each condition between ORs is independent, so your query is functionally equal to: WHERE '%PDF SEARCH%' OR '%pdf%' OR key LIKE '%PDF%'. You can probably see from that that it won't work. You need complete conditions every time.

|

Collectives™ on Stack Overflow

php, sql multiple keyword match using LIKE

4 Answers 4

Comments

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related