16

I've been converting an app to use PDO prepared statements rather than mysqli and I'm running into a strange problem. I have some records in the database where it's expected that a field will 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'd build the query like this:

WHERE fieldName is null;

And would get the expected results when the field was null.

Now with PDO, my queries aren't returning any results and I'm not getting any errors. It just simply isn't returning the records I would expect. When I echo the built queries and run them directly in MySQL I get the expected results, but within the application there are no results returned.

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

WHERE fieldName is null;

or

WHERE fieldName <=> null;

I have also tried the standard prepared statement of: 

WHERE fieldName = :fieldName

and then binding with these kinds of statements:

$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 question, I still am not clear on the difference between bindParam and bindValue, so if it makes sense to include in your answer I would really appreciate some clarification on the subject...

j0k
  • 22,600
  • 28
  • 79
  • 90
cdwhatcott
  • 454
  • 2
  • 4
  • 14
  • Different question, but all awnsers are in here: http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo – Green Black Nov 12 '12 at 23:17
  • I've tried all the different suggestions from that question and none have worked so far... – cdwhatcott Nov 12 '12 at 23:21
  • If you are inserting or searching on null, why do you need to parametrize it? – Mike Brant Feb 24 '15 at 14:16
  • 1
    @MikeBrant because you want to search for a field that sometimes is null, sometimes it isn't... `WHERE field=:value` and :value may be null or not – the_nuts Dec 07 '20 at 11:51

3 Answers3

12

Since this question has been written, mysql introduced a spaceship operator that allows us to use a regular query to match a null value

WHERE fieldName <=> :fieldName;

will match both a null or any not null value.

So just write your query right away and execute it as usual

$stmt = $db->prepare('SELECT field FROM table WHERE fieldName <=> :fieldName;');
$stmt->execute(['fieldName' => null]);
$result = $stmt->fetchAll(); // whatever fetch method is suitable

And with dynamically built queries it's all the same.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
cdwhatcott
  • 454
  • 2
  • 4
  • 14
  • 2
    I did not think it was possible to use placeholders for an `IS` condition in a PDO statement, although I am having difficultly finding definitive proof of this. In testing, I cannot get "WHERE fieldName is :fieldName" to work, even using the exact code you provided. I can get that code to work fine if I use something other than `IS`, such as `=` or `>`. Can you elaborate on the code you used, or have any other suggestions? I'm stuck with MySQL version 5.1 though, so that may explain why it doesn't work for me (I see that you're using 5.5). – Seth McCauley Jan 05 '15 at 17:02
-1

Different question, but all awnsers are in here: How do I insert NULL values using PDO?

To sum it up:

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

Should work.You can also use:

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

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

Community
  • 1
  • 1
Green Black
  • 5,037
  • 1
  • 17
  • 29
  • 3
    `WHERE fieldName = :fieldName` will work for "bob" but not for when :fieldName is "NULL", where `WHERE fieldName IS :fieldName` will work for "NULL" but not for when :fieldName is "bob", – Timo Huovinen Sep 19 '18 at 10:02
-2

In MySql you can use the query 

select * from atable where isnull(column_name);

to check for null values.

Giorgio
  • 1,940
  • 5
  • 39
  • 64
Abul Fayes
  • 117
  • 5