Check if a string is in a MySQL field value

Question

I have a DB field called 'colors' where I store favourite colors from people. These are selected from a multi-select field and I store them all in one field. I use PHP to get all the values in one field using:

$lookfor = implode(",", $looking_for);

What's the best way to search for a string within a field that contains values in the format red,blue,yellow....

Since the users will use the same multi-select box I still need to access the values in this format. I tried using

SELECT * 
  FROM users 
 WHERE users_looking_for LIKE '%$lookfor%'

But this won't work if my values are not store in the same order as they are searched.

Answer 1

Short term solution

Use the FIND_IN_SET function:

$query = sprintf("SELECT u.* 
                    FROM USERS u
                   WHERE FIND_IN_SET('%s', u.users_looking_for) > 0", 
                  mysql_real_escape_string($lookfor));

If you want to search for more than one lookfor value, you will need to add additional FIND_IN_SET checks:

WHERE FIND_IN_SET('value1', u.users_looking_for) > 0
  AND FIND_IN_SET('value2', u.users_looking_for) > 0

Long Term Solution

Do not store denormalized data - use a many-to-many table setup:

PEOPLE

• people_id (primary key)

COLORS

• color_id (primary key)

PEOPLE_COLORS

• people_id (primary key, foreign key to PEOPLE.people_id)
• color_id (primary key, foreign key to COLORS.color_id)

Answer 2

but this won't work if my valyes are not store in the same order as they are searched.

Sort them before storing, and sort them before searching.

Answer 3

You should use a table user_colors with two fields : "user_id" and "color"

This would be much easier to search for values.

example :

SELECT * FROM user_colors WHERE color='red' OR color='blue';

Answer 4

I would suggest that instead of using a VARCHAR field to hold the list of selected colours, you move to more of a relational approach, where each colour for each user is listed. That way when you do your query you are able to say where it is IN a set of values which are their selections.