How to bind a value if I want it to accept both INT and NULL with PDO?

Question

$stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id = :id' );
$stmt->bindValue( ':id', $folder_id, PDO::PARAM_INT );

I have the code above. If a folder has a parent_folder_id then it means it is inside another folder. If this column is NULL then it means that it is a root folder.

From my understanding if $folder_id is NULL then it will treat it as 0 (so I get no results with the code as it is because the value in that column is NULL and not 0). If I change the third argument to PDO::PARAM_NULL I still get no results. I believe this is because it evaluates the query as "WHERE parent_folder_id = NULL" which doesn't equal the same as "WHERE parent_folder_id is NULL".

Is there a way to have PDO treat this correctly or should I create my SQL statement with an inline if to change the "=" with "is" and swap the bindValue third parameter with the correct one?

Answer 1

If you're using MySQL, you can use the nonstandard NULL-safe equal operator <=>, which can compare either null or non-null values.

$stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id <=> :id' );
$stmt->bindValue( ':id', $folder_id, PDO::PARAM_INT );

This operator always returns true or false, not NULL, which is what you get if you try to compare anything to NULL with equals =.

ANSI SQL defines a predicate IS [NOT] DISTINCT FROM which works similarly, but it's not supported by all vendors (yet).

Answer 2

You can use the NULL-safe equal operator, <=>. Your query should be SELECT id, name FROM folders WHERE parent_folder_id <=> :id

If you ever change to another database and need to update the query, some of them has an NOT DISTINCT FROM which does about the same thing.