Help on a plugin that shows top posters

Twiebie	#1 2012-07-31 17:53
Team Thanked: 74 times	I want to create a little plugin that shows the weekly top posters in a list. I've grabbed a bit of code from the forum stats plugin to get started, but now i'm wondering how I can get/specify the users post count for a specific timespan (a week for example). $res = $db->query("SELECT user_id, user_name, user_postcount FROM $db_users WHERE 1 ORDER by user_postcount DESC LIMIT 10"); foreach ($res->fetchAll() as $row) { $t1->assign(array( 'TOPPOSTERS_NAME' => cot_build_user($row['user_id'], htmlspecialchars($row['user_name'])), 'TOPPOSTERS_POSTS' => $row["user_postcount"], )); } Thanks.

Twiebie

#1 2012-07-31 17:53

I want to create a little plugin that shows the weekly top posters in a list.

I've grabbed a bit of code from the forum stats plugin to get started, but now i'm wondering how I can get/specify the users post count for a specific timespan (a week for example).

$res = $db->query("SELECT user_id, user_name, user_postcount FROM $db_users WHERE 1 ORDER by user_postcount DESC LIMIT 10");

foreach ($res->fetchAll() as $row) {
	$t1->assign(array(
		'TOPPOSTERS_NAME' => cot_build_user($row['user_id'], htmlspecialchars($row['user_name'])),
		'TOPPOSTERS_POSTS' => $row["user_postcount"],
	));
}

Thanks.

Trustmaster	#2 2012-07-31 18:11
Administrators Thanked: 265 times	The users table does not keep history information, so you cannot extract statistics for a given time span. Instead, the information should be extracted from the forum posts table. The query for top 10 posters for the last 7 days looks like this: SELECT u.user_id, u.user_name, u.user_avatar, COUNT(p.fp_id) AS count FROM `$db_forum_posts` AS p LEFT JOIN `$db_users` AS u ON p.fp_posterid = u.user_id WHERE fp_creation > UNIX_TIMESTAMP() - 7243600 GROUP BY fp_posterid ORDER BY count DESC LIMIT 10 May the Source be with you!

Trustmaster

#2 2012-07-31 18:11

Administrators
Thanked: 265 times

The users table does not keep history information, so you cannot extract statistics for a given time span. Instead, the information should be extracted from the forum posts table. The query for top 10 posters for the last 7 days looks like this:

SELECT u.user_id, u.user_name, u.user_avatar, COUNT(p.fp_id) AS count
    FROM `$db_forum_posts` AS p
        LEFT JOIN `$db_users` AS u ON p.fp_posterid = u.user_id
    WHERE fp_creation > UNIX_TIMESTAMP() - 7*24*3600
    GROUP BY fp_posterid
    ORDER BY count DESC
    LIMIT 10

May the Source be with you!

Twiebie	#3 2012-07-31 18:37
Team Thanked: 74 times	Thanks for the quick reply, Trustmaster! Got it working now. :)