PHP Tutorial: Looping Through & Displaying A MySQLi Result Set

Comment 1 Standard

Recently I’ve seen a lot of people using while loops to retrieve a single record. You only need to loop through your result set when you expect to get more than one row of data returned. If you use a loop even when you only expect one value back from your query then you’re wasting your server’s resources because the PHP interpreter has to do more work to process a loop than it does to return a single result. This assumes you have already connected to the database and have a table to work with.

Let’s say we have a table called members with the following information:

-----------------------------------------------------
| ID  | NAME       | EMAIL              | LOGGED_ON |
-----------------------------------------------------
| 1   | Sam Jones  | sjones@gmail.com   |  False    |
-----------------------------------------------------
| 2   | Dee Walter | deeman@gmail.com   |  True     |
-----------------------------------------------------
| 3   | John Doe   | jdoe@gmail.com     |  False    |
-----------------------------------------------------
| 4   | Lisa Wells | lwells@gmail.com   |  True     |
-----------------------------------------------------

Now our ID field is an auto_increment and unique. That means no two members will have the same ID number. It’s guaranteed that looking up a member by their ID number will return 1 record or no results. 1 record if a member with that ID number is found and no records if a member with that ID doesn’t exist. Anytime you expect to get 1 or fewer records returned there’s no reason to use a loop, instead find the row directly:

<?php

//run the query
$result = mysqli_query($dbh, "SELECT * FROM members WHERE id='1'")
    or die (mysqli_error($dbh));

//fetch the results
$row = mysqli_fetch_array($result);

//display the results
echo $row['id'] . " " . $row['name'] . " " . $row['email'] . " "  . $row['logged_on'];

?>

Since we know there is only one member with the ID equal to 1 we can run our query, fetch the results and then display the data returned from the result.

Now let’s say we want to display ALL of our members. In this situation we know our members table will have zero or more records at any time, especially as new members are added in the future. In this case we expect our result will always, with the exception of when we add our first member, have one or more member in it. Here it’s appropriate to use a loop to display the data.

<?php

//run the query
$loop = mysqli_query($dbh, "SELECT * FROM members")
   or die (mysqli_error($dbh));

while ($row = mysqli_fetch_array($loop))
{
     echo $row['id'] . " " . $row['name'] . " " . $row['email'] . " "  . $row['logged_on'] . "<br/>";
}
?>
Advertisements

One thought on “PHP Tutorial: Looping Through & Displaying A MySQLi Result Set

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s