7

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!

Matt
  • 774
  • 1
  • 10
  • 28
  • Have a look at http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo – Michael Dec 29 '12 at 04:56
  • @Michael, i've tried using bindValue too with no success. Unfortuently I think xdazz and dasfisch below are correct. – Matt Dec 29 '12 at 05:07
  • So in my function which sets the parameters, I am using preg_replace("/=\s+" . $parameter . "/", " IS NULL", $statement->queryString) to rewrite the queries if the contents of the variable is null so I dont have to write twice the number of queries anytime I might be checking to see if something equals null. – Matt Dec 29 '12 at 05:32

3 Answers3

8

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.

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • Thank you! That is exactly what I was looking for, and is much better/safer solution that what I had done rewriting the queries with a regex if the variable for the parameter was null. As far as it being incapable of being equal to itself, it can in PHP (and I think javascript). $var = null; if($var === null) will work. – Matt Dec 29 '12 at 09:25
  • Some programming languages may handle null/undef values differently, but in MySQL and in relational databases in general, `NULL` is treated as described and requires special handling. – Michael - sqlbot Dec 29 '12 at 18:05
5

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);
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • I was hoping PDO would rewrite my query so that it is that way if I passed a PARAM_NULL. Just wondering, but what is the point of PARAM_NULL then? Is it only good for inserts/updates? – Matt Dec 29 '12 at 05:01
  • 2
    @mattburnett PDO won't rewrite query for you, you need to make your where condition your self, for `PARAM_NULL`, it is not always necessary. If you use php's `null`, PDO will insert db's `NULL` for you. – xdazz Dec 29 '12 at 05:05
2

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.

Sebastian Frohm
  • 418
  • 5
  • 16