vb.org Archive - Query users posts for the month...

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Query users posts for the month... (https://vborg.vbsupport.ru/showthread.php?t=73687)

roundhost

01-03-2005 10:05 AM

Query users posts for the month...

Hi all,

Basically i plan to query all the users posts for the month by using php to work out the date, and querying how many posts each user has made that month to make a list of the top 10 (exluding admins).

I can do this, but i'm thinking about server load.Going through every single user is quite a task, would it be best to be done on a cron than everytime the page is visited?

RoundHost

Dean C

01-03-2005 10:44 AM

[sql]
SELECT
userid,
username,
COUNT(*) AS totalposts
FROM
post
WHERE
DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateline
GROUP BY
userid
ORDER BY
totalposts DESC
LIMIT
10
[/sql]

That'll do the trick, we're assuming there are 30 days in a month though. Just set it to run at the last day of every month and your users won't know otherwise. If you want it to be completely accurate then I can look that up for you too :)

roundhost

01-03-2005 11:06 AM

Thanks

roundhost

01-03-2005 01:37 PM

It sortof works :S

Basically, im echoing the results out in a table with the following code:

PHP Code:


		
			
echo "<table><tr><td>Username</td><td>Posts</td></tr>";

$result = $DB_site->query("SELECT 

        userid,

        username, 

        COUNT(*) AS totalposts 

FROM 

        post

WHERE 

        DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= dateline

GROUP BY 

        userid

ORDER BY

        totalposts DESC

LIMIT 

        10");



$row = $DB_site->fetch_array($result);



while ($row = mysql_fetch_array($result)) {

echo "<tr><td><a href=\"http://www.youngcoders.com/member$row[userid].html\">$row[username]</td><td>$row[totalposts]</td></tr>";

}

echo "</table>";

This is not the final code, its just be testing to see if it works...

Anyway, the results i get, are not the top 10 posters in the last week, they are the top 10 posters ever, and my only super mod, doesnt appear in the results, yet i do, even though im a admin :S

I do not see how that could have happened...

rake	01-03-2005 01:55 PM

take a look at the post below...

roundhost

01-03-2005 04:35 PM

Quote:

Originally Posted by rake

try this:

$cut = 60*60*24*30;
$cut = time() - $cut;

$a = $DB_site->query("SELECT COUNT(*) as count,userid,username
FROM post
WHERE dateline > $cut
GROUP BY userid
ORDER BY count DESC");
while($user = $DB_site->fetch_array($a))
{
if(in_array($user['usergroupid'],array(6,7))
{
continue;
}

.....
}

That doesnt seem to work at all :(

Thanks for the help though :)

rake	01-03-2005 04:44 PM

1 Attachment(s)

tiny error. but i just tried it and it works... take a look at the attached file.

All times are GMT. The time now is 01:38 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01036 seconds Memory Usage 1,730KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: