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

$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?

---

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

---

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.

---

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 tested though

EDIT :

More something like this :

  <?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 it's not optimal, but PDO is still buggy...