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

Answers:

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:

<?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.

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 stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0