Query a comma seperated list of values in field

Basically I have a field calls 'tags' in my Customer table. Initially this was just a straightforward field containing just one value, but now I would like it extend it to include multiple values seperated by a comma.

I'm using PHP and MySQL and this is the current code:

SELECT * FROM customer WHERE (tags='$tags')

Code (markup):

How do I now get it to match the value of $tags to any records that have that value in the 'tags' field?

 

SELECT * FROM customer WHERE tags LIKE '%tags%'
or
SELECT * FROM customer WHERE tags LIKE 'tags'

 

Cheers mate,

The first one worked, the second one didn't.

 

glad to help you

 

You should use FIND_IN_SET() instead of LIKE. Using LIKE may not return the correct results e.g.

SELECT * FROM customer WHERE tags LIKE '%ball%'

Code (markup):

will return ball, balls and ballon, ballons. I hope you dont want to get the results of ballons.

But the using FIND_IN_SET() will give you the correct results.

SELECT * FROM customer WHERE FIND_IN_SET('ball',tags)

Code (markup):

 

I'd put the list in a separate table with 'customer_id' and 'tag' fields in it instead, as such substring comparison is very slow.

Also, the sample query given above would return tags like 'ballet', 'baseball', 'fishballs', etc. To correctly match only 'ball', you'd have to do 4 comparisons (which, again, is very slow):

[B]SELECT * FROM customer WHERE [/B]
  [B]tags = 'ball'[/B] [I]-- this is the only tag[/I]
  [B]OR tags LIKE 'ball,%'[/B] [I]-- the tag occurs first[/I]
  [B]OR tags LIKE '%,ball,%'[/B] [I]-- the tag occurs in the middle[/I]
  [B]OR tags LIKE '%,ball'[/B] [I]-- the tag occurs last[/I]

Code (markup):