PHP Tutorial: Looping Through & Displaying A MySQLi Result Set
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/>";
}
?>
