Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Determining users above the average monthly posts

Started by OtterlyAdorable, March 04, 2019, 10:42:10 AM

Previous topic - Next topic


Currently running Forum version: SMF 2.0.15.

My forum (the Irate Pirate) wants to run a monthly event for users who are above the average monthly post count.

We found one mod that might help us get partway there called Enhanced Forum Statistics, but upon trying to install this mod, it got a bunch of flags for conflicting with another mod.  We have a lot of mods, and it would take me a while to figure out which mod it conflicts with.  Depending on the mod, it may be entirely unreasonable to uninstall that mod for this one.

So, our next thought was just to get a spreadsheet of info on the posts made in the past month.  All we really need is, for each post, the date and the member who posted it.  We can extrapolate the average posts per month overall and by user with that information.

The problem is, I can't find that information.  I've looked at our database on the back end, and so far as I can tell, that information isn't available, and I'm not sure where I could find it.

Anyone have any other ideas for what I could do?


This might help!  Trying this now, I'll let you know either way.  Thank you!


Quote from: Illori on March 04, 2019, 10:46:50 AM
maybe can help you?

So this... almost helps us.  I figured out where to get the total monthly posts from our basic forum statistics, divided by the days of the month gives us the average posts per day.  Sweet.

The mod you linked does provide the information we need for each user, but.... as far as I can tell, there is nowhere to look at that information for each user in a list.  I would have to go into each individual member's profile to look at it, and that's... just not going to be viable for us.

Thank you so much for the recommendation, but I think I'll have to continue looking.


maybe if you post in that mods support topic the author can help you get the data where you need it.


You can add a page to your forum (or use a portal, they can add page with PHP code) and then add PHP code with queries and show users and their posts number.

Some queries can be:

Number of posts for each member:
SELECT COUNT(*) AS post, id_member
FROM {$db_prefix}messages
WHERE YEAR(poster_time) = $year AND MONTH(poster_time) = $month
GROUP_BY id_member

$year & $month = year & month to count.

Number of posts (greater than or equal to average) for each member:
SELECT COUNT(*) AS post, id_member
FROM {$db_prefix}messages
WHERE YEAR(poster_time) = $year AND MONTH(poster_time) = $month
GROUP_BY id_member
HAVING post >= $average_post

And $average_post can be $total_post/$total_member:

$total_post =
SELECT COUNT(*) AS total_post
FROM {$db_prefix}messages
WHERE YEAR(poster_time) = $year AND MONTH(poster_time) = $month

$total_member =
SELECT COUNT(*) AS total_member
FROM {$db_prefix}members

Also you can see or not approved posts, approved members,...


except for the fact that you should NEVER do direct databse queries in yoru SMF pages.
ALWAYS use the SMF databse functions to pre-parse and clean the data


Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."