PHP mysql PDO refuses to set NULL value [duplicate]

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

Null Int

bindValue(':param', null, PDO::PARAM_INT);

Null Null

bindValue(':param', null, PDO::PARAM_NULL);

'Null', Int

bindValue(':param', 'NULL', PDO::PARAM_INT);

'Null', Null

bindValue(':param', 'NULL', PDO::PARAM_NULL);

Null
```
bindValue(':param', null); 
```
'Null'
```
bindValue(':param', 'NULL'); 
```
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

@DanielA.White he just said he can do it via straight sql so yes it is nullable. — Jon Taylor
– Jon Taylor, Commented Jul 27, 2012 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
– Jon Taylor, Commented Jul 27, 2012 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
– wmarbut, Commented Jul 27, 2012 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
– wmarbut, Commented Jul 27, 2012 at 17:41

Your Common Sense · Accepted Answer · 2024-03-04 12:29:00Z

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

This... $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); this little guy right here was the trick. @Truth, Thanks! Great Catch!

Dharman · Accepted Answer · 2021-02-15 13:57:35Z

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

Your Common Sense · Accepted Answer · 2023-03-20 12:16:17Z

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.

Collectives™ on Stack Overflow

PHP mysql PDO refuses to set NULL value [duplicate]

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related