8

I recently found out that you can bind null values in PDO:

$stmt = $db->prepare('SELECT * FROM foo WHERE bar = :bar'); $stmt->execute(array(':bar'=>null)); $foo = $stmt->fetchAll(PDO::FETCH_OBJ);

This would successfully fetch all foo from the database, where the bar column is null.

However, I would now like to do the opposite. I would like to fetch all columns where the bar column is not null.

I am aware I could simply replace bar = :bar with bar IS NOT NULL. However, I would like to avoid that, and instead do it through prepared statements, because I sometimes have to build the query string dynamically and having to do it manually would be a lot of extra work.

Is this possible?

Improve this question
9
  • You could use query builder library like pixie and then you could build dynamic queries easily or make your own query builder Commented Oct 9, 2015 at 9:03
  • You build two statements then, one where you pass values and the other where you use IS NOT NULL. PDO will quote the parameters. Commented Oct 9, 2015 at 9:19
  • @Mjh PDO/MySQL will treat the parameters as values. There may or may not be any quoting involved; ideally there isn't any quoting involved if you're using native prepared statements. It's simply a difference in treating parameters as part of the syntax or as values. Commented Oct 9, 2015 at 9:21
  • @deceze looking at the code OP posted, PDO will quote the parameters. There's no implicit stating of what the parameter type is. Commented Oct 9, 2015 at 9:22
  • @Mjh Understand that prepared statements are a native API supported by the database. The database itself will receive the prepared statement and the bound values separately. They're not even actually concatenated into the query string, hence there's no need for quoting them! That's what I'm saying. Commented Oct 9, 2015 at 9:40

2 Answers 2

Reset to default
10

You cannot bind "NOT NULL". You can only bind values. "IS NOT NULL" is not a value, it's completely different query syntax. You will simply have to dynamically build your query, value binding cannot help you with that:

$query = 'SELECT ... WHERE '; if (/* condition is NOT NULL */) { $query .= 'foo IS NOT NULL'; $stmt = $db->prepare($query); } else { $query .= 'foo = :foo'; $stmt = $db->prepare($query); $stmt->bindValue('foo', $foo); } $stmt->execute();
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Alright, I guess that settles it. I thought this would be harder than it actually was. Thanks for the help.
thank you for the straight answer
5

I am afraid you are wrong with your assumption. Although you can bind NULL values in general, WHERE bar = NULL statement won't return you any rows, neither with raw SQL or PDO. This whole statement will be evaluated to NULL and won't match any row.

Instead, you can use a NULL-safe equal to operator, <=>, to match fields that are either NULL or have some value. But to have values that are not null, you still have to have another query.

Improve this answer

Comments