I'm working on a forum. When
it displays a forum index, it gets all the topics and depending on the
offset, displays only ten at a time. Now that's a single query. But
problem comes when I display how many replies each post has. So far, when
it does the while loop, it does a query for each item. Here's the code:
$indexresult = mysql_query("SELECT
uid,userpost,datetime,body,head,editdatetime,edituserpost,
sticky,room,locked,lastpostdate,lastpostuser FROM orinsbox.forumposts
WHERE ROOM = '".$_GET['room']."' AND ".filter($filter)."
ORDER BY ".sortby($sortby)."")
$while_offset = 0; // This is the counter used to check what post the while loop is on
$while_count = 1; // This is how many posts have been displayed (once it has reached the offset)
// Print out posts
while ($row = mysql_fetch_assoc($indexresult)) // Run through all the posts
{
$while_offset++;
if ($while_offset > $offset AND $while_count <= 10){ // if the post is in the range
$while_count++;
// Get num of replies
$repliesresult = mysql_query("SELECT
uid FROM orinsbox.forumposts
WHERE common = '".$row["uid"]."'")
or die ("Unable to run query, YOU SUCK: ".mysql_error());
$replies = mysql_num_rows($repliesresult);
// This is setting the image to be used for the post
if($row["locked"] != NULL AND $row["sticky"] != NULL ){$icon = "<img src=\"images/gold_lock.gif\" width=\"18\" height=\"24\" title=\"Locked and Sticky\">";}
elseif($row["locked"] != NULL AND $row["sticky"] == NULL ){$icon = "<img src=\"images/silver_lock.gif\" width=\"18\" height=\"24\" title=\"Locked\">";}
elseif($row["locked"] == NULL AND $row["sticky"] != NULL ){$icon = "<img src=\"images/gold_nugget.gif\" width=\"18\" height=\"24\" title=\"Sticky\">";}
else {$icon = NULL;}
// $row["descrip"]
if (!isset($bgcolour) OR $bgcolour == "FFFFFF"){$bgcolour = "EEEEEE";}else{$bgcolour = "FFFFFF";} // For alternating backround colours of the cells
if (!isset($row["common"]) OR $row["common"] == NULL){
echo "<tr bgcolor=\"#".$bgcolour."\" height=24>";
echo "<td width=\"18\">".$icon."</td>
<td><a href=forums.php?room=".urlencode($_GET['room'])."&doForums=viewtopic&object=".$row["uid"]."&offset=".$offset.">".$row["head"]."</a></td>
<td>".ucwords($row["userpost"])."</td><td>".$replies."</td>
<td>On ".$row["lastpostdate"];
if ($row["lastpostuser"] != NULL){ " by ".$row["lastpostuser"];}
echo "</td>";
echo "</tr>";}
} else {// Don't post it
}
}
The 'uid' column is the unique number of the post. All posts have this.
Now the column called 'common' is for replies. Posts that are to appear
in the forum index don't have a 'common' set.
If it is a reply, the column 'common' is set to the uid of the post it is
a reply too.
Example: Post 1:
Subject: Hey all how do I do this UID: marforu200401F COMMON: NULL
Post 2:
Subject: Hey this is how you do it! UID: jayforu200404A COMMON:
marforu200401F
Is there an easier way to do this, maybe with a sub select statement
maybe? I only know basic mysql. I hope this is understandable...
Oh yeah, you might wonder why I use the custom offset instead of using
the LIMIT and OFFSET clauses in the main MySQL query. The problem with
this is
that when I order the posts, it would only order them by the one gotten,
not them all, which is what I wanted.
--
Marcjon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
