SELECT users from MySQL database by privileges bitmask?
I have users table and want to SELECT some rows by bitmask criteria. I'll try to explain my problem with small example.
Structure of table users
user_id int [primary key, auto_increment]
user_email varchar(200)
user_privileges int
Note: It has more fields but they are irrelevant for this question.
Filled table may look like this
+---------+--------------------+-----------------+
| user_id | user_email | user_privileges | << binary
+---------+--------------------+-----------------+
| 1 | [email protected] | 165 | 10100101
| 2 | [email protected] | 13 | 00001101
| 3 | [email protected] | 33 | 00100001
| 4 | [email protected] | 8 | 00001000
| 5 | [email protected] | 5 | 00000101
+---------+--------------------+-----------------+
Now I want to SELECT users by specific privileges bitmask (by user_privileges column).
For example:
- bitmask=1 [00000001] would select user-ids 1, 2, 3 and 5
- bitmask=9 [00001001] would select user-id 2 only
- bitmask=5 [00000101] would select user-ids 1, 2 and 5
- bitmask=130 [10000010] would select none
My question: Is it possible from query or I have to go all users one-by-one and check this value from PHP code? Also, is it possible if field user_privileges is text, containing hexadecimal numbers, instead of integers? I need working mysql query example.
Note: This is just a simple example with 8-bit privilege-set. In real environment it may have larger sets (greater integers, more bytes). Creating separate column for each privilege state works fine, but that's not possible solution. I'd rather work with hex values, but integers are fine too, something is better than nothing.
Thanx in advance.
Answer
SELECT
*
FROM
users
WHERE
(user_privileges & <level>) = <level>
<level> being the access level you want to search on (e.g. 1, 5, 9, 130, etc.)