MySQL list IN list

Question

I currently store user's inputs in comma separated lists like so:

Userid | Options
1      |  1,2,5

A user ticks a set of options in a form which is an array, which is then joined with a comma to make

1,2,5

Then MySQL is trying to find other users who some or all of the same options ticked on a different field name (although same table).

Currently I do this:

WHERE `choices` IN ('.$row['myoptions'].')

So this could be something like:

WHERE 1,2,5,8 IN (1,4,6)

This would return true because theres at least one value match right? Or have i got this confused..

Answer 1

May be you are going the wrong way to do this.

The function FIND_IN_SET might be helpful if the options column type is SET.

Example:

SELECT * FROM yourtabe WHERE FIND_IN_SET('2', Options);

But, it will only let you compare one string at a time, in the above example, it compares if 2 is present in the rows. If you have to compare multiple values you cannot accomplish that by using FIND_IN_SET.

However, in your case, LIKE clause may be of use to.

May be something like

SELECT * FROM yourtable WHERE Options LIKE '%2,3%';

Now this will search for 2,3 value anywhere in the column, and give the result. But this also comes with another complication, it gives the result only if 2,3 is present side by side of each other, if the column has 2,1,3 or 2,4,5,3 or even 3,2 it will not list these records.

Now coming to your question

`WHERE `choices` IN (1,4,6)`, 

will translate to

WHERE `choices` = '1' OR `choices` = '4' OR `choices` = '6'

so it will return false

Why?

because your column contains not only 1 or 4 or 6 but 1,2,5 as one string. So all the comparisons above to return false