CODEWITHSUNDEEP
phpmysqlpdo
donk
donk

Reputation: 1570

empty value inserted when parameter is false

I'm trying to insert FALSE value in the database, without any luck - it inserts an empty string. The column is varchar. If I insert TRUE value, it inserts 1.

I'm using mysql, PDO and php.

I'm also using a DB class which does nothing more with the query than preparing and executing it using PDO prepared statements.

The query in mind is a very basic one:

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

and then:

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

any ideas what is happening?

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've found a bug report concerning this here https://bugs.php.net/bug.php?id=33876, but there doesn't seem to be a proper, nice solution to this. Except maybe setting the PDO_PARAM_BOOL attribute.

EDIT3:

I've found out that the DB class that I am using does not bind the params separately, but does execute($param), and looking here http://php.net/manual/en/pdostatement.execute.php, it treats all params as PDO::PARAM_STR, which is not correct.

Upvotes: 5

Views: 2339

Answers (6)

donk
donk

Reputation: 1570

So I've found the solution. The problem was in the way binding of the values was done in the database class I was using. It was doing something like this:

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

But stating 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 there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

What the database class had to do was call bindValue() first with an appropriate data_type parameter (PDO constant). This comment sums it up nicely: http://lt.php.net/manual/en/pdostatement.bindvalue.php#104939

Upvotes: 1

Andy Pieters
Andy Pieters

Reputation: 343

You should really try to match the datatypes. One simple if is sufficient to achieve 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));

Upvotes: 0

technocrat
technocrat

Reputation: 3705

This may be a "strict mode" issue of mysql. I know the command line client gives gracefully to help "convert" the string 'false' into 0 for datatype TINYINT(1). It will produce a warning, but it'll go in. This is not the case for JDBC does not. PDO may be acting funky..

See JDBC bug 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

Upvotes: 1

svoop
svoop

Reputation: 3454

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)

Upvotes: 2

GordonM
GordonM

Reputation: 31750

MySQL will insert values that evaluate to false into columns. Depending on the datatype the actual value will differ. For character-based datatypes, an empty string will be inserted. For numerical types it will insert 0. You should use an appropriate data type. (FYI, while MySQL recognises the keyword BOOL for datatype 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 store the string "false" then you need to use a string literal instead of boolean false.

Upvotes: 3

xdazz
xdazz

Reputation: 160883

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

For datatype varchar, php value of false is considered as empty string.

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

Upvotes: 3

Related Questions