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>");
echo("</tr><tr>\n");
}
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...
TIA
Rene
SELECT threadID, COUNT(*) AS numPosts 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
You can also add more columns to the select.
-- paperCrane <Justin Patrin>
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
