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
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.
bit type value is echoed as identical string whether the bit value is 0 or 1?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'.
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
$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?
Here's a full example on how to retrieve BIT column types:
<?php
$conn = new PDO('mysql:host=test;dbname=test', 'test', 'test');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$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')");
$res = $conn->query('SELECT my_bit, my_multiple_bit FROM test');
while($row = $res->fetch(PDO::FETCH_ASSOC)){
var_dump($row);
}
... which prints:
array(2) {
["my_bit"]=>
string(1) "0"
["my_multiple_bit"]=>
string(1) "7"
}
array(2) {
["my_bit"]=>
string(1) "1"
["my_multiple_bit"]=>
string(3) "128"
}
Edit #1:
Here's my previous code, adapted to mysqli:
<?php
$conn = new mysqli('localhost', 'test', 'test','test');
$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')");
$res = $conn->query('SELECT my_bit, my_multiple_bit FROM test');
#while($row = mysqli_fetch_array($res, MYSQLI_ASSOC)){
while($row = $res->fetch_array(MYSQLI_ASSOC)){
var_dump($row);
}
I get an additional array item with all values set to NULL (I'm not familiar with mysqli so it's probably my fault) but output is otherwise identical. I've tested it in PHP/5.3.0 and PHP/5.4.5.
I suggest you try my code verbatim and verify whether you still get %qu or something weird. I have a strong feeling that such %qu string might be leaking from somewhere else...
Edit #2:
From the additional information, I think we can conclude the following:
Earlier PHP versions retrieved BIT columns as-is, as raw binary strings (0x07 and 0x80 are the correct numbers, 7 and 128 in decimal).
At some point, automatic encoding was added and BIT columns started being retrieved as decimals.
The PHP release used by the OP probably has a bug in the encoding code: %qu reminds of me C printf modifiers (though I couldn't find its exact definition).
Given the different behaviours, a workaround is required to ensure coherent output:
CAST(my_bit AS UNSIGNED) to generate a decimal numberHEX(my_bit) to generate an hexadecimal number