Advertisement:

Author Topic: User post counts by month?  (Read 1624 times)

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
User post counts by month?
« on: December 06, 2017, 06:16:07 PM »
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.

Offline Colin

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 7,858
  • Gender: Male
  • SMF Developer
    • colinschoen on GitHub
Re: User post counts by month?
« Reply #1 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.
"If everybody is thinking alike, then somebody is not thinking." - Gen. George S. Patton Jr.

Colin

Offline vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,482
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: User post counts by month?
« Reply #2 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
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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #3 on: December 06, 2017, 08:40:07 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?


Offline vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,482
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: User post counts by month?
« Reply #4 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
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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #5 on: December 06, 2017, 09:22:34 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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #6 on: December 06, 2017, 09:24:05 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.

Offline vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,482
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: User post counts by month?
« Reply #7 on: December 06, 2017, 09:32:24 PM »
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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #8 on: March 28, 2018, 09:46:30 PM »
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.

Offline vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,482
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: User post counts by month?
« Reply #9 on: March 28, 2018, 10:27:51 PM »
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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #10 on: March 30, 2018, 10:23:53 PM »
Thanks so much!

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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #11 on: March 31, 2018, 01:47:39 PM »
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


Offline vbgamer45

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 20,482
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: User post counts by month?
« Reply #12 on: March 31, 2018, 02:22:10 PM »
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

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #13 on: March 31, 2018, 05:02:04 PM »
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.

Offline khigh

  • Jr. Member
  • **
  • Posts: 161
Re: User post counts by month?
« Reply #14 on: March 31, 2018, 05:16:45 PM »
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!