How to bind a value if I want it to accept 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 the folder has parent_folder_id, then it is inside another folder. If this column is NULL, this means that this is the root folder.

From my understanding, if $ folder_id is NULL, then it will consider it equal to 0 (therefore, I do not get any results with the code, since this value in this column is NULL, not 0). If I change the third argument to PDO :: PARAM_NULL, I still do not get any results. I believe this is because it evaluates the query as "WHERE parent_folder_id = NULL", which does not match "WHERE parent_folder_id NULL".

Is there a way for PDO to relate to this correctly or do I need to create an SQL statement using inline if I change "=" to "is" and replace the third parameter of bindValue with the correct one?

Answer 1

If you use MySQL, you can use the non-standard NULL-safe equal operator <=> , which can compare either null or non-zero values.

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

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

ANSI SQL defines the IS [NOT] DISTINCT FROM predicate, which works similarly, but is not supported by all providers (yet).

Answer 2

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

If you ever switch to another database and you need to update the query, some of them have NOT DISTINCT FROM , which does roughly the same thing.

Answer 3

Maybe this?

 <?php $stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id = :id' ); if (is_null($folder_id)) { $stmt->bindParam( ':id',null, PDO::PARAM_NULL ); } else { $stmt->bindParam( ':id', $folder_id, PDO::PARAM_INT ); } 

Not verified though

EDIT:

Something like that:

  <?php if (is_null($folder_id)) { $stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id IS NULL' ); } else { $stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id = :id' ); $stmt->bindParam( ':id', $folder_id, PDO::PARAM_INT ); } 

I know this is not optimal, but PDO is still buggy ...