Non-null binding in PDO?

Question

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 will successfully retrieve all foo from the database, where the bar column is null.

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

I know that I could just replace bar = :bar with bar IS NOT NULL . However, I would like to avoid this, and instead do it using prepared instructions, because sometimes I have to build a query string dynamically, and there will be a lot of additional work for this manually.

Is it possible?

Answer 1

You cannot bind "NOT NULL". You can only bind values. "IS NOT NULL" is not a value; this is a completely different query syntax. You just have to dynamically build the query, binding a value cannot help you with this:

 $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(); 

Answer 2

I am afraid that you are mistaken in your assumption. Although you can bind NULL values ​​in general, the WHERE bar = NULL statement will not return you either a row or raw SQL or PDO. All this operator will be evaluated in NULL and will not correspond to any line.

Instead, you can use NULL-safe equal to the operator <=> to match fields that are NULL or have some value. But in order to have values ​​that are not non-zero, you still need to have a different query.