PHP/MySQL/PDO binding null parameter doesn't work [duplicate]

Question

I am having trouble binding a null parameter in the following code

$nullVariable = NULL;
$sql = new PDO('mysql:host=' . $Server, $User, $Password);
$sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$statement = $sql->prepare("SELECT * FROM Table WHERE Binary16Column = :uuid");
$statement->bindParam(":uuid", $nullVariable, PDO::PARAM_NULL);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);

The results variable will be a empty array. If I dont use parameters and modify my query to "WHERE Binary16Column IS NULL" it returns the expected number of rows. So the problem must be with how I am handling the parameter, rather than my SQL query.

My code is more complex than listed above, and I need to be able to use a parameter variable which may be null, so checking to see the variable is null and running a different query is less than ideal. Technically I have my own function for setting parameters, this is where I am checking if the contents of the variable is null, and binding the parameter appropriately, so I dont have to write an unnecessary number of queries. The query works also works fine if the variable contains valid data, and the parameter type is PARAM_LOB.

Does anyone know what i'm doing wrong? Thanks a lot!

@Michael, i've tried using bindValue too with no success. Unfortuently I think xdazz and dasfisch below are correct. — Matt
– Matt, Commented Dec 29, 2012 at 5:07
So in my function which sets the parameters, I am using preg_replace("/=\s+" . $parameter . "/", " IS NULL", $statement->queryString) to rewrite the queries if the contents of the variable is null so I dont have to write twice the number of queries anytime I might be checking to see if something equals null. — Matt
– Matt, Commented Dec 29, 2012 at 5:32

Michael - sqlbot · Accepted Answer · 2012-12-29 06:51:35Z

8

Read up on three-valued logic. NULL is not a value; it is a marker for the absence of a value, and so NULL can never be equal to anything, including itself.

However, there is a null-safe comparison operator also known as the "spaceship operator," which does consider two nulls to be equivalent.

WHERE Binary16Column <=> :uuid

... should do what you expected.

answered Dec 29, 2012 at 6:51

Michael - sqlbot

181k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

Thank you! That is exactly what I was looking for, and is much better/safer solution that what I had done rewriting the queries with a regex if the variable for the parameter was null. As far as it being incapable of being equal to itself, it can in PHP (and I think javascript). $var = null; if($var === null) will work.

Michael - sqlbot Over a year ago

Some programming languages may handle null/undef values differently, but in MySQL and in relational databases in general, NULL is treated as described and requires special handling.

xdazz · Accepted Answer · 2012-12-29 04:58:41Z

5

If you want to select the record with Binary16Column is null, you need to use IS NULL as the condition, but not = NULL.

SELECT * FROM Table WHERE Binary16Column IS NULL

You need to do:

$uuid = /**some value**/;

$sql = new PDO('mysql:host=' . $Server, $User, $Password);
$sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

if ($uuid === null) {
  $statement = $sql->prepare("SELECT * FROM Table WHERE Binary16Column IS NULL");
} else {
  $statement = $sql->prepare("SELECT * FROM Table WHERE Binary16Column = :uuid");
  $statement->bindParam(":uuid", $uuid);
}

$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);

edited Dec 29, 2012 at 4:58

answered Dec 29, 2012 at 4:52

xdazz

161k38 gold badges255 silver badges278 bronze badges

2 Comments

Matt Over a year ago

I was hoping PDO would rewrite my query so that it is that way if I passed a PARAM_NULL. Just wondering, but what is the point of PARAM_NULL then? Is it only good for inserts/updates?

xdazz Over a year ago

@mattburnett PDO won't rewrite query for you, you need to make your where condition your self, for PARAM_NULL, it is not always necessary. If you use php's null, PDO will insert db's NULL for you.

Sebastian Frohm · Accepted Answer · 2012-12-29 04:58:07Z

2

I think the reason you are not getting a result because NULL is a keyword. Because of the way MySQL treats NULL values, I think you are going to have to do IS NULL, when you are performing a search for NULL values. I did a bunch of tests in my local database where I have NULL values. The only time that it worked is when I was using IS NULL or IS NOT NULL.

I am sorry I can't be more help (or if I'm just telling you what you already know), but it seems like you are going to have to write separate queries, or perhaps some simple logic to concatenate the appropriate WHERE logic, depending on whether a variable is null or not.

answered Dec 29, 2012 at 4:58

Sebastian Frohm

4175 silver badges16 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL/PDO binding null parameter doesn't work [duplicate]

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Linked

Related