CODEWITHSUNDEEP
phpmysqlpdo
wmarbut
wmarbut

Reputation: 4685

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

Upvotes: 23

Views: 24597

Answers (3)

Madara's Ghost
Madara's Ghost

Reputation: 174957

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();

Upvotes: 18

AminMeskin
AminMeskin

Reputation: 9

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.

Upvotes: 0

Talha
Talha

Reputation: 1636

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

Upvotes: 2

Related Questions