1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Database structure for making a list of popular pages

Discussion in 'Databases' started by mad4, Oct 24, 2006.

  1. #1
    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?
     
    mad4, Oct 24, 2006 IP
  2. rosytoes

    rosytoes Peon

    Messages:
    230
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    if you already have the code to find the most popular pages ever, then just find that query and put in a date criteria.
     
    rosytoes, Oct 24, 2006 IP
  3. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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.
     
    T0PS3O, Oct 24, 2006 IP
  4. mad4

    mad4 Peon

    Messages:
    6,986
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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.
     
    mad4, Oct 24, 2006 IP