Jump to content


Photo

Bitfields in PHP and MySQL


  • Please log in to reply
7 replies to this topic

#1 blueworld

blueworld
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 06 September 2006 - 10:31 PM

I'm trying to store some data to a bitfield column in MySQL 5, and then retrieve it back to an integer in PHP. Some part of the process isn't working, and I've been unable to find any helpful information in my searches.

Here's my test code:
$query1 = "UPDATE phpbb_users SET warning1 = b'0101' WHERE user_id = 2";
mysql_query($query1);
$query = "SELECT warning1 FROM phpbb_users WHERE user_id = 2";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$bitfield = $row['warning1'];
echo gettype($bitfield);
echo '<br>' . $bitfield;
echo '<br>' . (int) $bitfield;
The column warning1 is of type BIT(32). Output of this script is
string

0
Does anybody know why I'm getting a string, and how I can get an integer?

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 10:56 PM

How is the table defined?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 blueworld

blueworld
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 06 September 2006 - 10:58 PM

The relevent part of the definition is:
ALTER TABLE phpbb_users ADD ( warning1 BIT(32) )

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 11:24 PM

instead of the gettype() call, try

echo mysql_field_type($result, 0); // --> int

I'm guessing that db fields are retrieved as string representations of the contents
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 blueworld

blueworld
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 06 September 2006 - 11:36 PM

Thanks!
echo mysql_field_type($result, 0);
returns "unknown" for me. It doesn't seem to be a string representation.

I'm really puzzled by this. At this point it seems like it would be easier to just use integer fields instead of bit fields.

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 11:38 PM

I think BIT is a synonym for TINYINT (single byte)
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2006 - 11:42 PM

if you try

var_dump($row['warning']);

you'll see it's a string in PHP - as I suspected - string representations are returned
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 blueworld

blueworld
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 07 September 2006 - 01:26 AM

According to the MySQL manual,

BIT[(M)]

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1).


You're right, it was being returned as a string. The binary data was being interpreted as ascii encoding, so I got 4 characters for my 32-bit field. I guess I have to write a function now to convert the characters into an appropriate integer.

Thanks for your help!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users