System: Debian Jessie (on cubietruck, armv7)
PHP and MySQL have been compiled from the latest sources (PHP 7.1.9, MySQL 5.7.19). PHP info: https://ibb.co/ggbsZQ
When I'm trying to retrieve bit(1) column value using mysqli or pdo it returns string(20), basically it is a huge number not a one bit.
Table structure and data:
create table TestValue (
Id int not null auto_increment
,StringValue nvarchar(32)
,BitValue bit(1)
,TinyintValue tinyint(1)
,IntValue int
,DoubleValue double
,DateTimeValue datetime
,primary key (Id)
) engine=InnoDB default charset=utf8 collate=utf8_general_ci;
insert into TestValue (StringValue, BitValue, TinyintValue, IntValue, DoubleValue, DateTimeValue)
values (N'Test string 2', 1, 1, 42, 3.141517, '2017-09-01 12:45:11')
,(N'String 2', 0, 0, 128, 4.5, '2017-09-01')
,(null, null, null, null, null, null);
The test code:
echo "pdo:\n";
$pdo = new PDO("mysql:host=localhost;dbname=$db", $user, $password);
$stmt = $pdo->prepare('select BitValue, TinyintValue from TestValue');
$stmt->execute();
$rowset = $stmt->fetchAll(PDO::FETCH_OBJ);
var_dump($rowset);
echo "\nmysqli:\n";
$mysqli = new mysqli('localhost', $user, $password, $db);
$result = $mysqli->query('select BitValue, TinyintValue from TestValue');
while ($row = $result->fetch_assoc())
{
var_dump($row);
}
The results:
pdo:
array(3) {
[0]=>
object(stdClass)#4 (2) {
["BitValue"]=>
string(20) "12999622736513859585"
["TinyintValue"]=>
string(1) "1"
}
[1]=>
object(stdClass)#5 (2) {
["BitValue"]=>
string(20) "12999622757988696064"
["TinyintValue"]=>
string(1) "0"
}
[2]=>
object(stdClass)#6 (2) {
["BitValue"]=>
NULL
["TinyintValue"]=>
NULL
}
}
mysqli:
array(2) {
["BitValue"]=>
string(20) "12999763474002214913"
["TinyintValue"]=>
string(1) "1"
}
array(2) {
["BitValue"]=>
string(20) "12999763495477051392"
["TinyintValue"]=>
string(1) "0"
}
array(2) {
["BitValue"]=>
NULL
["TinyintValue"]=>
NULL
}
I could use tinyint(1) instead of bit(1) however many projects use bit to store bool values and probably those projects won't work correctly on my environment.
Main question is how to retrieve bit value correctly?