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

Question

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...

Different question, but all awnsers are in here: stackoverflow.com/questions/1391777/… — Green Black
– Green Black, Commented Nov 12, 2012 at 23:17
I've tried all the different suggestions from that question and none have worked so far... — cdwhatcott
– cdwhatcott, Commented Nov 12, 2012 at 23:21
If you are inserting or searching on null, why do you need to parametrize it? — Mike Brant
– Mike Brant, Commented Feb 24, 2015 at 14:16
@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
– the_nuts, Commented Dec 7, 2020 at 11:51

Your Common Sense · Accepted Answer · 2020-12-07 14:34:01Z

13

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.

edited Dec 7, 2020 at 14:34

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Nov 12, 2012 at 23:36

cdwhatcott

4722 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Seth McCauley Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:00:00Z

-2

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.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Nov 12, 2012 at 23:22

Green Black

5,0571 gold badge19 silver badges29 bronze badges

1 Comment

Timo Huovinen Over a year ago

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",

Giorgio · Accepted Answer · 2015-02-24 14:10:49Z

-2

In MySql you can use the query

select * from atable where isnull(column_name);

to check for null values.

edited Feb 24, 2015 at 14:10

Giorgio

1,9706 gold badges41 silver badges70 bronze badges

answered Dec 30, 2013 at 1:19

Abul Fayes

1571 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related