How can I remove this query from within a loop?

Question

I am currently designing a forum as a personal project. One of the recurring issues I've come across is database queries in loops. I've managed to avoid doing that so far by using table joins or caching of data in arrays for later use.

Right now though I've come across a situation where I'm not sure how I can write the code in such a way that I can use either of those methods easily. However I'd still prefer to do at most 2 queries for this operation rather than 1 + 1 per group of forums, which so far has resulted in 5 per page. So while 5 isn't a huge number (though it will increase for each forum group I add) it's the principle that's important to me here, I do NOT want to write queries in loops

What I'm doing is displaying forum index groupings (eg admin forums, user forums etc) and then each forum within that group on a single page index, it's the combination of both in one page that's causing me issue. If it had just been a single group per page, I'd use a table join and problem solved. But if I use a table join here, although I can potentially get all the data I need it'll be in one mass of results and it needs displaying properly.

Here's the code (I've removed some of the html for clarity)

<?php
    $sql= "select * from forum_groups"; //query 1
    $result1 = $database->query($sql);
    while($group = mysql_fetch_assoc($result1)) //first loop
      {?>
        <table class="threads"> 
        <tr>
              <td class="forumgroupheader"> <?php echo $group['group_name']; ?> </td>
            </tr>
            <tr> 
          <td class="forumgroupheader2"> <?php echo $group['group_desc']; ?> </td>
            </tr>
       </table>
       <table>
        <tr>
        <th class="thforum"> Forum Name</th>
        <th class="thforum"> Forum Decsription</th>
        <th class="thforum"> Last Post </th>
        <tr>
            <?php 

                $group_id = $group['id'];
                $sql = "SELECT forums.id, forums.forum_group_id, forums.forum_name, forums.forum_desc, forums.visible_rank, forums.locked, forums.lock_rank, forums.topics, forums.posts, forums.last_post, forums.last_post_id, users.username
FROM forums 
LEFT JOIN users on forums.last_post_id=users.id 
WHERE forum_group_id='{$group_id}'";
                //query 2
                $result2 = $database->query($sql);
                while($forum = mysql_fetch_assoc($result2))
                                            //second loop        
                    {?>

So how can I either
a) write the SQL in such a way as to remove the second query from inside the loop or
b) combine the results in an array
either way I need to be able to access the data as an when so I can format it properly for the page output, ie within the loops still.

Answer 1

I don't think your approach is so bad and I wouldn't change it. However, as an exercise you could do it all in one query if you select the forums (join with group) and order by group id. That way you've only made one select and you can begin looping. In your loop you would check if group_id has changed. If it has, then begin a new group header.

If this is an ultra high traffic site, you can also look into caching so pages are written to disk after they've been built.

Here's an example of how you could detect group change:

$currentGroupId = 0;
$firstTime = true;
while ($row = mysql_query_fetch_assoc($res)) {
    if ($row['group_id'] != $currentGroupId) {
        $currentGroupId = $row['group_id'];
        if (!$firstTime) {
            // echo close group html
        } else {
            $firstTime = false;
        }
        // echo open group html
    }

    // do forum stuff
}
// echo close group html

Answer 2

You can do this with two queries. The first query does not need changing (although personally I'd prefer not to use SELECT *). The second query should be changed to join with forum_groups and run outside the loop, before you start iterating:

SELECT forum_groups.id, ...other columns here...
FROM forum_groups 
JOIN forums ON forum_groups.id = forum_group_id
LEFT JOIN users ON forums.last_post_id = users.id

You can also combine those two queries into one huge query that fetches all data in one go, but I'd probably not do this as it will require returning redundant data.