Saturday, 7 September 2013

Order posts by popularity based on date submitted and views

Order posts by popularity based on date submitted and views

The way I am currently trying to sort rows(posts) in the database by
popularity is by dividing the timestamp submitted by the number views the
post has and then order them in ascending order.
my table looks like below
ID submitted views
1 2013-09-03 15:14:05 1
2 2013-09-04 11:13:54 2
3 2013-09-05 18:14:13 3
4 2013-09-06 12:05:05 4
I have tried
SELECT * FROM posts ORDER BY SUM(unix_timestamp(submitted)/views) ASC
LIMIT 30
however it only gives 1 result when I have nearly 40 posts
I have also tried it without the sum function
SELECT * FROM posts ORDER BY unix_timestamp(submitted)/views ASC LIMIT 30
but all this seems to do is display them posts in order of just views in
descending order which is really weird because I have stated ASC.
If is helps in any way I am using PDO.

No comments:

Post a Comment