tracking views (ala digg's most viewed 24 hours)

[knopix]

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.

[ansi]

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

[krt]

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.