Null inserted when parameter is false

Question

I am trying to insert a FALSE value into the database, with no luck - it inserts an empty row. Column - varchar. If I insert a TRUE value, it inserts 1.

I use mysql, PDO and php.

I also use the DB class, which does nothing more with the query than it prepares and executes it using the prepared PDO instructions.

This query is very simple:

$sql = 'INSERT INTO api_logs_values (value) VALUES (?)'; 

and then:

 $this->_db->query($sql, array(FALSE)); 

any ideas what is going on?

EDIT:

Added table structure:

 api_logs_values | CREATE TABLE `api_logs_values` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `log_id` int(10) unsigned NOT NULL, `name` varchar(255) DEFAULT NULL, `value` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `log_id` (`log_id`,`name`) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 

EDIT2:

I found an error report here https://bugs.php.net/bug.php?id=33876 , but it seems like this is not the right, nice solution for this. Except, possibly, setting the PDO_PARAM_BOOL attribute.

EDIT3:

I found out that the DB class that I use does not bind the parameters separately, but does ($ param) and looks here http://php.net/manual/en/pdostatement.execute.php , it treats all the parameters as PDO: : PARAM_STR, which is incorrect.

Answer 1

So, I have found a solution. The problem was that the value binding was done in the database class that I used. He was doing something like this:

 $stmt = $this->pdo->prepare($query); $stmt->execute($param); 

But from http://lt.php.net/manual/en/pdostatement.execute.php : It takes a parameter:

input_parameters An array of values ​​with as many elements as the associated parameters in the executed SQL statement. All values ​​are treated as PDO :: PARAM_STR.

What the database class was supposed to do was first call bindValue () with the corresponding parameter data_type (PDO constant). This comment summarizes: http://lt.php.net/manual/en/pdostatement.bindvalue.php#104939

Answer 2

If you want to insert the string "false", then $this->_db->query($sql, array("FALSE"));

For the varchar data type, php false is considered an empty string.

If you want TRUE be 1 and FALSE to 0 , set the data type of the tinyint column.

Answer 3

MySQL will insert values ​​that evaluate to false in columns. The actual value will vary depending on the type of data. For characteristic data types, an empty row will be inserted. For numeric types, it will insert 0. You must use the appropriate data type. (FYI, while MySQL recognizes the BOOL keyword for data type definitions, the column will actually be created as TINYINT. In this case, it will store 1 for true and 0 for false)

If you want to literally save the string "false", you need to use a string literal instead of boolean false.

Answer 4

MySQL implements booleans as follows:

BOOL, BOOLEAN These types are synonyms for TINYINT (1). A value of zero is considered false. Nonzero values ​​are considered true.

(from: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html )

Answer 5

This could be a mysql strict mode issue. I know that the command line client elegantly allows you to "convert" the string "false" to 0 for the data type TINYINT (1). He will give a warning, but he will come in. This does not apply to JDBC. PDO can act funky ..

See the JDBC error report: http://bugs.mysql.com/bug.php?id=24526

See MySQL SQLMode Strict: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_strict_all_tables

Answer 6

You should really try to map data types. Simple enough for this. The code will look like this:

 $sql = 'INSERT INTO api_logs_values (value) VALUES (?)'; $value = false; $value = ($value === false? '0':'1'); $this->_db->query($sql, array($value));