Post count by date range

[DRJ]

I am having trouble working with the dateline field. What I am trying to do is get the top post counts for 2013. But this code will return results but is not restricting to posts in the date range and just gives all the posts.



// get top 10 posters overall
$poststats="<table border=\"0\">";
$result = $db->query("SELECT p.userid , p.username , u.posts
FROM vb_post AS p, vb_thread AS t, vb_forum As f, vb_user As u
WHERE p.threadid = t.threadid
AND t.forumid = f.forumid
AND p.userid = u.userid
AND p.dateline >= UNIX_TIMESTAMP('2013-01-01 00:00:00')
AND p.dateline < UNIX_TIMESTAMP('2014-01-01 00:00:00')
GROUP By p.userid
ORDER BY u.posts DESC
LIMIT 0 , 10");
While ($getstats_posters = $db->fetch_array($result))
{
$poststats.=$getstats_posters['username']." - ".$getstats_posters['posts']."<br>";
}
$poststats.="</table>";

echo $poststats;

[kh99]

It's displaying the count of all posts because you're displaying the 'posts' column from the user table. I think you want to add something like
COUNT(*) AS yearly_posts

to the select part. Also to be honest I'm not sure what records you get when you select from multiple tables like that - it may not be what you want (or maybe it is, I don't know).

[DRJ]

Gotcha, this worked.

COUNT(u.posts) AS total

Thanks :)

[kh99]

Edit - yeah, I just realized it should have been COUNT.