Incorrect PHP / MySQL / PDO binding parameter not working

Question

I had a problem 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 result variable will be an empty array. If I do not use the parameters and change my query to "WHERE Binary16Column IS NULL", it returns the expected number of rows. Therefore, the problem should be how I process the parameter, and not in my SQL query.

My code is more complex than the one listed above, and I need to be able to use a parameter variable, which can be null, so checking for a null variable and executing another query are less ideal. Technically, I have my own function for setting parameters, this is where I check to see if the contents of the variable are null and bind this parameter accordingly, so I don't need to write an unnecessary number of queries. Query execution also works fine if the variable contains valid data and the parameter type is PARAM_LOB.

Does anyone know what I'm doing wrong? Many thanks!

Answer 1

Read on three-digit logic . NULL is not a value; it is a marker of the absence of a value, and therefore NULL can never be equal to anything, including it.

However, there is a null-safe comparison operator, also known as a “spacecraft operator,” which considers two zeros to be equivalent.

 WHERE Binary16Column <=> :uuid 

... should do what you expected.

Answer 2

If you want to select an entry with Binary16Column equal to null, you need to use IS NULL as a 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 the result is because NULL is a keyword. Due to the way MySQL handles NULL values, I think you will need to do NULL when you search for NULL values. I spent a bunch of tests in my local database where I have NULL values. The only time it worked was when I used NULL NULL or NOT.

I'm sorry that I can no longer help (or, if I just tell you what you already know), but it looks like you will have to write separate queries or perhaps simple logic to combine the corresponding WHERE logic, depending on whether the variable is null or not.