postalservice14 Posted November 17, 2006 Share Posted November 17, 2006 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 topicPAGE_VISITS visits_id page_id timestampEverytime 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 Quote Link to comment Share on other sites More sharing options...
printf Posted November 17, 2006 Share Posted November 17, 2006 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.