MYSQL PHP looking for multiple items in CSV

[farzher]

This should be easy enough to understand. I have a CSV value in MYSQL ex: "chicken,penguin".

 

I need to find which row contains chicken AND penguin. I've been using find_in_set('chicken', items)

 

But if I do find in set with chicken AND chicken it would still return true for "chicken,penguin"

 

What do? To make sure both of the items are correct?

[ManiacDan]

WHERE find_in_set('chicken', items) AND find_in_set('penguin', items)

That doesn't work?

 

Also, you're doing this wrong.  Research normal forms as they relate to database design.  You should split this field into its own table.

 

-Dan

[farzher]

WHERE find_in_set('chicken', items) AND find_in_set('penguin', items)

 

Sure that works, but if I'm looking for

 

WHERE find_in_set('chicken', items) AND find_in_set('chicken', items)

 

(both chicken) it returns true on the set "chicken,penguin" which I don't want it to do o.o

 

I decided not to make it its own table because it seemed like a waste of space, storing the id, item_name, and user_id instead of just item_name and then a comma. Should I make it its own item_user_list table? Even if I do, I still have the same problem.

[ManiacDan]

Wait...you want to return sets that have TWO instances of "chicken"?  Absolutely you must break this into its own table.  You should really break it into another table, with "name" and "count," so that you can store "chicken":"2"

 

-Dan