[cfish]

I am trying to create a list of the top posters in the last week (7 days) but I'm having a problem getting it to work. Here's what I have so far:

PHP Code:

$query = "SELECT userid, username COUNT(*) AS totalposts FROM " . $prefix . "post WHERE dateline > DATE_SUB(CURDATE(), INTERVAL 1 WEEK) GROUP BY userid ORDER BY totalposts DESC LIMIT 10";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result))
    {
    $user_id = $row[userid];
    $user = $row[username];
    $posts = $row[totalposts];

print "<li><a href=\"/forum/member.php?u=$user_id\" title=\"$posts Posts\">$user</a></li>\n";
} 


When I run this, I get:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in...

Which presumably means that there's something wrong with $result. I keep looking at this and can't see what's wrong. Could some kind person put me out of my misery?

******************************

OK, I found it - a missing comma in the SQL query

So, for those interested, here is a generic code fragment that will find the top posters in the last 7 days and print them out as a list along with the number of posts and a link to their profile page.

PHP Code:

# link to the database, then test the connection
$link = mysql_connect("localhost", $user, $password);
if (!$link) die ("cant connect to mysql");

# select the database once connected
mysql_select_db($db, $link) or die ("cannot connect");

# the time one week ago
$week=time()-604800;

# query and results for top posters in the last 7 days
$query = "SELECT userid, username, COUNT(*) AS totalposts FROM post WHERE dateline > $week GROUP BY userid ORDER BY totalposts DESC LIMIT 10";
$result = mysql_query($query);

# close the sql connection
mysql_close($link);

# display top posters as an unordered list
print "\n<!-- start top posters week module -->\n\n";
print "<p>Top Posters - Week</p>\n";
print "<ul>\n";

while ($row = mysql_fetch_array($result))
    {
    $user_id = $row[userid];
    $user = $row[username];
    $posts = $row[totalposts];

    print "<li><a href=\"/forum/member.php?u=$user_id\" title=\"$posts posts in the last 7 days\">$user ($posts)</a></li>\n";
}

print "</ul>\n\n";
print "<!-- end top posters week module -->\n"; 


Any suggestions for improvement gratefully received.