How To Add Top Users, Hot Topics And Forum List

Reply · Quote

Alan

2008-02-22, 01:55 #1

Member since Oct 2007 · 4 posts
Group memberships: Members

Show profile ·

Link to this post

Subject: How To Add Top Users, Hot Topics And Forum List

I'm Alan and I run www.eightlane.com. I thought I'd put something back in and help people with some code from my site that allows you to run some descriptives on your forum, useful for homepage stats etc:

TOP USERS:

$sql= mysql_query("SELECT _forumUsers.ID , _forumUsers.Name , _forumPosts.User , count(*) FROM _forumPosts LEFT JOIN _forumUsers ON _forumPosts.User =_forumUsers.ID GROUP BY User HAVING _forumPosts.User >0 ORDER BY 'count(*)' DESC LIMIT 0 , 4;");

while($row=mysql_fetch_assoc($sql)) {
$query = mysql_query("SELECT User FROM _forumPosts WHERE User = '$row[User]'") or die(mysql_error());
$count = mysql_num_rows($query);
$new_id = "<b>$count</b> posts - <a href=\"forum.php%3Freq%3Dcp%26id%3D$row[ID]\">$row[Name]</a><br/>";
echo "$new_id";
}
$sql = mysql_query("SELECT * FROM _forumPosts WHERE User = 0") or die(mysql_error());
$count = mysql_num_rows($sql);
echo "Guests have posted <b>$count</b> posts";

HOT TOPICS

$time=time();
$sql = mysql_query("SELECT * FROM _forumThreads WHERE LastPostDate > ($time-250000) ORDER BY Views DESC LIMIT 4;") or die(mysql_error());

echo "<table width=\"100%\" cellspacing=\"0\" cellpadding=\"0\"><tr>";
echo "<td width=\"75%\" align=\"center\"><b>Hot Topics</b></td><td width=\"25%\" align=\"center\"><b>Viewed</b></td></tr>";

while($row = mysql_fetch_array( $sql )) {
echo "<tr><td width=\"75%\">";
echo "<a href=\"forum.php%3Freq%3Dthread%26id%3D$row[ID]\">" . stripslashes(substr($row['Subject'],0,75)) . "</a></td><td width=\"25%\">$row[Views] times</td></tr>";
}
echo "</table>";

LATEST FORUM TOPICS

$sql= mysql_query("SELECT * FROM _forumThreads ORDER BY LastPostDate DESC LIMIT 16;");
while($row=mysql_fetch_assoc($sql)) {
echo "<a href=\"forum.php%3Freq%3Dthread%26id%3D$row[ID]\">" . ucwords($row['Subject']) . "</a>";
}

Reply · Quote

NFG 2010-02-28, 10:09 #2

Member since Sep 2006 · 105 posts
Group memberships: Members

Show profile ·

Link to this post

Alas, the 'top posters' doesn't actually work. Or at least, it doesn't work for me. Instead of the top four members by postcount, I get the first four members with non-zero posts. On my forum user #2 is a test account with zero posts, so it lists members 1, 3, 4 and 5. User #32 has far more posts, but is not listed.

Have you revised this in the two years since you posted it? I tried to make it work, but your SQL-fu is greater than mine. =/

Reply · Quote

NFG 2010-03-01, 02:34 #3

Member since Sep 2006 · 105 posts
Group memberships: Members

Show profile ·

Link to this post

OK, I had someone work over the query to get the appropriate results, and here's some working code:

TOP USERS BY POST-COUNT:

SELECT U.ID , U.Name , P.User , COUNT(*) AS post_count FROM unb_Posts P, unb_Users U where P.User=U.ID GROUP BY P.User ORDER BY post_count DESC limit 4;

You can change the LIMIT at the end to any number of users.