PHP mysql PDO refuses to set NULL value

I am unable to set a nullable field with a default value of null to null using mysql pdo. I can do it using straight sql.

I have tried: (mostly from this question How do I insert NULL values using PDO?)

  1. Null Int
    bindValue(':param', null, PDO::PARAM_INT);
  2. Null Null
    bindValue(':param', null, PDO::PARAM_NULL);
  3. ‘Null’, Int
    bindValue(':param', 'NULL', PDO::PARAM_INT);
  4. ‘Null’, Null
    bindValue(':param', 'NULL', PDO::PARAM_NULL);
  5. Null
    bindValue(':param', null);
  6. ‘Null’
    bindValue(':param', 'NULL');
  7. and the bindParam counterparts of 5 and 6 with a variable that held the value of the binding.

Everything from PDO results in the value being set to 0.

PHP Version: PHP 5.3.2-1ubuntu4.10

MYSQL SERVER VERSION: 5.1.63-0ubuntu0.10.04.1

Screenshot of column info



Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The following works for me:


$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_NULL);


Pass in PHP’s null, with type of PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.

Method 2

I will strong recommend to first assign all parameters to variables and then pass those variables to the bindParam() method.

You can assign by passing NULL to those variables and it will work fine.

$myVar = NULL;
$conn->bindParam(':param1' , $myVar , PDO::PARAM_STR);

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x