Hep with select top 5 posters (users) in the last 30 days

[xiaolu]

I am trying to modify the "TOP 5" mod which list 5 users with most posts. What I want is, only count the posts from the last 30 days (users who have posted within last 30 days), but the code below (user_posts from table USERS in particular) would return total post of a user, when I just want total number of posts of the user from last 30 days

Code: Select all

		// grab users with most posts
	    $sql = 'SELECT user_id, username, user_colour, user_posts
	       	FROM ' . USERS_TABLE . '
			WHERE ' . $db->sql_in_set('user_type', $ignore_users, true) . '
				AND user_posts <> 0 AND from_unixtime(user_lastpost_time) BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
	       ORDER BY user_posts DESC';
		$result = $db->sql_query_limit($sql, 5);

The above code gets data from table USERS only. I believe I need to join this table USERS with table POSTS (and sum the record in this table POSTS per its post_time within last 30 days)

Anyone could help me with the update to the above lines of code? Thanks in advance!:-)

By the way, I am familiar with SQL in general, and know how to select records by joining two or more tables, with whatever "WHERE ..." condition. Unfortunately I am not too familiar with PHP syntax....

[canonknipser]

My guess: It will increase your server load massively, because the number of users total post is part of the users table and therefore you don't need any aggregate functions.
For your request, you need a something like (quick and dirty, untested)

Code: Select all

SELECT  u.username as username,  count(p.post_id) AS postcount, u.user_id AS uid
                                FROM phpbb_users AS u
                                LEFT JOIN phpbb_posts AS p ON p.poster_id = u.user_id
                                WHERE  DATEDIFF(now(),FROM_UNIXTIME(p.post_time)) < 30
                                GROUP BY u.user_id

For the general question about "How to do sql in phpBB", maybe a look at the Coding Guidelines will give you a hint. The complete documentation is in the Api Documentation, just search for dbal.
Also, a deeper look in some php-files from the core may give you some ideas.

[xiaolu]

Thank you canonknipser for the detailed info and the code. I will give it a shot this weekend.

And yes, I understand for this post count within a period of time, I'd need do SUM (aggregate) of records in table phpbb_posts (and on my board, there are over 200,000 posts/records in this table), which would increase server load...

[canonknipser]

I'd need do SUM (aggregate) of records

No SUM, just COUNT

[xiaolu]

I'd need do SUM (aggregate) of records

No SUM, just COUNT

You are absolutely right. It's COUNT(*). Thanks again.

[xiaolu]

FYI. This is what I did, and it worked. Thanks again!

Code: Select all

// return monthly top posters 
$sql = 'SELECT u.user_id, u.username, u.user_colour, count(p.poster_id)
     FROM xiaolu_posts p, xiaolu_users u
     WHERE p.poster_id = u.user_id
     AND from_unixtime(post_time) BETWEEN (NOW() - INTERVAL 30 DAY) AND NOW()
     GROUP BY p.poster_id
     ORDER BY count(p.poster_id) DESC';