Selecting rows where the field is null using prepared PHP PDO and MySQL statements

Question

I converted the application to use prepared PDO instructions, not mysqli, and I am having a strange problem. I have some entries in the database where it was expected that the field would be null. Not 'null' (string), or '' (empty string), but NULL. I build my queries dynamically, so in the past, when I came across a null variable in an object, I would build the query as follows:

WHERE fieldName is null; 

And got the expected results when the field was zero.

Now with PDO, my queries do not return any results, and I get no errors. It just doesn't return the records I would expect. When I repeat the constructed queries and run them directly in MySQL, I get the expected results, but the results are not returned in the application.

Some of the things I tried include building queries that look like this:

 WHERE fieldName is null; 

or

 WHERE fieldName <=> null; 

I also tried the standard prepared statement:

 WHERE fieldName = :fieldName 

and then binding to these types of operators:

 $stmt->bindParam(":$field", $value); $stmt->bindParam(":$field", $value, PDO::PARAM_NULL); $stmt->bindParam(":$field", null, PDO::PARAM_NULL); $stmt->bindValue(":$field", null, PDO::PARAM_NULL); $stmt->bindValue(":$field", null, PDO::PARAM_INT); 

Any help with this would be greatly appreciated. My PHP version is 5.3.10 and MySQL is 5.5.22. As a side issue, I still don't quite understand the difference between bindParam and bindValue, so if it makes sense to include in your answer, I would really appreciate some clarification on this ...

Answer 1

Turns out it was a combination of some answers to this question: How to insert NULL values ​​using PDO? (as John pointed out)

I needed to change the where clause:

 WHERE fieldName is :fieldName; 

And then pass an array of field value => to the execution function:

 $binds = array(); foreach ($wheres as $where) { $field = $where['field']; $value = $where['value']; $binds[":$field"] = $value; } $stmt->execute($binds); 

The request will not work at all with bindValue, even changing the where clause. Does anyone know why the binding does not work, but did this other method?

Answer 2

A different question, but there are all awnsers here: How to insert NULL values ​​using PDO?

Summarizing:

 $stmt->bindValue(':param', null, PDO::PARAM_INT); 

Must work. You can also use:

 $stmt->execute( array(":param" => NULL)); 

For bindparam, you must specify a value in a variable (not a constant), bindvalue, you can use constants, etc.

Answer 3

In MySql you can use a query

 select * from atable where isnull(column_name); 

to check for null values.