Page view stats - is this way too wasting?

PHP
1

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 or is ok?


$minDate=time() - (365 * 24 * 60 * 60);
$maxDate=time();
$yearStat=$db->get_results("SELECT * FROM page_year_stat WHERE date>$minDate AND date<$maxDate");
$pageViews=0;
if($yearStat){
  foreach($yearStat as $date=>$value){
    $pageViews=$pageViews+$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]).

2

Check this out:

3

Create table

CREATE TABLE `stats` (
  `viewdate` date NOT NULL DEFAULT '1970-01-01',
  `pageid` int(11) unsigned NOT NULL,
  `views` int(11) NOT NULL DEFAULT '1',
  UNIQUE KEY `date_page` (`viewdate`,`pageid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert some random dummy data

INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO stats (viewdate,pageid) VALUES (DATE_SUB(CURDATE(),INTERVAL 3*rand() DAY),5*rand()) ON DUPLICATE KEY UPDATE views = views + 1;

Get stats for page 1 last year (see interval):

SELECT viewdate, pageid, SUM(views) AS pageviews
FROM stats
WHERE viewdate &gt; SUBDATE(CURDATE(),INTERVAL 1 YEAR)
AND pageid = 1
GROUP BY viewdate
ORDER BY viewdate DESC

Get stats for all pages last week:

SELECT viewdate, pageid, SUM(views) AS pageviews
FROM stats
WHERE viewdate &gt; SUBDATE(CURDATE(),INTERVAL 7 DAY)
GROUP BY viewdate, pageid
ORDER BY viewdate DESC

Etc.

4

thank you, I think now everything is clear. So this means that 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?

5

You should be asking the database for these values, not calculating them in PHP. :wink:

6

Are you tracking unique visitors or just pageviews?

7

Fantastic, thank you :slight_smile:

It works, but I need to remove GROUP BY viewdate. I am not sure why you put it there. Also I am not sure why ORDER BY viewdate DESC since it SUM all days in the interval.

8

SUM(views) GROUP BY(viewdate) returns all pageviews that day, maybe not the best example…

You probably want something like this:

One page:

SELECT SUM(views) AS pageviews
FROM stats
WHERE viewdate > SUBDATE(CURDATE(),INTERVAL 1 YEAR)
AND pageid = 1

By pageid:

SELECT pageid, SUM(views) AS pageviews
FROM stats
WHERE viewdate > SUBDATE(CURDATE(),INTERVAL 1 YEAR)
GROUP BY pageid

All pages:


SELECT SUM(views) AS pageviews
FROM stats
WHERE viewdate > SUBDATE(CURDATE(),INTERVAL 1 YEAR)

Just change the interval unit and specifier to get the timespan you want.