Search code examples
phpmysqldatabaseselectflags

How to properly get flag value from database using PHP?

Imagine it like this. There is a field in my database called flags in which are added or removed data like this:

UPDATE people SET flags=flags|16 WHERE ....
UPDATE people SET flags=flags|128 WHERE ....
UPDATE people SET flags=flags&~16 WHERE ....
UPDATE people SET flags=flags&~128 WHERE ....

For instance this field can have value like 65536 or more or less. My question is - How to get specific flag from this field using PHP code? I mean something like this:

SELECT * FROM people WHERE flags=16;

But the result will return all people with not just number 16 in field but it will return people with flag 65536, people with 16 but not people with 2 or 1. Which SELECT query should I use here in my php code or maybe some specific PHP integrated functions? Thank you.

Solution

  • Assuming flags is a bitfield and you want to select rows where bit #4 (10000) is set

    SELECT * FROM people WHERE flags & 16;

    This is not ideal though as you're losing out on all that referential goodness that DBs are good for.

    What you should have is two new tables; flags and people_flags. The former contains all the flags (id and name columns should be sufficient). The latter contains flag_id and people_id columns, creating a many-to-many relationship (see junction table).