CODEWITHSUNDEEP
phpmysqlsqlpdonull
user1032531
user1032531

Reputation: 26281

NULL in MySQL PDO WHERE statement

$id can but is not necessarily NULL. Other than using PHP to test $id for NULL, and changing the query to IS NULL, how can this query be performed?

$stmt = $db->prepare("SELECT * FROM t WHERE id=?");
$stmt->execute([$id]);
return $stmt->fetchAll();

Per https://stackoverflow.com/a/1391801/1032531, I tried bindValue(':id', null, PDO::PARAM_INT);, but get error Fatal error: Cannot pass parameter 2 by reference

I've also tried $stmt->bindParam(':id', $id, PDO::PARAM_NULL);, and while no error, I get no results.

Upvotes: 1

Views: 1355

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

What you are looking for is the ANSI standard IS NOT DISTINCT FROM operator. This returns true for NULL IS NOT DISTINCT FROM NULL.

MySQL supports this using the <=> comparison operator. So you can use:

SELECT *
FROM t
WHERE id <=> ?

This assumes that you actually want NULL values for id when you compare to NULL. Often, the desired logic is to take all rows if the parameter is NULL:

WHERE id = ? OR (? IS NULL)

or:

WHERE id = COALESCE(?, id)

Upvotes: 3

Mureinik
Mureinik

Reputation: 311308

As you've noticed, nulls in SQL aren't values, and can't be evaluated with the = operator, but have to be specifically addressed with the is operator. One neat way of doing this in one shot is to use two conditions at once, one handling the nulls and one handling the real values:

$stmt = $db->prepare("SELECT * FROM t WHERE id=? OR (id IS NULL AND ? IS NULL");
$stmt->execute([$id, $id]);
return $stmt->fetchAll();

Upvotes: 0

Related Questions