mySQL help please

[KITT]

I'm hoping there's some mySQL experts on PH

I need to run a query like this:

SELECT * FROM `TABLE` WHERE `Flags`=BIT1

i.e. list all the records which have BIT1 set in their flags field. Is there a way of doing this just using an SQL query?

I know I can read in all the results one at a time and use PHP to check the flags field, but wondered if there's a better way?

BIT0 = 1
BIT1 = 2
BIT2 = 4
BIT3 = 8 etc.

cheers

[pdV6]

No idea about MySQL, but in MS-SQL, you could do the following, so maybe its similar:

My test said:

declare @BIT0 int declare @BIT1 int declare @BIT2 int declare @BIT3 int set @BIT0 = 1 set @BIT1 = 2 set @BIT2 = 4 set @BIT3 = 8 select * from [testtable] where ([flags] & @BIT1) = @BIT1 Results ------- Name Flags Val2 2 Val3 3 Val6 6 Val7 7

>> Edited by pdV6 on Tuesday 8th February 16:12

[KITT]

Doesn't seem to like that I'm afriad. Any other suggestion folks?

[pdV6]

Search the help for "bitwise operators" and find the MySQL equivalent of a bitwise AND ("&" in the MS-SQL above)

[Tripps]

Are you sure bitwise operations are that efficient to make the space-savings worthwhile?

Certainly in SQL Server the extra CPU load would outweigh the storage requirements over a group of boolean bit fields.

Unless of course you're writing en embedded application or something along those lines...

[pdV6]

Tripps said:

Are you sure bitwise operations are that efficient to make the space-savings worthwhile? Certainly in SQL Server the extra CPU load would outweigh the storage requirements over a group of boolean bit fields.

Tend to agree with Tripps here.

Furthermore, from a support POV, individual bit fields will effectively be "self commenting".

However, a "flags" field can be very flexible in that you can keep using up spare bits as needed as your application develops without needing to change the database design.

[pdV6]

See http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Bit_functions.html

This should work, then:

select * from [testtable] where ([flags] & 2) = 2;

[KITT]

pdV6 said:

However, a "flags" field can be very flexible in that you can keep using up spare bits as needed as your application develops without needing to change the database design.

Got it in one! I didn't originally want to do it this way but powers that be (i.e. the boss) wanted that flexability as modifying the database later is a big PITA.

Cheers Pete, I've sussed it now, using a bit of PHP and mqSQL:

$Query=sprintf("SELECT * FROM `TABLE` WHERE (`Flags` & %d)", BIT1);



>> Edited by KITT on Wednesday 9th February 09:45