List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 19 2001 1:52am
Subject:Re: Creating bitmask for SELECT against type SET
View as plain text  
At 5:16 PM -0800 3/18/01, Todd Markle wrote:
>Recently, I'm having to match against SET columns quite a bit. Is there a
>fast way to create a bitmask of set data?
>For example:  A user table has interests as set ('ABX','BX','CXX'...)
>I want to select users where interests include ('CXX','NNX'...)
>Method 1: select them one at a time....
>    SELECT * from users where interests LIKE "%CXX%";
>    SELECT * from users where interests LIKE "%NNX%";
>    etc.
>Method 2: the same thing using OR....
>    Select * from users where interests LIKE "%CXX%"
>              OR interests LIKE "%NNX%"...
>I would prefer to do this:
>    -Make a bitmask of ('CXX','NNX'...)
>    -Select WHERE interests bitwise-or bitmask
>As far as I can tell from the documentation of Find_In_Set(), I am on my
>own to make the bitmask... first selecting the column definition, and then
>using find_in_set iteratively on that column definition for each member I
>want to match.  This seems a pain, since MYSQL creates the number I want
>by itself every time I write a record to the table!
>(Yeah, I know that another possibility is to create a joined
>table rather than use the set type, but this is a lightweight
>application... a few thousand users, about 30 interests, and
>only a handful of these matches a week.  I'm just trying to
>clean up the code.)

I have code that does this by reading the SET column definition from
SHOW COLUMNS output, then uses it to allow a set of corresponding
checkboxes from a form to be converted into a bitmask. That allows
you to perform numeric testing against SET column values.  The code
is in Perl, lemme know if you want it.

Paul DuBois, paul@stripped
Creating bitmask for SELECT against type SETTodd Markle19 Mar
  • Re: Creating bitmask for SELECT against type SETPaul DuBois19 Mar