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

Answer 2

I do not think this will return true.

WHERE CHOICES IN ()

when you do this, it will compare complete choices value to individual item inside IN

You might wanna have a look at find_in_Set function of MySQL

WHERE find_in_set(optionNumber1, choices) > 0 
OR find_in_set(optionNumber2, choices) > 0
OR find_in_set(optionNumber3, choices) > 0

You will have to make query in a loop in programming language you are using

Answer 3

I think you are not getting Confused. You are absolutely right this will return something (a tuple or more then one tuple) and that of-course is a True value. So Carry on....

Answer 4

just had the same problem. solved it using RLIKE:

$options_in_row = array_filter(explode(',',$row['myoptions'])); // convert the csv to array of numbers. use array_filter because empty values will generate a regex that always find something.
$options_rx = implode('|', array_map(function ($x)
        {
            return "\b$x\b"; // adding \b to avoid partial number hits, such as '2' inside '123,234'
        }, $options_in_row));
// $options_rx is something like '\b123\b|\b234\b'
$sql = '.... WHERE `choices` RLIKE "'.$options_rx.'"';

take into account that this code assumes csv of numbers. if your case is different you'll have to add escaping.

Answer 5

I don't know where is choice column in which table, but have u tried this way

SELECT * FROM t1 WHERE `choices` > ANY (SELECT options FROM t2);

Reference