using PDO to find records where field is NULL
Results 1 to 8 of 8

Thread: using PDO to find records where field is NULL

  1. #1
    Senior Member jazz_snob's Avatar
    Join Date
    Aug 2004
    Location
    California, USA
    Posts
    436

    using PDO to find records where field is NULL

    I can't find any info on this.

    In mysql I'd write
    Code:
     select id, fax from addresses where isnull(fax);
    I'm trying this:
    PHP Code:
    $val NULL;
    $stmt $pdo->prepare("select id, fax from addresses where fax =:fax");
    $stmt->bindParam(':fax'$valPDO::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:
    PHP Code:
    $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.
    Last edited by jazz_snob; 01-02-2011 at 12:04 PM.
    "Sometimes it's to your advantage for people to think you're crazy" -Thelonious Monk
    $life->setMeaning(42);
    phpfunk.com...now in blog flavor

  2. #2
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,538
    I had the impression that the standard SQL syntax for this is just:
    Code:
    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.)
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

  3. #3
    Senior Member jazz_snob's Avatar
    Join Date
    Aug 2004
    Location
    California, USA
    Posts
    436
    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().
    "Sometimes it's to your advantage for people to think you're crazy" -Thelonious Monk
    $life->setMeaning(42);
    phpfunk.com...now in blog flavor

  4. #4
    Senior Member jazz_snob's Avatar
    Join Date
    Aug 2004
    Location
    California, USA
    Posts
    436
    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.
    "Sometimes it's to your advantage for people to think you're crazy" -Thelonious Monk
    $life->setMeaning(42);
    phpfunk.com...now in blog flavor

  5. #5
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,538
    Quote Originally Posted by jazz_snob
    How do I mark this UNRESOLVED?
    I don't think it is possible, heh.

    Quote Originally Posted by jazz_snob
    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.
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

  6. #6
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,399
    Thread marked unresolved.

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

  7. #7
    Senior Member jazz_snob's Avatar
    Join Date
    Aug 2004
    Location
    California, USA
    Posts
    436
    Verified cannot use bindParam() w/ a NULL as I don't think it works. I enabled query logging (everything) and ran the following code:
    PHP 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'$valPDO::PARAM_NULL);
    $stmt->execute(); 
    In the query log:
    Code:
    select id, fax from addresses where fax =NULL
    Which will always yield 0 records as that is not how to test for NULL values.
    "Sometimes it's to your advantage for people to think you're crazy" -Thelonious Monk
    $life->setMeaning(42);
    phpfunk.com...now in blog flavor

  8. #8
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,538
    Quote Originally Posted by bradgrafelman
    (FYI, laserlight, I usually do this via Thread Tools -> Edit Thread, remove [RESOLVED] tag, set thread icon to "No Icon").
    Heh, nice cheat
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •