XF 1.5 SQL query to generate the Top Poster Member?

rdn

Well-known member
I want to manually generate the Top Poster Member within the last 30 days.
SQL guru please :)

Thanks!
 
Rich (BB code):
SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts'
FROM xf_post AS p
INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
INNER JOIN xf_user AS u ON (u.user_id = p.user_id)
WHERE 1=1
AND p.message_state = 'visible'
AND t.discussion_state = 'visible'
AND p.post_date > UNIX_TIMESTAMP('2016-10-31')
GROUP BY p.user_id
ORDER BY NumPosts DESC
 
For the month of December :)
Code:
SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts'
FROM xf_post AS p
INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
INNER JOIN xf_user AS u ON (u.user_id = p.user_id)
WHERE 1=1
AND p.message_state = 'visible'
AND t.discussion_state = 'visible'
AND p.post_date > UNIX_TIMESTAMP('2016-12-01')
GROUP BY p.user_id
ORDER BY NumPosts DESC
 
from ssh command can do

Code:
read -ep "enter database name: " dbname; read -ep "enter month in format i.e. december = 2016-12-01: " $month; echo "";mysql -e "SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts' FROM xf_post AS p INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id) INNER JOIN xf_user AS u ON (u.user_id = p.user_id) WHERE 1=1 AND p.message_state = 'visible' AND t.discussion_state = 'visible' AND p.post_date > UNIX_TIMESTAMP('"${month}"') GROUP BY p.user_id ORDER BY NumPosts DESC" $dbname
will prompt you for variables to fill in
Code:
enter database name: yourxf_dbname
enter month in format i.e. december = 2016-12-01: 2016-12-01
 
  • Like
Reactions: rdn
from ssh command can do

Code:
read -ep "enter database name: " dbname; read -ep "enter month in format i.e. december = 2016-12-01: " $month; echo "";mysql -e "SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts' FROM xf_post AS p INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id) INNER JOIN xf_user AS u ON (u.user_id = p.user_id) WHERE 1=1 AND p.message_state = 'visible' AND t.discussion_state = 'visible' AND p.post_date > UNIX_TIMESTAMP('"${month}"') GROUP BY p.user_id ORDER BY NumPosts DESC" $dbname
will prompt you for variables to fill in
Code:
enter database name: yourxf_dbname
enter month in format i.e. december = 2016-12-01: 2016-12-01
Thanks Eva:).
Very useful for me every month :)
 
more automated if you use this to grab previous months date format instead of entering each time
Code:
date +"%Y-%m-%d" -d 'last month'
2016-12-01

Code:
read -ep "enter database name: " dbname; month=$(date +"%Y-%m-%d" -d 'last month'); echo "";mysql -e "SELECT u.user_id, u.username, COUNT(*) AS 'NumPosts' FROM xf_post AS p INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id) INNER JOIN xf_user AS u ON (u.user_id = p.user_id) WHERE 1=1 AND p.message_state = 'visible' AND t.discussion_state = 'visible' AND p.post_date > UNIX_TIMESTAMP('"${month}"') GROUP BY p.user_id ORDER BY NumPosts DESC" $dbname
 
Last edited:
  • Like
Reactions: rdn
Top Bottom