MySQL INSERT inserting BIT to 1 instead of 0

I have the following query which should insert a 0 but instead inserts 1. It is executed with prepared statements in PHP:

$insertsql= 
    "INSERT INTO table1 (column1, column2, column3, column4, column5, column6) 
    VALUES (?, ?, ?, ?, ?, ?)";
$insertstmt = $pdo->prepare($insertsql);
$insertstmt->execute(array($var1, $var2, $var3, $var4, $var5, 0));

All inserts are performed fine, except a 1 is inserted in column6 instead of a 0. Column 6 has the datatype BIT.

The query works fine, however, when executing

INSERT INTO table1 (column6) VALUE (0);

directly in the phpmyadmin ‘MySQL’ tab.

What am I missing?

EDIT: Using

$insertstmt->execute(array($var1, $var2, $var3, $var4, $var5, false));

works as expected. Why is 0 working directly within a SQL query but not when using pdo to execute it?

Answer

You need to explicitly declare this parameter as Boolean when passing it to PDO.

From the PDO execute documentation :

input_parameters

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

This is not what you want.

So you would need to change your logic to avoid using the execute(array()) construct and bind each parameter individually, using bindValue(). For boolean :

$insertstmt->bindValue(':col6', false, PDO::PARAM_BOOL);

Since booleans are just TINYINT(1) in MySQL, PDO::PARAM_INT should work fine as well :

$insertstmt->bindValue(':col6', 0, PDO::PARAM_INT);

---

Finally : if this boolean value will always be false, then you may pass it directly to the query, like :

$insertstmt->execute(array($var1, $var2, $var3, $var4, $var5, false));

---

As wisely commented by spencer7593, another option is to do the type casting from within the SQL. For example one could convert the string to a bit value with :

INSERT INTO table1 (column1, column2, column3, column4, column5, column6) 
VALUES (?, ?, ?, ?, ?, IF( ? ='0', b'0', b'1') )