wmarbut wmarbut - 4 months ago 137
PHP Question

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


EDIT
Screenshot of column info

Picture

Answer

The following works for me:

<?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_NULL);

$stmt->execute();

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

Comments