Overriding fetch() for PDO when fetching using foreach

Question

I have extended PDOStatement and modified the fetch() method to typecast values of the types timestamp and arrays, in PostgreSQL, to DateTime and native array. This works as intended but I can't override the behaviour when using the statement in a foreach.

I have solved this by returning rows into an object implementing ArrayAccess, IteratorAggregate and Countable. However I'm not satisfied with that solution and just want a pure array back.

Example:

class ExtendedStatement extends PDOStatement {
    protected function __construct() {
        $this->setFetchMode(PDO::FETCH_ASSOC);
    }
    public function fetch(
        $fetch_style = PDO::FETCH_ASSOC,
        $cursor_orientation = PDO::FETCH_ORI_NEXT,
        $cursor_offset = 0)
    {
        $r = parent::fetch($fetch_style, $cursor_orientation, $cursor_offset);
        if (is_array($r)) {
            $r["extradata"] = TRUE;
        }
        return $r;
    }
}
$db = new PDO("sqlite::memory:");
$db->setAttribute(
    PDO::ATTR_STATEMENT_CLASS, array("ExtendedStatement", array($db)));
$db->exec("CREATE TABLE example(id INTEGER PRIMARY KEY, name VARCHAR)");
$db->exec("INSERT INTO example(name) VALUES('test')");

// This is what is does
$s = $db->prepare("SELECT * FROM example");
$s->execute();
foreach ($s as $r) {
    var_dump($r);
}
$s->closeCursor();

// This is how I want it to be
$s = $db->prepare("SELECT * FROM example");
$s->execute();
while ($r = $s->fetch()) {
    var_dump($r);
}
$s->closeCursor();

// This is how I want it to be
$s = $db->prepare("SELECT * FROM example");
$s->execute();
var_dump($s->fetch());
$s->closeCursor();

Output:

array(2) {
  ["id"]=>
  string(1) "1"
  ["name"]=>
  string(4) "test"
}
array(3) {
  ["id"]=>
  string(1) "1"
  ["name"]=>
  string(4) "test"
  ["extradata"]=>
  bool(true)
}
array(3) {
  ["id"]=>
  string(1) "1"
  ["name"]=>
  string(4) "test"
  ["extradata"]=>
  bool(true)
}

Can you show some code that does work as expected, and does not work as expected? If you've overridden `fetch`, it should always work "correctly." — Charles, Jul 26 '10 at 08:09
Does it work normally when you replace the foreach with `while($r = $s->fetch()) {`? Also, keep in mind that the result returned by the `parent::fetch` call may well not be an array, it could be null. You should probably add an `is_array` check or something similar. — Charles, Jul 26 '10 at 08:42
Yes it works as expected. My normal code works, but this starts looping to infinity. This code is just an example to demonstrate the bevaviour. This is not the real code. — runfalk, Jul 26 '10 at 08:44

score 3 · Accepted Answer · answered Jul 26 '10 at 09:02

3

The PDOStatement class implements the built-in internals-only Traversable interface. The iterator that it implements bypasses the public PDOStatement::fetch() method.

answered Jul 26 '10 at 09:02

Daniel Egeberg

8,359
31
44

I have realised this already, but I still want to hack into it in some way. I had an idea of implementing Iterator, but abandoned it in hope of a better solution. – runfalk Jul 26 '10 at 09:16
@antennen: Well, you have three choices really: 1) patch `ext/pdo/pdo_stmt.c`, 2) use `while` instead, 3) implement your own iterator. Regardless, I would probably recommend you to file a bug at http://bugs.php.net. It seems reasonable enough that it should call the public `fetch()` method. – Daniel Egeberg Jul 26 '10 at 09:35
Bug reported: http://bugs.php.net/bug.php?id=52444. I might try the own iterator approach for now. – runfalk Jul 26 '10 at 10:59

score 0 · Answer 2 · answered Jul 26 '10 at 09:09

0

I use something like this:

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$sql = 'select * from table';
$stmt = $db->prepare($sql);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT))
{
    // do stuff
}

So the only thing you have to do is set some options :)

answered Jul 26 '10 at 09:09

Dennis Haarbrink

3,738
1
27
54

Given that he already has that kind of code in the examples in his question, I'm pretty sure he is aware of that possibility. – Daniel Egeberg Jul 26 '10 at 09:12

Overriding fetch() for PDO when fetching using foreach

2 Answers2