Minimizing MySQL Queries with the implode() Function

After seeing the many warnings that MySQL queries shouldn't be executed within loops, I finally broke down and figured out an alternate solution for the majority of queries where I use loops. Most times, a loop feels necessary when one database table contains the core information and another has multiple entries of supporting information. Instead of going for the typically loop, let's look at using the implode() function.

Since some books have multiple authors, the database was split into two tables.

To grab the book titles based on whatever is entered in $firstName and $lastName, we would first get the matching author entries. While looping through the authors, another query could be executed to get the titles.

<?php
//INITIALIZE VARIABLES
$bookTitles = array();
 
//GET AUTHOR INFORMATION
$sql = "SELECT bookID FROM book_authors WHERE firstName='$firstName' AND lastName='$lastName'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
     //GET BOOK TITLES
     $sql = "SELECT title FROM book_list WHERE id={$row['bookID']}";
     $result2 = mysql_query($sql);
     while($row2 = mysql_fetch_array($result2)) {
          $bookTitles[] = $row2['title'];
     }
}
 
//DISPLAY BOOK TITLES
if(count($bookTitles)) {
     print '<ul><li>' . implode('</li><li>', $bookTitles) . '</li></ul>';
} else {
     print '<p>No books found for the chosen name.</p>';
}
?>

Instead of grabbing one book title at a time, the process could be simplified with the implode() function. We just need to store the book IDs to build the WHERE clause afterwards.

<?php
//INITIALIZE VARIABLES
$bookIDs    = array();
$bookTitles = array();
 
//GET AUTHOR INFORMATION
$sql = "SELECT bookID FROM book_authors WHERE firstName='$firstName' AND lastName='$lastName'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
     $bookIDs[] = $row['bookID'];
}
 
//GET BOOK TITLES
if(count($bookIDs)) {
     $sql = "SELECT title FROM book_list WHERE id=" . implode(' OR id=', $bookIDs);
     $result = mysql_query($sql);
     while($row = mysql_fetch_array($result)) {
          $bookTitles[] = $row['title'];
     }
}
 
//DISPLAY BOOK TITLES
if(count($bookTitles)) {
     print '<ul><li>' . implode('</li><li>', $bookTitles) . '</li></ul>';
} else {
     print '<p>No books found for the chosen name.</p>';
}
?>

Conclusion

Collecting information from a database with loops places an unnecessary burden on the server. It may take dozens (or more) queries to get everything required. The same process can be completed using the implode() function and a couple queries.