Page 1 of 1

find_in_set not finding all?

Posted: Fri Apr 16, 2004 4:58 pm
by rxsid
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:

Code: Select all

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:

Code: Select all

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!

Posted: Fri Apr 16, 2004 5:02 pm
by Weirdan

Code: Select all

select * from Main1 where The_Set_Field in (1,4,5,6,11,12,13) order by Date_Created desc

Posted: Fri Apr 16, 2004 5:14 pm
by rxsid
thanks for the reply Weirdan.

i've tried your suggestion...but apparently the syntax wasn't correct, so i tried this:

Code: Select all

select * from Main1 where The_Set_Field in ('1,4,5,6,11,12,13') order by Date_Created desc
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.

Posted: Fri Apr 16, 2004 5:28 pm
by Weirdan
oops, wrong answer.
Make the binary from your search criteria, then & it with The_Set_Field+0
Do you use php to construct your query?

Posted: Fri Apr 16, 2004 5:58 pm
by rxsid
I am using PHP to create the query on the MySQL db.

I'm not sure I follow your suggestion...can you elaborate?

Thanks again.

Posted: Fri Apr 16, 2004 6:25 pm
by Weirdan
SET's in MySQL are actually numbers.
Suppose you have the column defined as SET('1','2','3'). If you store the '1,3' value in this column, it's stored as 5 (101 in binary representation).

When you retrieve the SET value in numeric context (The_Set_Column+0) MySQL returns 5 for example given above ( read it as 'first and third elements are set' ).

If we would have ability to convert the given string representation of set to binary form without inserting it in table and retrieving it back, it will be easy to apply the bitwise operations to it. Unfortunately I have not found such a function in MySQL reference manual. So here is PHP equivalent:

Code: Select all

/**
 * Converts set to binary representation
 *
 * @param  array, string  $set  set to convert.
 *                              Accepts either a comma delimited string
 *                              or array consisting of the name of elements
 *
 * @param  array, string  $full_set  set with all possible elements.
 *                                   Accepts string or array
 *
 * @param  [string]       $delimiter  optional delimiter for string sets
 *                                    ignored if set is array
 *                                    default delimiter is comma
 *
 * @return  int  binary representation of the given set
 *
 * NOTE: order of elements in $full_set DOES matter, but in $set - doesn't.
 */
function set_to_binary($set, $full_set, $delimiter = ',') {
    if( is_string($set) ) $set = explode($delimiter, $set);
    if( is_string($full_set) ) $full_set = explode($delimiter, $full_set);
    $full_set = array_unique($full_set);

    $binary_set = 0;
    foreach($set as $element) {
        if( ( $pos = array_search($element, $full_set) ) !== false ) {
            $binary_set |= pow(2, $pos);
        }
    }
    return $binary_set;
}
Let's return to your task. Suppose we want to search for rows which have '1' or '3' element set:

Code: Select all

$full_set = "0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19";
$ret = mysql_query("select * from Main1 where ( (The_Set_field+0) &  " . set_to_binary('1,3', $full_set) .  " )>0");
Voila! ;)

Posted: Fri Apr 16, 2004 6:44 pm
by rxsid
wow...that works like a charm!

seems rather heavy though as far as code and processing.

i was thinking that using a column of SET type would be nice in this case...but I'm starting to wonder if something different would be in order...as far as making the SELECT end of it a little "easier".
Your code works very well....it's just that sometimes I wonder if code as complecated as this begins to hurt script performance...and at what point.


ENUM won't work because only one of the subset could be stored. Perhaps I should keep this column as a string instead. what do you think?

Posted: Fri Apr 16, 2004 6:56 pm
by Weirdan
rxsid wrote:Perhaps I should keep this column as a string instead. what do you think?
How do you plan to make your select work then?

I believe I've see something similar to function I gave to you in one of the major buletin board software packages. They used it to store user's preferences. vBulletin perhaps... or phpBB :roll: Anyway, I believe there works people much smarter than me ;)

Posted: Fri Apr 16, 2004 7:17 pm
by Weirdan
Query by itself is lightning fast, but set_to_binary function.... I'm not sure. Benchmark it =)

Posted: Sat Apr 17, 2004 3:19 am
by rxsid
hello again Weirdan.

after testing the suggestion...I found that sometimes the results would not include all rows that it should. for example....a query with say 1,4,11 would return a row that had 1,4,11 or 4,11 or 11....but not a row that had just 1.

so...after tweaking your suggestion a bit...I've found the solution. and of course, it was much easier than I originally thought:

Code: Select all

$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!

thanks again for your suggestions and help! it ultimately lead me to find my solution.

Posted: Wed Apr 21, 2004 12:51 pm
by Weirdan
here is even simplier solution:

Code: Select all

$finalDecimal = 0;

for ($xyz = 0; $xyz<=19; $xyz++) {
    if ($formCheckbox[$xyz] == "on") {
        $finalDecimal |= (1<<$xyz);
    }
}
It works exactly the same as yours, but little faster and more clear (at least to me ;) )