How do I get the total amount of replies?

[Boofo]

How can I chnage the following to get only the ammount of replies and not replies and threads?

$topposter = $db->query_first("
SELECT user.userid, user.username, COUNT(post.postid) AS postcount
FROM " . TABLE_PREFIX . "post AS post
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
WHERE thread.forumid=$forumid
GROUP BY post.userid
ORDER BY postcount DESC
LIMIT 1
");

[Andreas]

Isn't that obvious? ;)


$topposter = $db->query_first("
SELECT user.userid, user.username, COUNT(post.postid) AS postcount
FROM " . TABLE_PREFIX . "post AS post
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
WHERE thread.forumid=$forumid AND thread.firstpostid != post.postid
GROUP BY post.userid
ORDER BY postcount DESC
LIMIT 1
");

[Boofo]

It wasn't until you pointed it out to me.

Thank you, sir. ;)

[Boofo]

Ok, new problem. ;)

The 2 following queries seem to be interfering with each other. What happens is the first query is only for the top replies to a thread. The second query is for all posts in a thread (Threads + Replies). Sometimes, not always, the Top Poster (second query) will pick up the Top Relpies (first query) username. The other counts seem to stay correct. First, is there a way to combine the 2 and if not, how do I keep the username mix-up from happening?

$topreplies = $db->query_first("
SELECT user.userid, user.username, COUNT(post.postid) AS replycount
FROM " . TABLE_PREFIX . "post AS post
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
WHERE thread.forumid=$forumid AND thread.firstpostid != post.postid
GROUP BY post.userid
ORDER BY replycount DESC
LIMIT 1
");

$topposter = $db->query_first("
SELECT user.userid, user.username, COUNT(post.postid) AS postcount
FROM " . TABLE_PREFIX . "post AS post
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
WHERE thread.forumid=$forumid
GROUP BY post.userid
ORDER BY postcount DESC
LIMIT 1
");

[Boofo]

Ok, I think I fixed it. I changed the top poster query to this:

$topposter = $db->query_first("
SELECT user.userid, user.username, COUNT(post.postid) AS postcount
FROM " . TABLE_PREFIX . "post AS post
LEFT JOIN " . TABLE_PREFIX . "thread AS thread ON (post.threadid = thread.threadid)
LEFT JOIN " . TABLE_PREFIX . "user AS user ON (post.userid = user.userid)
WHERE thread.forumid=$forumid AND thread.firstpostid = post.postid
GROUP BY post.userid
ORDER BY postcount DESC
LIMIT 1
");

I added this:

AND thread.firstpostid = post.postid

to this:

WHERE thread.forumid=$forumid

and it seemed to fix it. Still, is there a way to combine these 2 queries and get all the same info out of it?