How can I track which items in my table were the most viewed in the last 24 hours? For instance, Digg has a list of the most viewed in the last 24 hours, 7 days, 30 days.. etc... I want to do the same thing.
when the page loads, increment a value in a table full of page views. i'd set the table up with something like this: id, page, count, ip, dtime then when the page is loaded, select the page from the db where page = page and update count += 1. then when you print the results do some date formatting then. select * from tbl1 where dtime between time1 and time 2
ansi's method is the first idea that comes to thought but will produce a table that will become incredibly huge very quickly. I use 5 fields per item: totalViews, views and viewsData, viewsToday, viewsThisWeek. You can also have more fields for more intervals. When someone visits the page, views and totalViews are incremented. Then a cron runs every hour and stores the value of the "views" field along with a reduced timestamp and appends it to viewsData, a CSV field. This is optimised such that data older than 24 hours is summed for each day/week. It also parses the viewsData to get a "views for today" field. To calculate this everytime is a waste of resources so I cache it in viewsToday. Of course, the values will not be exact but very close, and insignificant on a large scale when there are many views on the site. I think this is the most efficient method when it comes to minimising server load. You can always reduce the interval from 1 hour but keep in mind that there will be more data stored in the database.