1

$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.

Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387

2 Answers2

3

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Would this not return all records if `$id` is `NULL`? If `$id` is `NULL`, I only which to return records where `id` is `NULL`. – user1032531 Jun 12 '16 at 13:44
  • Ah, I see how `<=>` works now. http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to. Thanks! Is it a MySQL only operator? – user1032531 Jun 12 '16 at 13:50
  • @user1032531 . . . Probably. Given that the ANSI standard is `IS [NOT] DISTINCT FROM`, then other databases would support the standard version. – Gordon Linoff Jun 12 '16 at 23:19
0

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();
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thans Mureinik, I wouldn't have thought of that. I am messing around with `bindParam()`. Do you know whether it will also work? – user1032531 Jun 12 '16 at 13:14
  • @user1032531 `bindParam()` is a longer way of doing the same thing, essentially (and more powerful one, at that, as it lets you specify the type of variable). The same technique should work there too. – Mureinik Jun 12 '16 at 13:17
  • Do you know how `bindParam()` would be used? I am trying it but getting the same results as just passing an array using `?` or `:id`. – user1032531 Jun 12 '16 at 13:21