21

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

EDIT Screenshot of column info

Picture

Community
  • 1
  • 1
wmarbut
  • 4,595
  • 7
  • 42
  • 72
  • what is the column type? is that nullable? – Daniel A. White Jul 27 '12 at 17:35
  • 2
    @DanielA.White he just said he can do it via straight sql so yes it is nullable. – Jon Taylor Jul 27 '12 at 17:35
  • can you show us the rest of your code rather than just your bindValue statements? I suspect if you have tried all these options and it isn't a problem with your actual database then it must be a problem with the rest of your code. – Jon Taylor Jul 27 '12 at 17:36
  • @Daniel A. White, I've included a screenshot showing the column is nullable plus as Jon Taylor said, I can do this with straight sql. – wmarbut Jul 27 '12 at 17:40
  • @JonTaylor The code that builds the query is half a page long and I am NDA'd from sharing it. I can confirm with no doubts that my value binding call is definitely getting called and is not getting overwritten later on. – wmarbut Jul 27 '12 at 17:41
  • The column has the 'DEFAULT NULL' attribute, did you try setting the columnValue to empty then attempting the execute (save)? – Mike Purcell Jul 27 '12 at 17:41
  • @hidden_premise not much else we can do to be honest if we cant see any code, have you tried putting in a non null value with the same statement, just to check if it works? – Jon Taylor Jul 27 '12 at 17:43
  • Since the default is NULL, you can just remove the column entirely from your (I'm assuming) `INSERT/UPDATE` statement and let it handle itself. – Palladium Jul 27 '12 at 17:48
  • @Palladium wouldnt removing it from the update statement leave it as it was? Rather than making it the default value. – Jon Taylor Jul 27 '12 at 17:48
  • @JonTaylor RIGHT. Never mind. You can take it out if it's an `INSERT` statement (good catch). – Palladium Jul 27 '12 at 17:50
  • @Palladium, you can take it out of the statement if it is already NULL, but if you have it already populated with a value and need to NULL it out then the problem remains – wmarbut Jul 27 '12 at 18:50
  • Looks like our villian was me not having prepare emulation set to false `$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` – wmarbut Jul 27 '12 at 18:55

3 Answers3

17

NULL values do not require any special treatment. Simply bind your value the usual way

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$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_STR);

$stmt->execute();

PDO will detect a null value and send it to database instead of string.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
  • 8
    This... `$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);` this little guy right here was the trick. @Truth, Thanks! Great Catch! – wmarbut Jul 27 '12 at 18:54
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);
Dharman
  • 30,962
  • 25
  • 85
  • 135
Talha
  • 1,546
  • 17
  • 15
0

If you want to save an empty string as null, you can use a function like this:

function setnull($item){
    if ($item === ""){
        return NULL;
    }else{
        return $item;
    }
}

Then you can proceed with insert the usual way.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
AminMeskin
  • 1