mysql lookup two tables using like

Question

I'm stuck with the following query, it partially seems to work before giving up the ghost. I'm no guru but I'd be grateful for any help offered.

I have two tables

cms_tags

key  | tag          |  timesused
---------------------------
1     retriever        12
2     jack retriever   1
3     energetic        5
4     bulldog          3

cms_doggies (four fields = key,dogname,description,tags)

key   dogname         description                                                                                                                                               tags
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1     Jack Retriever  Jacks only just been with us for a short while he's a golden retriever that is energetic and loves to play with a frisbee. Does not like canned food.     ,retriever,golden,energetic,frisbee

the query should return:

retriever
jack retriever
energetic

...instead it only returns:

jack retriever

here's the query:

$dogname= strtolower($_GET['dogname'])
$description= strtolower($_GET['description'])
$tags= strtolower($_GET['tags'])

SELECT `key`, `tag` 
  FROM `cms_tags` 
 WHERE `tag` LIKE '%$dogname%' 
    OR `tag` LIKE '%$description%' 
    OR `tag` LIKE '%$tags%' 
ORDER BY `primkey` 
LIMIT 0, $finalnumber;

Answer 1

Hello.

The $tags parameter has the value ',retriever,golden,energetic,frisbee', so there are no matching rows. Instead of using LIKE, try using IN ([Value list]).

Example:

SELECT key, tag
FROM cms_tags
WHERE tag IN ('retriever','golden','energetic','frisbee')

This query will return every row where the tag field has any of the values in the list. Notice, however, that you cannot use wildcards.

Hope this helps you.

Answer 2

Hi Thanks for all the help especially to OMG ponies for pointing me in the FTS direction, I ended up doing the following

ALTER TABLE cms_tags ADD FULLTEXT(tag); ALTER TABLE cms_doggies ADD FULLTEXT(dogname, description, tags);

SELECT * FROM cms_tags WHERE MATCH (tag) AGAINST ('$dogname $description $tags') LIMIT 0, $number;

the query now returns

retriever jack retriever energetic

as these are in the cms_tags table it also returns

play food

I'll have to figure out a way to filter common words like these.

Once again thanks for all your help.