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

Just increase an integer on the post you currently serve.

Answer 3

Well, you can simply add a field pageviews to your pages table and do UPDATE pageviews = pageviews +1 WHERE id = 1 query on each page load

Answer 4

A simple example can be found at http://www.configure-all.com/page_view_counter.php

Answer 5

For the sake of viewing statistics by day/week/month/year, I have made two tables. The first archives all visits to the site with my page and id saved on the same row. The second table records tallys, such as Piskvor describes.

The benefit is that I can view stats for any page and ID I want over time (but that'll be a lot of rows over time...) or I can simply view total pageviews. For the visitors of my site, I serve information from this second table, but my admin panel makes full use of the first table.

statsEach

 - statID
 - page (example: page 100 is index.php, or 210 is news.php)
 - id (example: 1 is news story 1, 2 is news story 2,...)
 - date
 - time
 - user

and

statsTotal

 - statTotalID
 - page
 - id
 - total

I don't know what you need/want to do, or even if my table structure is best, but this works for me.