user1032531 user1032531 - 5 months ago 18
SQL Question

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 http://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.

Answer

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