Just looking for a bit of input on a project I'm doing. Basically I want to be able to list the most popular pages on a clients site. The unusual bit is that I want the list to show the most popular pages for the previous 7 days rather than for all time. I thought of a few ways but they all seemed too bulky. Whats the best database structure to do this?
if you already have the code to find the most popular pages ever, then just find that query and put in a date criteria.
I do it with osCommerce like this. Each pahe has a 'created' and 'last modified' date. Each page has also a column with page views. Sp I calculate how long it's been since creation (or modification) and divide the ipressions by it so I have a ranking. But that doesn't allow for 'last 7 days' only. For that you will probably have to record date stamps for each impression and then purge/average them on a cron every week or so to avoid a massive database. So you could do a table LAST_7_DAYS_IMPS with page id and time stamp Then use that data every week to purge the values in a total page views.
Cheers guys. Stuff to think about. What I may do is keep a column in the db to track the hits that day (T) using a simple "update hits=hits+1" query and another column to track the cumulative hits for the last 7 days. The figures can be then updated each day by deducting the value for T-7 days from the cumulative score and adding the value for T to get the new 7 day total. With some cookies and a script to ignore spiders this should work OK. Edit: That would mean I had to store the daily totals as well. Hmmm.