find_in_set not finding all?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rxsid
Forum Commoner
Posts: 82
Joined: Thu Aug 29, 2002 12:04 am

find_in_set not finding all?

Post 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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
rxsid
Forum Commoner
Posts: 82
Joined: Thu Aug 29, 2002 12:04 am

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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?
rxsid
Forum Commoner
Posts: 82
Joined: Thu Aug 29, 2002 12:04 am

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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! ;)
rxsid
Forum Commoner
Posts: 82
Joined: Thu Aug 29, 2002 12:04 am

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 ;)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Query by itself is lightning fast, but set_to_binary function.... I'm not sure. Benchmark it =)
rxsid
Forum Commoner
Posts: 82
Joined: Thu Aug 29, 2002 12:04 am

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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 ;) )
Post Reply