how to use MySQL bitwise operations in php?

select * from table2 where id & 66

Although the question on how to perform bitwise operations in MySQL has been answered, the sub-question in the comments about why this may not be an optimal data model remains outstanding.

In the example given there are two tables; one with a bitmask and one with a break down of what each bit represents. The implication is that, at some point, the two tables must be joined together to return/display the meaning of the various bits.

This join would either be explicit, e.g.

SELECT * 
FROM Table1 
    INNER JOIN TABLE2 
        ON table1.ptid  & table2.id <> 0

Or implicit where you might select the data from table1 into your application and then make a second call to lookup the bitmask values e.g.

SELECT * 
FROM table2
WHERE id & $id <> 0 

Neither of these options are ideas because they are not "sargable" that is, the database cannot construct a Search ARGument. As a result, you cannot optimize the query with an index. The cost of the query goes beyond the inability to leverage an index since for every row in the table, the DB must compute and evaluate an expression. This becomes very Memory, CPU and I/O intensive very quickly and it cannot be optimized without fundamentally changing the table structure.

Beyond the complete inability to optimize the query, it can also be awkward to read the data, report on the data, and you also potentially run into limits adding more bits (64 values in an 8 bit column might be fine now but not necessarily always so. They also make systems difficult to understand, and I would argue that this design violates first normal form.

Although using bitmasks in a database is often a sign of bad design, there are times when it's fine to use them. Implementing a many-to-many relationship really isn't one of those times.

The typical approach to implementing this type of relationship looks something like this:

table1 
Id        Val1         Val2
---------------------------
1         ABC          DEF
2         ABC          DEF
3         ABC          DEF
4         ABC          DEF
5         ABC          DEF
6         ABC          DEF

table2
id     types
-------------
1      music
2      art
3      pictures
4      video
5      art2
6      actor
7      movies

table1-table2-relationshitp
table1ID    Table2ID
---------------------
1           1
1           2 
2           3
2           5
3           2
3           7
...

And you would query the data thusly

SELECT table1.*, table2.types
FROM table1 
     INNER JOIN table1-table2-relationship 
          ON table1.id = table1-table2-relationship.table1id
     INNER JOIN table2 
          ON table1-table2-relationship.table2.id = table2.id

Depending on the access pattern of these tables, you would typically index both columns on the relationship table as a composite index (I usually treat them as a composite primary key.) This index would allow the database to quickly seek to the relevant rows in the relationship table and then seek to the relevant rows in table2.

After playing around with the answer from Marcus Adams, I thought I'd provide another example that helped me understand how to join two tables using bitwise operations.

Consider the following sample data, which defines a table of vowels, and a table of words with a single value representing the vowels present in that word.

# Create sample tables.
drop temporary table if exists Vowels;
create temporary table Vowels 
(
    Id int, 
    Letter varchar(1)
);
drop temporary table if exists Words;
create temporary table Words
(
    Word varchar(20),
    Vowels int
);

# Insert sample data.
insert into Vowels
    select 1,   'a' union all
    select 2,   'e' union all
    select 4,   'i' union all
    select 8,   'o' union all
    select 16,  'u';
insert into Words 
    select 'foo',           8  union all 
    select 'hello',         10 union all
    select 'language',      19 union all
    select 'programming',   13 union all
    select 'computer',      26;      

We can now join the Vowel table to the Word table like so:

# List every word with its vowels.
select Word, Vowels, Letter, Id as 'Vowel Id'
from (
    select *
    from Words
) w
join Vowels v
where v.Id | w.Vowels = w.Vowels
order by Word, Letter;

And of course we can apply any conditions to the inner query.

# List the letters for just the words with a length < 6
select Letter
from (
    select *
    from Words
    where length(Word) < 6
) w
join Vowels v
where v.Id | w.Vowels = w.Vowels
order by Word, Letter