2

I had no problems working with MySQL's BIT data type on a Windows environment with PHP and PDO. The only trick that I used was to cast it to int.

But when I uploaded the code to a Linux server, then I wasn't able to see those BIT values: I was getting a square character.

After searching for this, I've found this answer by Marc B, which coupled with Stanimir Stoyanov's comment I ended up doing this for both Windows and Linux and it's working great

CODE FOR DISPLAY

//linux OR windows
$display = ($value_from_db === chr(0x01) || $value_from_db == 1) ? 1 : 0;

CODE FOR SAVING

//both platforms
$value_for_db = ($display == 1) ? 0x01 : 0x00;

So my questions are:

  • Are both platforms indeed treating this data type differently?
  • Or is it a specific programm issue? (php, apache, mysql)
  • Is this the correct way to address this issue? Maybe there's another approach

Windows 7: PHP 5.6.10, Apache 2.4.12, MySQL 5.6.26

Debian Wheezy: PHP 5.5.30, Apache 2.2.22, MySQL 5.6.25

Community
  • 1
  • 1
Matías Cánepa
  • 5,770
  • 4
  • 57
  • 97
  • try this `INSERT INTO tab1 (bit_field) values (b?)`, you can bind boolean or int value. – Wajih May 17 '16 at 06:56
  • like this http://stackoverflow.com/a/36762564/5559741 – Wajih May 17 '16 at 07:09
  • 1
    You might want to include the code you're using to save and retrieve things from the database. PHP is very loosely typed, so just because you're setting something to an integer doesn't mean it's getting passed to the database that way. – miken32 May 18 '16 at 19:16

2 Answers2

2

You need to use the PHP functions bindec and decbin:

code for display

//linux OR windows
$display = bindec($value_from_db);

Code for saving

//both platforms
$value_for_db = decbin($display);
Luigi Pressello
  • 937
  • 4
  • 9
  • Thanks for your help! It worked very good on linux, on windows I'm only having problems with saving. I'm getting `22001 SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column` – Matías Cánepa May 18 '16 at 02:18
  • Hi Matías, can you please provide the table structure to debug the problem? I mean the output for the ` SHOW CREATE TABLE your_table_name` SQL command. – Luigi Pressello May 19 '16 at 08:03
  • I've upgraded PHP version, and everything seems to be working fine now. Thanks! – Matías Cánepa May 19 '16 at 13:08
1

BIT is not a single bit, but it is a fixed-width bit-field value that ranges from 1 to 64. The value you store in a column of this datatype is stored as BINARY. Many client libraries, Command Line client and other GUI Clients do not know how to handle this value. It is also transmitted as BINARY value. How it is being displayed depends on the width of column. For example, if column is 32 bit wide, it is treated as CHAR(4) and in case of 8 bit wide, it is treated as CHAR(1). That is why you need to cast it as an INTEGER value in order to display it properly.

In case of Microsoft SQL Server, BIT is a single-bit column and stored as a single bit within INTEGER datatype.

You can also try BOOL or BOOLEAN datatype for this. These datatypes are similar to TINYINT(1) which can store values other than 0 and 1. TINYINT(1) will consume full byte, but BIT(1) also does the same. According to this page, zero value is considered false and non-zero values are considered true. So, you assign it a value 2, it will be considered true. Therefore, if you want to allow only 0 and 1, then you should use BIT and cast the value to display it properly.

According to this page, one should cast BIT field to INT before displaying it. For this, with reference to this link, try following.

     $bitvalue = ($bitvalue == 0x01) ? 'TRUE' : 'FALSE'

OR

     SELECT CAST(bitfield AS unsigned int)
Community
  • 1
  • 1
A J
  • 3,970
  • 14
  • 38
  • 53
  • using `bindec()` as Luigi suggested, resolved de display issue, right now I'm having trouble saving the value on windows (see my comment in Luigi's answer) – Matías Cánepa May 18 '16 at 15:22
  • @Matías, I thought you had solved the problem, as you said, ` I ended up doing this for both Windows and Linux and it's working great`. If there is still a problem, you should refer to this page, http://dev.mysql.com/doc/refman/5.7/en/bit-field-literals.html – A J May 18 '16 at 17:38
  • You should try `(bitfield AS unsigned)` in your query while saving data. – A J May 18 '16 at 17:49
  • Yes, the problem is solved. But I'm trying to use a "universal" approach, instead of doing an OS based code – Matías Cánepa May 18 '16 at 18:58
  • I am using the same configuration of windows and casting bit to int is working. Moreover, Miken32 has a point. – A J May 19 '16 at 01:09