PDO: Iterators

[Updated to fix missing execute()]

Louis-Philippe Huberdeau posted this comment to an earlier post, asking:

I have been playing with PDO this morning, looks great! I just wonder if it could use a few more PHP5 features and SPL kind of thing.

The gist of what he was suggesting is already present. His first request was to be able to access the columns as properties of the statement handle. While you can't do that exactly, you can do something similar. His second request was being able to iterate the rows using foreach(). Here are two ways to acheive that, using alternative syntax:

<?php
   $stmt = $db->prepare("select foo from bar");
   $stmt->execute();
   $stmt->setFetchMode(PDO_FETCH_LAZY);
   foreach ($stmt as $row) {
       echo $row->foo;
   }
?>

and the less verbose version:

<?php
    foreach ($db->query("select foo from bar", PDO_FETCH_LAZY) as $row) {
        echo $row->foo;
    }
?>

PDO_FETCH_LAZY is almost exactly the same thing as having the properties show up on the statement handle; the "lazy object" you get on each iteration is internally aliased to the statement handle. If you're familiar with the concept of interfaces from other languages, the lazy object is actually the same object instance, it's just a different interface.

The implication of this is that you can't simply copy a $row into another variable and expect its contents to remain as they were at that point; $row still references the current active row of the statement, so if you fetch() another row (either direct or by advancing the iteration with foreach) $row will refer to the new row.

PDO_FETCH_LAZY doesn't mean that you can be lazy, it means that PDO can be lazy. :-)

If you want to be lazy, and still want $row->foo, use PDO_FETCH_OBJ instead; the sample above will work the same.