05 Nov 09 PHP Developer – Looping through database results

As a PHP Programmer, a very routine PHP/MySQL procedure is fetching a set of records from the result of a query.

$sql = "SELECT ...";
$result_set = mysql_query($sql);
for ($ctr = 0; $ctr < mysql_numrows($result_set); $ctr++)
{
    $my_object = mysql_fetch_object($result_set);
    //do something with $my_object
}

Now as tidy as the above code is, what’s the big problem? The number of rows returned by the query remains the same throughout. Why are we calling the mysql_numrows function on the same result set, to return the same answer over and over, possibly thousands of millions of times depending on the size of the result set? On a larger web application with a larger result set, things like this will dramatically increase unnecessary overhead. This is one of the most basic optimizations to make:

$sql = "SELECT ...";
$result_set = mysql_query($sql);
$result_num = mysql_numrows($result_set);
for ($ctr = 0; $ctr < $result_num; $ctr++)
{
    $my_object = mysql_fetch_object($result_set);
    //do something with $my_object
}

Now, there’s a couple of different methods you can use to achieve the same purpose, some of which may actually be more appropriate, such as a simple while loop, but the purpose of this article was to illustrate the issue above solely. More on optimization later..

Tags: for, loop, MySQL, PHP, php programmer, sql

Leave a Comment

You must be logged in to post a comment.