[Resolved] find_in_set doesn't find all?

[4rxsid]

Hi all,

I've got a column of type SET, with possible values of:

0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19

My query which uses FIND_IN_SET doesn't find all entries that have one or more of the search criteria:

SELECT * FROM Main1 WHERE Category like '%%' AND FIND_IN_SET('%1,4,5,6,11,12,13%', The_Set_Field) > 0 ORDER BY Date_Created DESC

This will return a row that only has each and all of those in the search 1,4,5,6,11,12,13. But will not return those rows that have for example...just 11, or that have 1,6,11. Why is this returning only an exact match?

I've also tried all of these with the same restrictive results:

SELECT * FROM Main1 WHERE Category like '%%' AND FIND_IN_SET('1,4,5,6,11,12,13', The_Set_Field) > 0 ORDER BY Date_Created DESC

SELECT * FROM Main1 WHERE Category like '%%' AND (FIND_IN_SET('%1,4,5,6,11,12,13%', The_Set_Field) > 0) ORDER BY Date_Created DESC

SELECT * FROM Main1 WHERE Category like '%%' AND (FIND_IN_SET('1,4,5,6,11,12,13', The_Set_Field) > 0) ORDER BY Date_Created DESC

SELECT * FROM Main1 WHERE Category like '%%' The_Set_Field LIKE '%1,4,5,6,11,12,13%' ORDER BY Date_Created DESC

SELECT * FROM Main1 WHERE Category like '%%' The_Set_Field LIKE '1,4,5,6,11,12,13' ORDER BY Date_Created DESC

Any ideas?

Thanks in advance!

[LordShryku]

Try "IN"

  SELECT * 
    FROM Main1 
   WHERE The_Set_Field IN ('1','4','5','6','11','12',13')
ORDER BY Date_Created DESC

[4rxsid]

thanks for the reply LordShryku

your suggestion returns an error (syntax), so I changed it to:

IN ('1,4,5,6,11,12,13')

This will also return only those rows that match exactly 1,4,5,6,11,12,13 in the SET. I want to be able to also return rows that have other possible combinations of those numbers queried such as

1,4,5 or 12,13 or 1,12,13 or just 1 and so on. As of now...only exact matches are returned which is a limitation I dont want.

Thanks.

[LordShryku]

IN makes a list, so your single quotes must match accordingly.
Saying WHERE field IN ('1,4,5,6,11,12,13')
is the same as saying WHERE field = '1,4,5,6,11,12,13'

There error happened because I missed a single quote before 13.
Use this -> IN ('1','4','5','6','11','12','13')
It's like saying
WHERE field = 1
OR field =4
OR field =5
OR field = 6
OR field = 11
OR field = 12
OR field = 13

[4rxsid]

unfortunately neither of those return any results (zero records found)...even though those numbers are found in many different combinations in many different rows.

neither works:

IN ('1','4','5','6','11','12','13') 

or 

WHERE field = 1 
OR field =4 
OR field =5 
OR field = 6 
OR field = 11 
OR field = 12 
OR field = 13

I think it has to do with binary (the way this SET data is stored in the MySQL db)....but I just can't figure it out. Any other ideas/suggestions?

Thanks.

[4rxsid]

ok...I've found the solution. and of course, it was much easier than I originally thought:

$workingDecimal = 1;
$finalDecimal = 0;

for ($xyz = 0; $xyz<=19; $xyz++) {
    if ($formCheckbox[$xyz] == "on") {
        $finalDecimal = $finalDecimal + $workingDecimal;
    }
    $workingDecimal = ($workingDecimal * 2);
}

SELECT * FROM Main1 WHERE Category like '%%' AND ( (Extras+0) & " . $finalDecimal . " )>0 ORDER BY Date_Created DESC

works like a charm!