SELECT users from MySQL database by privileges bitmask?
bitmask, mysql, php, select
Solution
[...] want to SELECT some fields
Wrong. You want to select some Rows. Columns are usually called fields.
You are supposed to read the Documentation: Bit Functions are documented for mysql.
So you can try:
`Select * from users WHERE (user_privileges & 1) >0`
Problem
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 | john@example.com | 165 | 10100101 | 2 | max@example.com | 13 | 00001101 | 3 | trevor@example.com | 33 | 00100001 | 4 | paul@example.com | 8 | 00001000 | 5 | rashid@example.com | 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.