PHP & MySQL best way to count page views for dynamic pages

Question

What is the best way to count page views for dynamic pages like the url example below? I'm using PHP and MySQL. A brief explanation would help. Thanks!

http://www.example.com/posts/post.php?id=3

Answer 1

Usually the table structure looks like this:

table pages:

id | name            | ... 
==========================
1    Some Page
2    Some Other Page

table pages_views:

page_id | views
================
1         1234
2         80

where pages_views has a unique index on page_id

The MySQL statement to increment the views then looks as follows:

INSERT INTO `pages_views` SET views=1 WHERE page_id=?
    ON DUPLICATE KEY UPDATE views=views+1 ;

Since pages_views.page_id is unique, the row for the page will get created if it doesn't exist; if it exists (that's the "duplicate key" clause), the counter will be incremented.

I chose two separate tables here, as CMS pages usually aren't updated too often (and therefore, their load is mostly reads), whereas page views are read and updated, well, with each page view.

Answer 2

This is my code and it's working properly when I open the page or When I refresh the page, page views is incrementing by 1. If the page_id doesn't exist it will insert a record with views = 1, if page_id exists it will increment the views

`INSERT INTO pages_views ( pages_id, views) VALUES ( $page_id, 1) ON DUPLICATE KEY UPDATE views=views+1`

With PDO you will have something like this

$sql = "INSERT INTO pages_views ( pages_id, views) VALUES ( :pageId, 1) ON DUPLICATE KEY UPDATE views=views+1";

$q = $conn->prepare($sql);
$q->execute(array(':pageId'=>$pageId));