PHP mysql PDO refuses to set NULL value

Question

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

Answer 1

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.

In case of WHERE clause, where your value could be either null or not null, use Mysql's spaceship operator:

$stmt = $sql->prepare("SELECT * FROM table WHERE col <=> :var");
$stmt->bindParam(":var", $nullOrNot);
$statement->execute();