Is foreach an equivalent of while in context of unbuffered queries?

Question

I am wondering -- if this code:

// example 1
$statement = $pdo->query('SELECT * FROM table'); // MySQL
while ($row = $statement->fetch())
{
  // doing something interesting
}

is equivalent of this code:

// example 2
$statement = $pdo->query('SELECT * FROM table'); // MySQL
foreach ($statement as $row)
{
  // doing something interesting
}

in context of unbuffered queries in MySQL? (I am aware the loops give the same results.)

Or in other words:

I'm sure, that executing the first example doesn't leave any data unread (according to the answer for Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active) and I don't have to bother if a next query or prepare right after the loop throws General error: 2014 Cannot execute queries while other unbuffered queries are active.
I'm not sure if the second example works in the same way: that all data is fetched.

I tried something like that:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$statement = $pdo->query('SELECT * FROM table WHERE x = 1');
while(...) OR foreach(...)
{
  // some magic
}
 OR
$statement->fetch(); // just fetch(), not fetchAll()

$statement = $pdo->query('SELECT * FROM table WHERE x = 2'); // MySQL
...

And:

In case of while or foreach the code runs without errors.
In case of just fetch(), I, as supposed, get error: 2014.

So it seems, that I answered my own question;) But I'm still not sure. I couldn't find any docs or an SO question that would answer my problem, either.

No it is not, as `$statement = $pdo->query('SELECT * FROM table');` only returns one PDOStatement object per call. But `foreach ($pdo->query('SELECT * FROM table') as $row) {` would get the same result as your while loop — RiggsFolly, Sep 05 '15 at 21:22
@RiggsFolly I'm sorry, but I don't ask if the loops give the same results: I know they do;) I am asking about the loops' identity in context of **unbuffered queries**. — jacek.ciach, Sep 05 '15 at 22:28
What version PHP are you using - earlier versions of PDO did 'fetchAll' behind the scenes unless explicitly stated otherwise. Oh joy, you switched off 'emulate prepares' with PDO while using `mysql`. At which point, then you will find it easier to appreciate what is happening by using `mysqli` directly as that is what PDO is doing. The `mysqli` documentation explains how it all works including buffered and unbuffered queries. — Ryan Vincent, Sep 06 '15 at 23:36
PHP 5.5.12. Oh, thanks a lot :) -- I will look at the `mysqli` documentation. — jacek.ciach, Sep 07 '15 at 10:54

jacek.ciach · Accepted Answer · 2015-09-08T14:07:00.727

PHP 5.5.12, Windows

I did some more tests. I created a table with 100 000 records and I checked how much memory is taken when all values in the table are summed with different methods:

+----------+-------------+--------+
| method   | memory used | time   |
+----------+-------------+--------+
| fetchAll | 69.9157 MB  | 7.20 s |  // <-- fails if memory_limit < 69 M
| while    | 0.2494 MB   | 3.24 s |
| foreach  | 0.2494 MB   | 0.98 s |  // <-- here I disagree with Ollie
+----------+-------------+--------+

Taking into account the test from my question and the tests above, I conclude that:

using foreach and while is equivalent in terms of buffering queries: both methods read row by row and after execution of the loop all data is fetched in such way, that a next query won't throw error 2014
foreach does not do something like fetchAll to get the whole resultset before iterating over PDOStatement: if it did, the test would fail when memory_limit < 69M (IMHO: it's quite logic, 'cause PDOStatement implements Traversable interface). So I cannot agree with Ollie Jones' answer (at least on my computer and environment).

score -1 · Answer 2 · answered Sep 05 '15 at 22:53

As you have observed, in php foreach works on arrays. So, when you use it on a pdo resultset, it's necessary for the pdo implementation to slurp the resultset first. That completes the unbuffered query, and soaks up RAM.

The ability to handle foreach on a stream of data is supported by recent versions of the C# language. But in php you need to use the while ($row = fetch()) construct.

Is foreach an equivalent of while in context of unbuffered queries?

2 Answers2