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
- SELECT * FROM my_table WHERE my_bit=0
- 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
- How come the returned
bittype value isechoedas identicalstringwhether thebitvalue is 0 or 1? - 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?
id: 2 - bit: 1
` with both queries. – Álvaro González Feb 27 '13 at 11:36