[Resolved] MySQL and the LIKE statement

Rulkster

I have a field in my table for description. It is a blob field.

I want a SQL statement that will do a search on the description field and return all relevant records.

Example records:

record 1 =
description - "are you having a wonderful day"

record 2
description - "are you having a bad day"

Variable I am using
$keywords = "are bad";

My SQL statement looks like this:

$qString = "Select * from my_table where description LIKE '%$keywords%') order by itemid";

Now, no records are being returned when both above records should. If I just put 'bad' in $keywords, then the one record returns. Likewise if i put "are", then the two records return. But if I put "are bad" - no records return.

Hope this made sense.

Brian

YappyDog

The wildcard (%) used with like means look for anything before, and then anything after (if you used two of them) what ever is between them.

So, looking for '%are bad%' will find a column that contains "are bad things happening", but will not find either of the ones you want.

You need to break your keywords up and search for each one individually.

$qString = "Select * from my_table where description LIKE '%$keyword_1%' or description like '%$keyword_2%' order by itemid";

Rulkster

What if i dont know before hand what the keywords are, or how many of them there will be?

YappyDog

You'll need to use something like the split() function to break your keywords up into an array, and then loop through your array and build your query statement.

http://us4.php.net/manual/en/function.split.php

http://us4.php.net/manual/en/control-structures.for.php