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%";
>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