PHP/MySQL/PDO binding null parameter doesn't work

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!

Answer 1

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.

Answer 2

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

Answer 3

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.