homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

page view stats - is this way too wasting?

Msg#: 4217905 posted 10:53 am on Oct 17, 2010 (gmt 0)

I need to show statistics per:
7 days
1 month
1 year

When user visit the page, function check if this date is already in database. If it is, it update field views+1, else create (INSERT INTO pageviews (pageId, pageviews, date) VALUES ('$pageId', 1, '".time()."').

I am wondering if the following method to wasting for database or is ok?
$minDate=time() - (365 * 24 * 60 * 60);
$yearStat=$db->get_results("SELECT * FROM page_year_stat WHERE date>$minDate AND date<$maxDate");
foreach($yearStat as $date=>$value){

2nd option
Or would be better to save data for each month, and than just cut some days from previous month and take some days from current month (because 1 month means [30 days - now] and not [1st day of the current month -current day of the current month]).



Msg#: 4217905 posted 11:25 am on Oct 18, 2010 (gmt 0)

I found a better way. I need to create 3 more fields:

table pageViews
pageId | dayViews | monthViews | yearViews | date

This would means 3 queries to get day, month and year views:
$yearAgo=time() - (365 * 24 * 60 * 60);
$monthAgo=time() - (30 * 24 * 60 * 60);
SELECT dayViews, monthViews, yearViews FROM pageViews ORDER BY date DESC
SELECT dayViews FROM pageViews WHERE date<$yearAgo ORDER BY date DESC
SELECT dayViews FROM pageViews WHERE date<$monthAgo ORDER BY date DESC

In this way I get all data from the most recent date. Than I need to subtract from this date pageViews from the first day of 30 and 365 days ago.

and finally 1 extra query to create or update row with 1 more pageViews

INSERT INTO pageViews (pageId, dayViews, monthViews, yearViews, date)VALUES($pageId, 1, $monthViews, $yearViews)
ON duplicate KEY UPDATE dayViews=dayViews+1,monthViews=monthViews+1,yearViews=yearViews+1,;

where primary key is date.

Is 4 queries in each pageview for statistic in this way too wasting?

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved