Most efficient way of storing daily page views, as well as a total count

Question

There allot of discussion on storing page views for an article or video in a database, but I can't seem to find any information on storing daily page views. For example DeviantArt shows you the past 15 or so days and how many page views each one got in a little graph, as well as the total page view for the profile.

You can see above exactly what I'm trying to achieve, and DeviantArt do it on a huge scale as they get millions of hits.

I am using CodeIgniter on PHP and MySQL

Answer 1

You could have a row for each day then increment the count when it is viewed.

INSERT INTO views (day,views) VALUES (CURDATE(),1) ON DUPLICATE KEY UPDATE views=views+1;

And a PHP script...

mysql_query("INSERT INTO views (day,views) VALUES (CURDATE(),1) ON DUPLICATE KEY UPDATE views=views+1;");

This uses a table called 'views' and 2 rows 'day' and 'views'.

See this article for more information.