Looping directly over a SELECT query

A really nice feature of PDO, is that you can loop directly over a select query, without needing to store the result first. I've got open pdo_loop.php, which you can find in the Chapter 02 > 02_03 folder, of the exercise files. The code on line three, includes the database connection we created in the previous movie, and the HTML contains a table, that'll be used to display the results of a select query.

So, let's create that query, we'll create it on the line after including the database connection. We'll save it as SQL. And, it will be select, name, meaning, gender, from names, and then we'll have a little order clause to it, so order by name. And instead of submitting this query to the database and storing the result, we can get direct access to each row, in a for each loop.

So let's create that for each loop. We need to go down into the HTML part of the page, create a new line between the two rows on line 29. Needs to be a php block. And in there we'll have a for reach loop. We use the data base connection. And the query method. And we pass that, the SQL query. Then we can alias that to a variable. It doesn't matter what you call the variable, but row is an obvious choice, because we're going to go through each row, one at a time.

Then we need an opening curly brace. We'll close that php block, and put the closing curly brace after the closing table row tag. So, we've now got the for each loop. And each time this'll run, it'll generate an associative array containing the value of each column, in the current row. So we can display them in the table cells, using the column names, as the array keys. So let's just remove this non-breaking space.

We'll have php block, echo and then row. First one will be name. And then if we copy that, we can replace the non-breaking space here. That would be meaning, and the last one is gender. So if we now save that, and load the page into a browser. There is the result. It's all displayed. So that's a quick and easy way to display the results of the select query, without having to save the result first.

So let's just go back to the editing program. Here, it is, it's a for each loop. Inside the for each loop, you use the database connection, the query method, and pass it the SQL directly as an argument, and then alias the result as row or any other variable that you want to call it. As the loop runs, you get the current row. And that's an associate array which you can then use to display. But it's important to note, that this works only with a for each loop, it won't work with any other type of loop.

In all other situations, the query method, returns an object containing the complete result set, as we'll see next.