MySQL select bit(1) shows as string(3)

Question

I have a table with a column bit(1) and the following query: SELECT my_bit FROM my_table.

When I echo the result on a php page the value shows up as string(3) %qu whether the bit value on a column is 0 or 1. How is this possible?

The following query resolved the issue when echoing on a web page: SELECT CAST(my_bit AS UNSIGNED) AS my_bit FROM my_table.

However, both of the queries above work on a command line tool. No string(3)s there.

Also everything works when selecting data, no difference between

1. SELECT * FROM my_table WHERE my_bit=0
2. SELECT * FROM my_table WHERE my_bit=(0)

when using command line tool or web interface php pages. (The latter is suggested here.) Echoing has to be done using CAST function but the WHERE is not affected by the parentehis: correct rows are returned.

Main questions

1. How come the returned bit type value is echoed as identical string whether the bit value is 0 or 1?
2. Are there any special issues that should be taken into consideration when inserting/selecting data using bit(1) type column? (Quick testing shows that everything works as intended: 0 get inserted as 0 and 1 as 1, but I might be missing something.)

I'm testing this locally with MAMP: PHP 5.3.2 and MySQL 5.1.44. Command line tool refers to Sequel Pro (not MAMP's PhpAdmin). Php pages are utf-8 and queries use SET NAMES 'utf8'.

---

Update: code

CREATE TABLE `my_table` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `my_bit` bit(1) NOT NULL,
  PRIMARY KEY (`id`,`lto_muu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

INSERT INTO `my_table` (`id`, `my_bit`) VALUES (null, 0), (null,1)

//php page
//query option 1
SELECT `id`, `my_bit`  FROM `my_table`
//query option 2
SELECT `id`, CAST(`my_bit` AS UNSIGNED) AS `my_bit`  FROM `my_table`

$mysqli = new mysqli("localhost", "root", "root","my_db");
$q = "SELECT `id`, `my_bit`  FROM `my_table`";//returns 2 rows

$r = $mysqli->query($q);

while($row = mysqli_fetch_array($r,MYSQLI_ASSOC)){
echo 'id: ' . $row['id'] . ' - bit: ' . $row['my_bit'] . '<br />';
}

Query option 1 prints out:

id: 1 - bit: %qu
id: 2 - bit: %qu

Query option 2 prints out:

id: 1 - bit: 0
id: 2 - bit: 1

---

Update 2: Álvaro's code

$conn = new mysqli('localhost', 'root', 'root','test');
//$mysqli->set_charset('utf8');
$conn->query('DROP TABLE IF EXISTS bit_test');
$conn->query('CREATE TABLE bit_test (
    my_bit BIT(1) NULL,
    my_multiple_bit BIT(8) NULL
)');
$conn->query("INSERT INTO bit_test (my_bit, my_multiple_bit) VALUES (b'0', b'111')");
$conn->query("INSERT INTO bit_test (my_bit, my_multiple_bit) VALUES (b'1', b'10000000')");

//opt 1
$q = 'SELECT cast(my_bit as unsigned) as my_bit, my_multiple_bit FROM bit_test';
//opt2 
//$q = 'SELECT my_bit, my_multiple_bit FROM bit_test';
$r = $conn->query($q);
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
    echo bin2hex($row['my_bit']) . '<br />';
    echo bin2hex($row['my_multiple_bit']) . '<br />';
    var_dump($row);
    echo '<br /><br />';
}

Using PHP 5.2.12 opt 1 and 2 both print:

30
07
array(2) {
  ["my_bit"]=>
  string(1) "0"
  ["my_multiple_bit"]=>
  string(1) ""
}
31
80
array(2) {
  ["my_bit"]=>
  string(1) "1"
  ["my_multiple_bit"]=>
  string(1) "�"
}

Using PHP 5.3.2 opt 1 prints:

30
257175
array(2) {
  ["my_bit"]=>
  string(1) "0"
  ["my_multiple_bit"]=>
  string(3) "%qu"
}
31
257175
array(2) {
  ["my_bit"]=>
  string(1) "1"
  ["my_multiple_bit"]=>
  string(3) "%qu"
}

And opt 2:

257175
257175
array(2) {
  ["my_bit"]=>
  string(3) "%qu"
  ["my_multiple_bit"]=>
  string(3) "%qu"
}
257175
257175
array(2) {
  ["my_bit"]=>
  string(3) "%qu"
  ["my_multiple_bit"]=>
  string(3) "%qu"
}

PHP 5.3.2 alvays prints %qu. Does this make any sense?

Answer 1

Short answer

OK, here's are short and long answers to my own question. If you want to be sure that bit(1) column gets retrieved as an integer use the following query when retrieving bit(1) values:

$q = 'SELECT (my_bit + 0) AS my_bit, (my_multiple_bit + 0) AS my_multiple_bit FROM bit_test';

If "+ 0" is not used the value of a bit(1) column is treated as binary string. This is the moment when I say "RTM" to myself...

Something similar is found here through the magic of Google...

If you are happy with quick solution, stop reading now.

Now the messy/long answer (let's use Álvaro's code)

Let's create the table and use four different queries with two PHP versions available on my MAMP installation: 5.3.2 & 5.2.12.

$conn = new mysqli('localhost', 'root', 'root','my_db');

$conn->query('DROP TABLE IF EXISTS bit_test');
$conn->query('CREATE TABLE bit_test (
    my_bit BIT(1) NULL,
    my_multiple_bit BIT(8) NULL
)');

$conn->query("INSERT INTO bit_test (my_bit, my_multiple_bit) VALUES (b'0', b'111')");
$conn->query("INSERT INTO bit_test (my_bit, my_multiple_bit) VALUES (b'1', b'10000000')");

//q1
$q = 'SELECT (my_bit + 0) AS my_bit, (my_multiple_bit + 0) AS my_multiple_bit FROM bit_test';
//q2
//$q = 'SELECT cast(my_bit as unsigned) as my_bit, my_multiple_bit FROM bit_test';
//q3
//$q = 'SELECT HEX(my_bit) AS my_bit , HEX(my_multiple_bit) AS my_multiple_bit FROM bit_test';
//q4
//$q = 'SELECT my_bit, my_multiple_bit FROM bit_test';

$r = $conn->query($q);

//let's echo a few options
//plain gives unaltered result
//bin2hex gives hexadecimal number of an ASCII string (since) the values are treated as strings
//base16to10 gives decimal representation of hexadecimal value
//yes, the two functions are contradictionary (dependable of the query in use)
//but I'll echo their result anyway
while($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){
  echo 'plain: ' . $row['my_bit'] . '<br />';
  echo 'plain: ' . $row['my_multiple_bit'] . '<br />';
  echo 'bin2hex: ' . bin2hex($row['my_bit']) . '<br />';
  echo 'bin2hex: ' . bin2hex($row['my_multiple_bit']) . '<br />';
  echo 'base16to10: ' . base_convert($row['my_bit'],16,10) . '<br />';
  echo 'base16to10: ' . base_convert($row['my_multiple_bit'],16,10) . '<br />';
  var_dump($row);
  echo '<br /><br />';
}

Here are the results of different queries

What I'm really interested in is the plain output and var_dump, but you can also check the bin2hex and base16to10 output can be useful when looking up if the numbers have some link to their corresponding values: numbers 0, 7, 1 and 128 (the values stored into four bitcolumns of the table).

q1 – the safe bet – output is identical in PHP 5.3.2 & 5.2.12

plain: 0
plain: 7
bin2hex: 30
bin2hex: 37
base16to10: 0
base16to10: 7
array(2) { ["my_bit"]=> string(1) "0" ["my_multiple_bit"]=> string(1) "7" }

plain: 1
plain: 128
bin2hex: 31
bin2hex: 313238
base16to10: 1
base16to10: 296
array(2) { ["my_bit"]=> string(1) "1" ["my_multiple_bit"]=> string(3) "128" }

q2 – some differences in output

Works fine with bit(1) but something is quite wrong in my_multiple_bit output.

PHP 5.2.12:

plain: 0
plain: 
bin2hex: 30
bin2hex: 07
base16to10: 0
base16to10: 0
array(2) { ["my_bit"]=> string(1) "0" ["my_multiple_bit"]=> string(1) "" }

plain: 1
plain: �
bin2hex: 31
bin2hex: 80
base16to10: 1
base16to10: 0
array(2) { ["my_bit"]=> string(1) "1" ["my_multiple_bit"]=> string(1) "�" } 

PHP 5.3.2

plain: 0
plain: %qu
bin2hex: 30
bin2hex: 257175
base16to10: 0
base16to10: 0
array(2) { ["my_bit"]=> string(1) "0" ["my_multiple_bit"]=> string(3) "%qu" }

plain: 1
plain: %qu
bin2hex: 31
bin2hex: 257175
base16to10: 1
base16to10: 0
array(2) { ["my_bit"]=> string(1) "1" ["my_multiple_bit"]=> string(3) "%qu" }

q3 – output is identical in PHP 5.3.2 & 5.2.12

This is also a safe query to use. Just remember to convert retrieved hexadecimal to decimal number.

plain: 0
plain: 7
bin2hex: 30
bin2hex: 37
base16to10: 0
base16to10: 7
array(2) { ["my_bit"]=> string(1) "0" ["my_multiple_bit"]=> string(1) "7" }

plain: 1
plain: 80
bin2hex: 31
bin2hex: 3830
base16to10: 1
base16to10: 128
array(2) { ["my_bit"]=> string(1) "1" ["my_multiple_bit"]=> string(2) "80" }

q4 – output is quite strange

PHP 5.2.12 works fine with bit(1) but I wouldn't use this query if there's any doubt about PHP version in use or it's behaviour.

PHP 5.2.12

plain:
plain: 
bin2hex: 00
bin2hex: 07
base16to10: 0
base16to10: 0
array(2) { ["my_bit"]=> string(1) "" ["my_multiple_bit"]=> string(1) "" }

plain: 
plain: �
bin2hex: 01
bin2hex: 80
base16to10: 0
base16to10: 0
array(2) { ["my_bit"]=> string(1) "" ["my_multiple_bit"]=> string(1) "�" } 

PHP 5.3.2

plain: %qu
plain: %qu
bin2hex: 257175
bin2hex: 257175
base16to10: 0
base16to10: 0
array(2) { ["my_bit"]=> string(3) "%qu" ["my_multiple_bit"]=> string(3) "%qu" }

plain: %qu
plain: %qu
bin2hex: 257175
bin2hex: 257175
base16to10: 0
base16to10: 0
array(2) { ["my_bit"]=> string(3) "%qu" ["my_multiple_bit"]=> string(3) "%qu" } 

Final thoughts and note to self

RTM, test and test again.

Also it would be nice to know for sure if this %qu output is an exclusive PHP 5.3.2 bug. (Or what other PHP versions are affected.)

So, Álvaro's answer is correct in sense that use CAST or retrieve HEX values from DB. The manual revealed additional "+0" method which seems take the pain away from different PHP versions behaviour. Hence the short answer is way on the top...