LAST ACTIVE = 2014 && >50 posts

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Anti-Spam Guide
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

LAST ACTIVE = 2014 && >50 posts

Post by noth » Mon Dec 29, 2014 8:34 pm

I want to do a TOP 100 chart of my users

the users I have date back 10 years as that is how old the forums are

but I only want the TOP 100 to include the members who were active in 2014

the memberlist can help me a bit, If I click on "Last Active" I can see pages and pages of members who visited in 2014 but it is not in posts order, if I click on Posts, the Last Visited is lost and it's just the number of posts

is there a SQL query I can run in phpMyAdmin that will give me the usernames in order of the highest posting TOP 100 members who were on the forums in 2014? :D

Birkley
Registered User
Posts: 16
Joined: Mon Dec 29, 2014 8:31 pm

Re: LAST ACTIVE = 2014 && >50 posts

Post by Birkley » Mon Dec 29, 2014 9:42 pm

I can jumble together a sample query, but you (or someone) may need to track down actual field names. I'll confirm field names and query correctness later this evening if someone hasn't already.

select <phpbb_users>, count(<posts>) from <database name>
where <last login> >= to_date('01/01/2014','MM/DD/YYYY')
and <last login> <= to_date('12/31/2014','MM/DD/YYYY')
having count(<posts>) > 50
ORDER BY count(<posts>) desc;

User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by noth » Mon Dec 29, 2014 9:49 pm

sounds good to me :D thank you Birkley for your help

This time of year - obviously we are coming up to 31 DEC

so it is an ideal time to put together a "TOP MEMBERS OF 2014" chart for your site

would/ does anybody else do this on their forums? I have done it before but included all members regardless of "Last visit" and now this seems more relevant than ever

Birkley
Registered User
Posts: 16
Joined: Mon Dec 29, 2014 8:31 pm

Re: LAST ACTIVE = 2014 && >50 posts

Post by Birkley » Mon Dec 29, 2014 10:40 pm

Give this a whirl. You'll need to adjust your fieldname prefix (mine was phpbb3_)with whatever you use and change the DB with your schema name.

//Selects Top 100 Posters Ever, who have visited in 2014

Code: Select all

select us.username, count(p.post_id)
from DB.phpbb3_users us, DB.phpbb3_posts p 
where us.user_lastvisit > '1388534400'
and us.user_id = p.poster_id
group by us.username
order by count(p.post_id) desc
limit 100;
Hope that's helpful!

User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by noth » Tue Dec 30, 2014 6:20 pm

Birkley I love that, it produces two columns, one the username and one the posts :P IT WORKS!! :ugeek:

to make it absolutely perfect, can you please tell me the enhanced version that would enable 5 columns 8-)

one the username and one the posts, next one, date joined, location and finally member no, making five columns in all, then I just copy and paste it to a notepad .... > HTML page :D
Attachments
bentley.JPG
two column, very good but improve?

Birkley
Registered User
Posts: 16
Joined: Mon Dec 29, 2014 8:31 pm

Re: LAST ACTIVE = 2014 && >50 posts

Post by Birkley » Tue Dec 30, 2014 8:37 pm

Just to make sure I understand what you're wanting, can you list out all the fields?

In example
1.) Username
2.) Post count
3.) etc...

User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by noth » Tue Dec 30, 2014 10:16 pm

1.) Username
2.) Post count
3.) date joined
4.) location
5.) member number :mrgreen:

User avatar
Komanche
Registered User
Posts: 232
Joined: Thu Aug 02, 2007 10:49 am
Location: Czech Republic

Re: LAST ACTIVE = 2014 && >50 posts

Post by Komanche » Wed Jan 21, 2015 4:14 pm

thanks for the query ;)

User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by noth » Sat Dec 31, 2016 7:25 pm

select us.username, count(p.post_id)
from DB.phpbb3_users us, DB.phpbb3_posts p
where us.user_lastvisit > '1388534400'
and us.user_id = p.poster_id
group by us.username
order by count(p.post_id) desc
limit 100;

I just tried this query again - I thought it would give me members visiting only in the last 12 months but somehow it is including all members who visited back to 2014 as well

it seems that where us.user_lastvisit > '1388534400' means last visited 2014 up to now

can I change that line to reflect only visited during 2016? :D

User avatar
bonelifer
Community Team Member
Community Team Member
Posts: 3380
Joined: Wed Oct 27, 2004 11:35 pm
Name: William
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by bonelifer » Sat Dec 31, 2016 7:32 pm

Change 1388534400 to:
1451606400


1451606400
Is equivalent to:
01/01/2016 @ 12:00am (UTC)

BTW: I used http://www.unixtimestamp.com/index.php to get the unix timestamp.
Knowledge Base | phpBB Board Rules | Search Customisation Database
Image
Please don't contact me via PM or email for phpBB support .

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 49332
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by stevemaury » Sat Dec 31, 2016 7:35 pm

Use 1451606399. BTW, neither last login nor last visit say anything about activity, if that is defined as posting.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. PM or email me.

All unsolicited PMs will be ignored.

User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by noth » Sat Dec 31, 2016 7:52 pm

brilliant, that query only returns members who visited in 2016 :P

exactly what I need GREAT 8-) thanks you Bonelifer, Steve (and Birkley of course) :mrgreen:

User avatar
noth
Registered User
Posts: 2451
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by noth » Tue Jan 02, 2018 5:39 pm

what would be the number for members who visited in the last 12 months please?

I want to do a TOP 100 chart of my users

the users I have date back 10 years as that is how old the forums are

but I only want the TOP 100 to include the members who Visited in the last 12 months

User avatar
RMcGirr83
Recognised Extension Developer
Posts: 20853
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr
Contact:

Re: LAST ACTIVE = 2014 && >50 posts

Post by RMcGirr83 » Tue Jan 02, 2018 5:44 pm

noth wrote:
Tue Jan 02, 2018 5:39 pm
what would be the number for members who visited in the last 12 months please?
https://www.unixtimestamp.com/index.php
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored

Locked

Return to “[3.1.x] Support Forum”

Who is online

Users browsing this forum: No registered users and 10 guests