Gordon P. Hemsley

Linguist by day. Web developer by night.

«
»

PHP, MySQL, and the BIT field type

Posted by Gordon P. Hemsley on February 8, 2010

As Dave Humphrey once taught me:

When you do a search, and it comes back with no results, it’s a sign that you need to write something.

This is an issue that I came across while testing SASHA (which is available for you to try out, by the way), and I didn’t know if it was a bug or a feature. I could find no mention of it anywhere, and the people in the #mysql IRC channel on FreeNode weren’t especially helpful in helping me get to the bottom of it.

What is the issue, you ask? Well, even that in and of itself is a question, because I don’t know whether it’s a bug (or feature) in PHP or MySQL. However, I’m inclined to think it’s the latter, and I’ll get to why in a moment.

But first, some background. The table that SASHA uses to store schedules uses the BIT field type for keeping track of which days of the week a schedule occurs on. I figured it’d be easiest to use a 7-bit field and just flip a bit for each day of the week. And that worked fine for me on my local test server. But then I had a colleague test SASHA out on his test server, and things went a little wacky.

It took a little while to figure out what was causing our problem, and we finally got to the bottom of it: I was using MySQL 5.0 and he was using MySQL 5.1! Apparently, between 5.0 and 5.1, the return format of a BIT field changed from the literal binary data (output in the browser as a character, because the browser didn’t know it wasn’t) to a decimal representation of that data.

The first problem with that was that I had no idea there was a possibility of getting anything but the raw binary data I was getting on my server. The second problem was coming up with a straight-forward solution to detecting whether the database was feeding us raw binary data or converted decimal data. There was no direct way to do this, but I figured out the next best thing. A simple way to check what kind of data we’re getting is to find out whether it converts cleanly to an actual character. Here’s an excerpt from SASHA that demonstrates:

// MySQL 5.0 returns bit as binary, while MySQL 5.1 returns decimal
if( $days == chr( ord( $days ) ) )
{
	$input = 'binary';
}
else
{
	$input = 'decimal';
}

That seems to do the trick when it comes to handling unpredictable BIT field data.

(Again, I can’t guarantee that this isn’t actually a PHP issue, but I seem to recall us both being around the same version of PHP.)

If you have any insight into the matter, please do leave a comment.

This entry was posted on February 8, 2010 at 12:47 AM and is filed under Mozilla, Open Source, SourceForge. Tagged: , , , , , , , , , , , , , , , . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “PHP, MySQL, and the BIT field type”

  1. daiscog said

    March 24, 2010 at 12:49 PM

    Hi,

    I had a similar problem. A solution I found was to CAST the binary data inside your mysql query to an integer. MySQL then returns it as a string (bizarrely) which can itself then be cast as an int in PHP. Example:

    
<?php

$query = "SELECT CAST(`days` AS unsigned integer) AS `days` FROM `table`";
$result = query($query);
$row = $result->fetch_assoc();

$days = (int) $row['days'];

// $days is now an int, upon which you can perform various bitwise checks

// TA-DA!

?>

    This works perfectly on both my machines, one running MySQL 5.0.51 (with PHP 5.2.6) and the other running 5.1.37 (with PHP 5.2.10).

    Now I’m not certain of the entire scope of the backwards/forwards compatability of this as I’ve only tested it on the versions mentioned above, so if anyone else can test on other versions and post their results, I’d be grateful!

    Cheers,

    Dai

    [GPH Editor’s Note: I’ve combined the two comments that were posted in succession to (hopefully) express the original intent of the first comment.]

    Reply

    • GPHemsley said

      March 27, 2010 at 1:36 AM

      That’s an interesting idea. I hadn’t thought of that. It probably would work much better, actually.

      Although, I do have it in mind to eventually support PostgreSQL in addition to MySQL. I would imagine that would make using CAST() slightly more difficult.

      But still, thanks for the tip!

      Reply

  2. PHP: InsertUpdate MySQL BIT(1) field « Shaked Klein Orbach said

    November 18, 2011 at 4:03 PM

    […] "When you do a search, and it comes back with no results, it’s a sign that you need to write something." (Taken from Here  […]

    Reply

  3. Bruce S Gruenbaum said

    May 20, 2013 at 1:42 PM

    Gordon,

    Thanks! This really helped. I ran into in an issue in 5.3.3 where it worked fine using the Zend server, but it failed using a vanilla CentOS Apache/PHP 5.3 install.

    As a point of interest, I had a statement that went something like this:
    ‘SELECT * FROM mytable WHERE…’
    that included a BIT column used to hold a logical value. The column name was ‘url_is_valid’.

    All I did was change the statement as follows:
    ‘SELECT *, CAST(`url_is_valid` AS UNSIGNED INTEGER) AS `url_is_valid` FROM mytable WHERE…’
    — renaming it to the same name so I don’t lose the ability to select all columns — and it worked like a dream. No other code change necessary.

    Thanks a ton for posting this idea. It works very well.

    Regards,
    Bruce

    Reply

Leave a comment

«
»