Tech Blog

PHP and MySQL 5 bit fields

Posted At : August 25, 2008 2:21 AM 2 Comments

While debugging some PHP code the other night I came across a particularly strange problem with MySQL Bit fields.

I was returning a query with some bit fields into my class but the it was not returning true or false when I switched the data.

A bit of hunting and it turned out the that it was being returned as a binary type and after a few failed attempts to convert it to an integer or boolean on the PHP side I found this bug report on it.

So the moral of the story is if you are selecting BIT types from a mysql DB in PHP don't do this:

SELECT myBit
FROM tbl_example
WHERE id = 1

Instead cast the bit to an integer in MySQL like so:

SELECT CAST(myBit AS unsigned integer) AS myBit,
FROM tbl_example
WHERE id = 1

Voila, it works like expected.

Cheers, Mark Lynch

Posted in: Open Source, mysql, Database, PHP

Leave a Comment

2 Comments

Kyle Perkins 8/25/08 5:58 PM

As of 5.0.3, BIT is a bit-field, not a synonym for TINYINT(1). You may want to rework your structure and SQL to use BOOL/TINYINT instead.

Good luck!

Anon. Coward 11/14/08 3:49 PM

Thanks, much appreciated, fixed my issues.