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?

PHP Code:

  $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?

PHP Code:

$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?

PHP Code:

 $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:

PHP Code:

  $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:

PHP Code:

  AND thread.firstpostid = post.postid 


to this:

PHP Code:

   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?