Query users posts for the month...

[roundhost]

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]

[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]

Thanks

[roundhost]

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]

take a look at the post below...

[roundhost]

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]

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