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.


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.


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

id title url
1 PHP Secrets Unleashed: Part 1 http://www.yourwebsite.com/php_secrets_1.php
2 PHP Secrets Unleashed: Part 2 http://www.yourwebsite.com/php_secrets_2.php
3 PHP: HTML's Best Friend http://www.yourwebsite.com/php_friend.php

The supplementary information table is called "researchReports_relatedLinks":

id reportID title url
1 2 Previously Unreleased Bonus Secret! http://www.yourwebsite.com/bonus_secret.php
2 2 Sorry, One More Secret http://www.yourwebsite.com/one_more_secret.php


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

$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()

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.

$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>";

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

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>';


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.


  • #2 Bri on 06.13.17 at 9:25 pm

    Wow. Why is this how-to at the bottom of the Internet? As a php noob, this guide is a must have. The only way to learn is by example, and examples need simplified. This guide is just that. Thank you. Excellent!

  • #1 Rajeev Thomas on 08.30.14 at 3:14 am

    Thank you for this post. As a beginner posts like this that explains complex code really helps to have a better perspective on coding practices/at right way of doing things. I took a look at your site and there are so many useful, interesting posts here! Thanks again!

Leave a Comment