Avoid MySQL Queries Within Loops

One consistent piece of advice that's given on PHP help forums is that queries shouldn't be executed within loops. However, they don't usually provide an alternate means for accomplishing the task at hand. So I wanted to share a solution which fixed one of my scenarios.

Background

Executing queries within loops is one of my bad habits. I've been coding that way for so long that it seems natural. But the many warnings about the inefficiency of the process is finally starting to sink in. That and a script, which might be the worst offender yet, has recently crossed my desk.

The script loops through hundreds of research reports from one database table. Within that loop it checks a secondary database for supplementary information. Even though there's only a single entry in the secondary database, the code checks hundreds of times. Let's fix the issue by separating the queries.

Data

The database information below has been stripped down to keep the example simple. The primary table called "researchReports" looks like the following:

The supplementary information table is called "researchReports_relatedLinks":

Solution

First, we'll grab the the primary database information and store it within a multidimensional associative array.

<?php
//GET REPORTS
$researchReports = array();
$sql = "SELECT id, title, url FROM researchReports ORDER BY title";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
     $researchReports[$row['id']] = array(
          'title'        => $row['title'],
          'url'          => $row['url'],
          'relatedLinks' => array()
     );
}
$result->free();
?>

Note the extra field, which was set to an empty array. Also, the information is indexed using the row ID from the primary table. Both of these things will be important for the next step. Now we can process the secondary information.

<?php
//GET RELATED LINKS
$sql = "SELECT reportID, title, url FROM researchReports_relatedLinks ORDER BY title";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
     $researchReports[$row['reportID']]['relatedLinks'][] = "<li><a href='{$row['url']}'>{$row['title']}</a></li>";
}
$result->free();
?>

With everything in place, a foreach loop can be used to display the information.

<?php
//DISPLAY REPORTS
print '<ul>';
foreach($researchReports as $currReport) {
     print "<li><a href='{$currReport['url']}'>{$currReport['title']}</a>";
     if(!empty($currReport['relatedLinks'])) { print '<ul>' . implode('', $currReport['relatedLinks']) . '</ul>'; }
     print '</li>';
}
print '</ul>';
?>

Conclusion

Now of course this is one of many ways to avoid processing queries within loops. Using multidimensional associative arrays has handled most, if not all, of what I've used queries in loops for. There may be better solutions with more advanced MySQL queries, but I've got a lot to learn in that realm.