using "while" statement while "selecting"

[AdamSEY]

I guess using "while" statement while "Selecting" is not possible right?

Using "while" statement while "Selecting" much better for performance. Because storing the data in a variable then looping to edit something is not preferred.

[ocram]

Thanks, good question!

You seem to be referring to the difference between the following two approaches:

$rows = $stmt->fetchAll();
// ...

// vs

while ($row = $stmt->fetch()) {
    // ...
}

First, performance in terms of execution speed is quite similar for these two approaches, and the first approach might even be a little bit faster.

What you have in mind is probably memory consumption, though. For very large result sets, the second approach may actually use a lot less memory if you are executing an unbuffered query.

You can only take advantage of this and the effect does only materialize if you process the data right away, i.e. inside the loop, and don’t need access to the whole result set outside of the loop anymore. So if you build an array inside the loop manually, you can just use the first approach instead.

In modern applications, you often don’t output data immediately but pass data to a templating engine which inserts them into the (HTML) templates. In that case, you will usually have to use the first approach, anyway.

But the second approach still has several use cases where it is preferable. You’re right.

Internally, we use the first approach for the select method and the second approach for the selectRow method in the PdoDatabase class.

We should add another method that uses the second approach, just like selectRow, but instead of returning a single row it should return a wrapper around the underlying result so that you can call something like next on this result or use it in a while or foreach loop. This new method should, unlike the existing methods, execute an unbuffered query, e.g. with

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

for MySQL, so that you can really take advantage of the reduced memory consumption.

[AdamSEY]

Does your library support unbuffered query?

[ocram]

No, not at the moment. That’s because buffered queries are really what you want with the methods that this library currently offers, and unbuffered queries would only be useful with that new query method that could be used with loops. If we add that method, we’ll probably use unbuffered queries there.

[ocram]

Let’s keep this open because this is something we should definitely implement at some point in the future. Thanks again for the feedback here!