4

I've this column in MySql: 

  male      BOOLEAN  COMMENT "true for male",

I'm using this query to insert using PDO wrapper in Drupal:

$id = db_insert('Person')->fields(array(
         .......      
        'male' => false,
     )
)->execute();

I'm getting this error:

WD php: PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect ^[[31;40m^[[1m[error]^[[0m integer value: '' for column 'male' at row 1: INSERT INTO .....

But if I replace with 'male'=>true, the query works. Surprisingly if I replace 0 or (int)false with false then also it works!

It means false is getting converted to '' ( empty string) in this context.

I'd like to know why it's getting converted to a string here.

I've got related question here: In PHP why is true cast 1 and false cast an empty string? but it does not answer why PHP is casting false to string?

Community
  • 1
  • 1
user5858
  • 1,082
  • 4
  • 39
  • 79
  • 1
    Maybe this helps: http://stackoverflow.com/questions/137487/null-vs-false-vs-0-in-php – Martin Joó Oct 26 '15 at 06:58
  • 2
    BOOLEAN in mysql is just a TINYINT, so all values must resolve to an integer at some point. It may be best to reference the db value as is/will be stored and avoid any conversion. – JRD Oct 26 '15 at 07:06
  • @MartinJoó no it does not – user5858 Oct 26 '15 at 08:01

1 Answers1

0

This is a bug (either PHP PDO or MySQL). Boolean FALSE should be converted to int 0, but instead is converted to an empty string. See the link to the very old bug report in MySql in the following question:

Duplicate of: PDO-MySQL: Boolean values get converted to 1 or empty string on prepared statement binding

$values = array_map(function ($v) { return is_bool($v) ? (int) $v : $v; }, $values)
Dan Chadwick
  • 361
  • 3
  • 7