PHP and MySQL are the best way to count page views for dynamic pages

Question

What is the best way to count page views for dynamic pages like the example URL below? I use PHP and MySQL. This will help a brief explanation. Thanks!

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

Answer 1

Typically, 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 instruction for increasing views is 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, a row for the page will be created if it does not exist; if it exists (this is a duplicate key sentence), the counter will increment.

I chose two separate tables here, since CMS pages are usually not updated too often (and therefore, their downloads are mostly read), while page views are read and updated, well, with every page view.

Answer 2

Well, you can just add the pageviews field to your page table and query UPDATE pageviews = pageviews +1 WHERE id = 1 each time the page loads

Answer 3

To view statistics for days / weeks / months / years, I compiled two tables. The first archives of all visits to the site with my page and ID are stored on one line. The second table records tables such as Piskvor.

The advantage is that I can view statistics for any page and id that I want over time (but it will be many lines over time ...), or I can just view the total pageviews. For visitors to my site, I serve the information from this second table, but my admin panel fully uses 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 / need to do, or even if my table structure is better, but it works for me.

Answer 4

This is my code and it works correctly when I open the page. When I refresh the page, page views increase by 1. If page_id does not exist, it will insert a record with views = 1, if page_id exists it will increase 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)); 

Answer 5

Just increase the integer in the message that you are currently serving.

Answer 6

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