Rene,
Here are my thoughts:
In general don't ever use "select * ..." yes it works fine but it's cleaner
to always spell out the columns you expect. Also you are often returning
data you don't need (overhead for the server). If you ever alter or add a
column you could mess up your code but not get an error message (especially
if you use $result[1] -- but you'd never do that either).
OK, so now your query:
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
Does this work??? (it shouldn't) You must not have different data in the
hf_posts table yet. I'm assuming there MANY hf_posts for ONE hf_threads;
So when you join on threadID the columns from hf_posts are all different and
this will cause unpredictable results (for more info see:
http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html
Particularly the quote: "Don't use this feature if the columns you omit
from the GROUP BY part aren't unique in the group! You will get
unpredictable results.")
Although MySQL allows this - don't do it. Spell out your SELECT list and
spell out your GROUP BY list (trust me it will make debugging easier).
In your case I'd rewrite the query as:
SELECT hf_threads.threadID, threadtitle, createnick,
createtime, max(time) as time, 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, threadID, threadtitle, createnick,
createtime,
ORDER BY time DESC
OK this looses you the hf_posts.name field (but I maintain you never had
that working correctly) but it gives you the "numPosts" column.
If you need the last posted by person's name I'd approach the problem by
adding that field to the hf_threads table - every time a new post come in
I'd update that table and change the name of the last post person (and if
you're doing that you might as well add the time and count - now your query
doesn't need a GROUP BY at all).
Good Luck,
Frank
On 3/22/04 9:09 AM, "php-db-digest-help@xxxxxxxxxxxxx"
<php-db-digest-help@xxxxxxxxxxxxx> wrote:
> From: -{ Rene Brehmer }- <metalbunny@xxxxxxxxxxxxxx>
> Date: Mon, 22 Mar 2004 18:12:35 +0100
> To: php-db@xxxxxxxxxxxxx
> Subject: Looking for simpler way to do this...
>
> 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
> --
> Rene Brehmer
> aka Metalbunny
>
> http://metalbunny.net/
> References, tools, and other useful stuff...
>
--
Frank Flynn
Poet, Artist & Mystic
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
