I'm working with PHP 4.3.0 w. MySQL 4.0.14b on Windows XP

I've written my own (pretty advanced actually - or it will be once I get the code completed) forum system (why? because I wanted to learn how to handle relational databases with PHP/MySQL, and then I just decided to complete the thing) ... it uses a multitude of 1-to-many relations across several tables, to keep track of categories, forums, threads, posts, perms, users and so on ...

This particular chunk of code is part of the "show forum" code. What the first query does is pull out all the threads in the current forum ($forumID ... this valuable is set further up the code), and all the posts in each thread to find the last post in that thread.

The second query is simply to figure out how many posts are in each thread. If at all possible, I'd like to combine these two query pulls (to keep the number of DB pulls per page down - there's currently 1 more, but going to be 2-3 more queries on this page than just this), but since I'm still rather n00bish at the SQL statements, I can't figure out how...

Code in question:

// get all the threads in current forum, and for each thread find the last post
$threadquery = "SELECT * FROM hf_threads,hf_posts WHERE hf_threads.forumID='$forumID' AND hf_posts.threadID=hf_threads.threadID GROUP BY hf_threads.threadID ORDER BY hf_posts.time DESC";
$threads = mysql_query($threadquery) or die('Error in thread query<br>'.mysql_error());

while($thread = mysql_fetch_array($threads)) {
  // info from hf_threads
  $threadID = $thread['threadID'];
  $threadtitle = $thread['threadtitle'];
  $threadmaker = $thread['createnick'];
  $threadstart = $thread['createtime'];
  // info from hf_posts
  $lasttime = $thread['time'];
  $lastposter = $thread['name'];

// number of posts in thread
$threadposts = mysql_fetch_array(mysql_query("SELECT COUNT(`postID`) AS numposts FROM hf_posts WHERE `threadID`='$threadID'")) or die('Threadposts');
$numposts = $threadposts['numposts'];

// output thread data
echo("<td><a href=\"showthread.php?forumID=$forumID&threadID=$threadID\">$threadtitle</a></td>");
echo("<td><b>By:</b> $threadmaker<br><b>At:</b> $threadstart</td>");
echo("<td align=\"center\"><a href=\"editthread.php?forumID=$forumID&threadID=$threadID\">Edit</a></td>");
echo("<td align=\"center\">$numposts</td>");
echo("<td><b>At:</b> $lasttime<br><b>By:</b> $lastposter</td>\n");
echo("<td align=\"center\"><input type=\"Checkbox\" name=\"del$threadID\"></td>");

Structure of tables in question:
#TABLE hf_threads:
threadID   // ID of current thread
forumID   // ID of forum where the thread belongs
userID   // ID of thread creator (for perms reasons)
createtime   // time of thread creation
createnick   // name of thread creator
threadtitle   // title of thread
sticky  // not implemented yet
closed   // not implemented yet

#TABLE hf_posts:
postID   // ID of post
threadID   // ID of thread where the post belongs
userID   // ID of poster (for perms reasons)
name   // name of poster
time   // time of post
post   // actual post content
edit   // tracking data of editings

not that it's really relevant in this case, but these 2 tables are through the relations tied together with 8 other tables handling the forums and perms and users and statistics and such ... that's the reason why I want to keep the number of queries down, as it has to do join searches on a couple of these to handle the perms and other things on each page...


Rene Brehmer
aka Metalbunny

References, tools, and other useful stuff...

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to