How can I remove this query from within a loop?
- by Chris
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.
