Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL list IN list

Tags:

mysql

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..

like image 477
Sir Avatar asked Dec 02 '11 05:12

Sir


1 Answers

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

like image 79
Starx Avatar answered Oct 23 '22 10:10

Starx