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

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Anti-Spam Guide
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
xiaolu
Registered User
Posts: 283
Joined: Sun Sep 29, 2013 7:37 pm

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

Post by xiaolu » Fri Sep 23, 2016 3:16 pm

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....

User avatar
canonknipser
Registered User
Posts: 1995
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

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

Post by canonknipser » Sat Sep 24, 2016 7:25 am

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.
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB

xiaolu
Registered User
Posts: 283
Joined: Sun Sep 29, 2013 7:37 pm

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

Post by xiaolu » Sat Sep 24, 2016 9:02 am

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...

User avatar
canonknipser
Registered User
Posts: 1995
Joined: Thu Sep 08, 2011 4:16 am
Location: Germany
Name: Frank Jakobs
Contact:

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

Post by canonknipser » Sat Sep 24, 2016 9:17 am

xiaolu wrote: I'd need do SUM (aggregate) of records
No SUM, just COUNT ;)
Greetings, Frank
phpbb.de support team member
English is not my native language - no support via PM or mail
New arrival - Extensions and scripts for phpBB

xiaolu
Registered User
Posts: 283
Joined: Sun Sep 29, 2013 7:37 pm

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

Post by xiaolu » Sat Sep 24, 2016 6:31 pm

canonknipser wrote:
xiaolu wrote: I'd need do SUM (aggregate) of records
No SUM, just COUNT ;)
You are absolutely right. It's COUNT(*). Thanks again.

xiaolu
Registered User
Posts: 283
Joined: Sun Sep 29, 2013 7:37 pm

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

Post by xiaolu » Tue Sep 27, 2016 12:00 pm

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';
Attachments
top_5.jpg

Locked

Return to “[3.0.x] Support Forum”