User post counts by month?

Started by khigh, December 06, 2017, 06:16:07 PM

Previous topic - Next topic

khigh

Hi all.

I'm trying to do a raffle, where each post is an entry. Probably some other stuff, too - like topic starts count double - referrals worth 10 - that kind of thing.

Doesn't look like there's any mods for that, and that's fine. I can work the specifics of the drawing. I'm just trying to see post counts per member based on a timeframe somewhere in stats. I got a mod called something like "top ten posters", but that's all I get - just the top ten. Technically a single poster could win, so I need it all the way down to individual posters.

Any way to track that? Quite possibly already built in that I'm overlooking?

Thanks for looking.

Colin

Hmm, interesting. That seems like a unique idea. There isn't an easy way to get that information now. Seems like a good candidate for the Mod Requests board.
"If everybody is thinking alike, then somebody is not thinking." - Gen. George S. Patton Jr.

Colin

vbgamer45

You can do with an SQL query based on the smf_messages table just do a time range and group by id_member
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

khigh

Quote from: vbgamer45 on December 06, 2017, 07:48:25 PM
You can do with an SQL query based on the smf_messages table just do a time range and group by id_member

Is that per member? or I can get a report for all members at once?


vbgamer45

That would get all memember at once .

Sudo code select id_member, count(*) from smf_messages where daterangehere group by id_member
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

khigh

Quote from: Colin on December 06, 2017, 07:06:50 PM
Hmm, interesting. That seems like a unique idea. There isn't an easy way to get that information now. Seems like a good candidate for the Mod Requests board.

OK - thanks. I don't mind doing it in the MySQL if I can figure out how, but if I can't sort that I'll likely request a mod

khigh

Quote from: vbgamer45 on December 06, 2017, 08:42:49 PM
That would get all memember at once .

Sudo code select id_member, count(*) from smf_messages where daterangehere group by id_member

Is there a way to get it without shell access? like Phpmyadmin? I'm using a hosting service.

vbgamer45

yes you can run sql queries in phpmyadmin
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

khigh

Thanks for the help so far.

What format should "daterangehere" take? Ultimately the dates I need are 2018-01-01 to 2018-03-31. I can modify the end date to practice with if it can't be in the future.

Once I fix that, I paste "Sudo code select id_member, count(*) from smf_messages where daterangehere group by id_member" into the box labeled "SQL query on database".

I should stress that I'm absolutely an ignorant noob - just want to get that out of the way.

thanks for looking.

vbgamer45

Example

select id_member, count(*) from smf_messages where poster_time > UNIX_TIMESTAMP(STR_TO_DATE('Mar 10 2018 12:00AM', '%M %d %Y %h:%i%p')) and poster_time < UNIX_TIMESTAMP(STR_TO_DATE('Mar 28 2018 12:00AM', '%M %d %Y %h:%i%p'))
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

khigh

Thanks so much!

i got calked away,so I'm replying from my phone. Cant wait to try it!

khigh

OK - I actually figured out a LITTLE something on my own.

I got an error message that smf_messages couldn't be found. Since my databasre appeared to start with wke, I changed that line to wke_messages and got this. Does this mean it didn't work, or it created something and I don't know how to access it?

Thanks for your continued patience


vbgamer45

It worked i Would add a group by id_member on the end of the statement

But it says id_member 236 had 300 posts during that itme.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

khigh

Oh that is so awesome! Thanks so much!

My users are enjoying the contest, which ends tonight.

I can technically do without, since I have almost a month to compile it at this point, but if I could bother you one more time, I'd be ever so appreciative.

Can I get the user name in the report? I have the member number and post count. If you could spare a moment to help me with that, it would save me a half day's work. But regardless thank you so much again.

khigh

I did it! I just added poster_name to the front part of the query. No big deal tto most, but I'm proud of myself for figuring that out.

Thanks again. I'll mark it solved!

If you ever hear of anyone wanting to do a posting contest - that's certainly the way to go.

Thanks again!

Advertisement: