Most Viewed (this week) (this month)

[postalservice14]

I am trying to implement a Most Viewed this week this month function for my web site.  My database is setup as follows:

PAGE
  page_id
  topic

PAGE_VISITS
  visits_id
  page_id
  timestamp

Everytime someone visits a page, it gets logged with the page_id and timestamped in the PAGE_VISITS table of the DB.

What I want to do is return the N number of "most visited" pages.  And I thought this DB structure would be the best way to do it...Am I wrong?  I gotta say I'm stumped now.  Is there some crazy SQL statement I can do?

Thanks in advance.

John

[printf]

Ok quick example... (only for month), but you can use (between, interval) and many different options, it all depends on what you want the result to show...

// top 3 pages for 11 month of 2006

[code]SELECT p.page_id, COUNT(v.visits_id) AS total FROM PAGE AS p
LEFT JOIN PAGE_VISITS AS v ON(p.page_id = v.page_id)
WHERE MONTH(`v.timestamp`) = 11 AND YEAR(`v.timestamp`) = 2006 ORDER BY total DESC LIMIT 3;[/code]

As I said there are hundreds of different formats, it depends on what exactly you want to display...

printf