Creating PDO Iterator

Question

I'm flowing this article to create Database Iterator. I've similar tables and records. But when I executing I'm getting blank page. I think issue on $data = new DbRowIterator($stmt);, It's not returning proper iterator to run accept method in FilterIterator class. I'm using laravel

a blank page could mean syntax errors; check for them. – Funk Forty Niner Sep 09 '16 at 11:43 — Funk Forty Niner, Sep 09 '16 at 11:43
Guys. no syntax errors. I've `var_dump` its returning empty – KKK Sep 09 '16 at 11:50 — KKK, Sep 09 '16 at 11:50

Your Common Sense · Accepted Answer · 2017-10-02T11:36:29.267

This article is a hoax. It claims that offered technique would speed up database calls with PDO, but in fact it slows them down significantly.

Premises on which it is grounded are also wrong. PDOStatement is already traversable, you don't need no tricks to iterate over PDOStatement using foreach.

The benchmarking section (as it often happens) is a blatant swindle. The guy is comparing fetchAll(), which obviously consumes a lot of time/memory, with fetching a single row at a time. And even this way his timing is much worse than with a proper solution.

The guy who wrote this article knows no PDO and - worse yet - no SQL.

Everything he invented already exists in PDO and SQL:

PDOStatement is already traversable. No need to reinvent the wheel.
The most important part: filtering has to be done in SQL, not in PHP. That's a textbook rule. If you need only 63992 out of 250000 records, you should select only 63992 in the first place. And if you ask a database to select the records for you, it will be incomparable faster.

So, to get everything this guy wrote with such effort, you need only few lines with PDO

$period = date_create("last_week")->format('Y-m-d 00:00:00');
$sql = 'SELECT * FROM `gen_contact` WHERE contact_modified  > ? ORDER BY `contact_modified` DESC';
$stmt = $pdo->prepare($sql);
$stmt->execute([$period]);
foreach ($stmt as $row) {
    echo sprintf(
        '%s (%s)| modified %s',
        $row->contact_name,
        $row->contact_email,
        $row->contact_modified
    ) . PHP_EOL;
}

And this code will be a multitude times faster as it does the filtering on the database level, instead of fetching the whole table and then filtering on PHP side.

As of the error you get, just set PDO in exception mode and watch for the regular PHP errors.

Thanks for this. What I'am looking for I want to change the cursor pointer when fetching. for example, I've 100 rows record and i want to start with 50 row — KKK, Sep 09 '16 at 11:56
Again you need SQL for this. Look for LIMIT clause. `SELECT * FROM t LIMIT 50, 50` will give you desired data — Your Common Sense, Sep 09 '16 at 11:58
This is what I'm looking for http://stackoverflow.com/questions/7018595/sql-offset-only. Still MySQL doesn't support this feature — KKK, Sep 10 '16 at 07:23
@Mifas well, you are doing something wrong, but ok. For just 100 rows it doesn't really matter — Your Common Sense, Sep 10 '16 at 07:26
Actually I'm working with 400k records with multiple table joins. bit hard to explain it here — KKK, Sep 10 '16 at 07:46
@Mifas 400k is not something unusual and joins are rather common thing too. But nobody needs offset. Then you have trouble explaining what are you doing, most likely you are doing it wrong — Your Common Sense, Sep 10 '16 at 08:06

score 5 · Answer 2 · answered Jun 07 '17 at 16:12

PDOStatement is not iterable, it's traversable:
It doesn't implements the \Iterable interface but the \Traversable interface, meaning it can be used in a foreach but you can't iterate manually over it because it doesn't expose it's inner behavior.

I don't think this makes much difference in the present case but there is still a major difference between the two interfaces.

I stumbled upon this question looking for a way to properly iterate over a PDOStatement (without using a foreach) and the article mentioned, although pretty clumsy looks actually fine to me.

score 4 · Answer 3 · answered Oct 02 '17 at 10:36

4

As @VincentChalnot said - PDO doesn't return instances of the Iterable class, but it does return Traversable's, and it turns out that PHP provides an Iterator for Traversable objects - the IteratorIterator (http://php.net/manual/en/class.iteratoriterator.php).

So if you want to use any of PHP's built in Iterator classes, for example the CachingIterator, with a PDOStatement then you would do it like this:

$iterator = new CachingIterator(new IteratorIterator($stmt));

answered Oct 02 '17 at 10:36

Angry Dan

3,241
2
21
18

It is important to understand that most classes that do not implement Iterators have reasons as most likely they do not allow the full Iterator feature set. If so, techniques should be provided to prevent misuse, otherwise expect exceptions or fatal errors. – Your Common Sense Oct 02 '17 at 11:30
Okay @YourCommonSense I'm interested now... what features of the Iterator class could you not implement whilst still making something internally Traversable? Is there anything specifically in PDO I should be worried about? – Angry Dan Oct 03 '17 at 08:16
Well it's not me who wrote that, it's a quote from the manual page you linked to. But I suppose something like http://php.net/manual/en/cachingiterator.offsetexists.php may fail? – Your Common Sense Oct 03 '17 at 08:25

Creating PDO Iterator

3 Answers3

Linked