I can't find any info on this.

In mysql I'd write

 select id, fax from addresses where isnull(fax);

I'm trying this:

$val = NULL;
$stmt = $pdo->prepare("select id, fax from addresses where fax =:fax");
$stmt->bindParam(':fax', $val, PDO::PARAM_NULL);
assert($stmt->execute());
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
// result: no records found which is not correct, there are many

changing the prepare method call to this works:

$stmt = $pdo->prepare("select id, fax from addresses where isnull(fax)");

But I'm thinking about a db-vendor agnostic approach, which is why we use PDO in the first place, right?. In ANSI SQL isnull() is not the same as mysql isnull() as far as I can tell.

How can I use PDO to find records where a particular field is NULL in a manner that is portable across database vendors (mysql, postgres, mssql, etc)? I don't know if this is even possible.

    I had the impression that the standard SQL syntax for this is just:

    select id, fax from addresses where fax is null

    (Though I don't see why you would want to select fax when fax is null anyway.)

      Thanks laserlight. In practice I almost never look for records where some field is NULL, but since it seemed to me like a tricky issue, so I wanted to know how to do it without using isnull().

        How do I mark this UNRESOLVED? I would still like to be able to use a NULL value in my bindParam() w/ PDO, rather than explicitly writing "is null" in my SQL statement.

          jazz_snob wrote:

          How do I mark this UNRESOLVED?

          I don't think it is possible, heh.

          jazz_snob wrote:

          I would still like to be able to use a NULL value in my bindParam() w/ PDO, rather than explicitly writing "is null" in my SQL statement.

          Unfortunately, I believe that this is not possible (too). When you bind, you are binding some value (or binding a variable whose value will be accessed), but NULL is a very special value that is not really a value in some ways.

          One way out is to avoid using NULL in the first place, e.g., default to a zero length string so you can search for ''. Of course, if you want to assign different semantics to NULL and '' then you will be in a quandary.

            Thread marked unresolved.

            (FYI, laserlight, I usually do this via Thread Tools -> Edit Thread, remove [RESOLVED] tag, set thread icon to "No Icon").

              Verified cannot use bindParam() w/ a NULL as I don't think it works. I enabled query logging (everything) and ran the following code:

              // got my pdo connection to database
$val = NULL; // value is passed by ref so have to set a variable to NULL
$stmt = $pdo->prepare("select id, fax from addresses where fax =:fax");
$stmt->bindParam(':fax', $val, PDO::PARAM_NULL);
$stmt->execute();

              In the query log:

              select id, fax from addresses where fax =NULL

              Which will always yield 0 records as that is not how to test for NULL values.

                bradgrafelman wrote:

                (FYI, laserlight, I usually do this via Thread Tools -> Edit Thread, remove [RESOLVED] tag, set thread icon to "No Icon").

                Heh, nice cheat 😃

                  Write a Reply...