News:

Wondering if this will always be free?  See why free is better.

Main Menu

Determining users above the average monthly posts

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

Previous topic - Next topic

OtterlyAdorable

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?


OtterlyAdorable

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

OtterlyAdorable

Quote from: Illori on March 04, 2019, 10:46:50 AM
maybe https://custom.simplemachines.org/mods/index.php?mod=1929 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.

Illori

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

davidhs

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

Kindred

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

Слaва
Украинi

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


Advertisement: