MySQL "sets"/binary math

How to use MySQL for some bit math

[suidas]

I'm wondering how to get MySQL and PHP to do some "set" math with binary numbers. I mean the following.

Imagine there are 8 possible rock bands, represented as bits. Everyone has a eight bits representing which they like.

Jim likes 10010110
Jill likes 11010000
Bob likes 01000001

Ergo, Jim and Jill share bands one and four. Jim and Bob share no bands, and Bob and Jill share one.

My question is, how do I do the bitmath here, either in PHP or MySQL?

I have a much larger number of "bands" (eg., 800), which I plan to store as a binary field in MySQL. How do I get MySQL to return the 10 people who share the most band preferences with Jim? Alternately, if MySQL won't do it, how do I do the math in PHP?

[emsaw]

suidas,

My two cents:
IMO, it would be easier to set up a foreign key relationship between the two tables..

Fan
fankey, fanfirstName, fanlastName

Band
bandkey, bandGenre, bandName

BandsFans
bandsFansKey, bf_fanKey, bf_bandKey

so you have your bands, your fans, and your fans of bands, and you don't have to do any nasty binary math.

then you can do simple counts and group bys on the BandsFans table for your information.

Mark

[Demaestro]

For real you want to do as the above suggests. The whole point of having a relation database is so you can retrive and store data in a human readable fashion using sql. If you have to join things on binary you are basically 'missing the point' of the database itself.