CODEWITHSUNDEEP
phpmysql
ddb
ddb

Reputation: 81

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

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

Upvotes: 7

Views: 15739

Answers (6)

ursuleacv
ursuleacv

Reputation: 1169

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));

Upvotes: 5

Piskvor left the building
Piskvor left the building

Reputation: 92762

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.

Upvotes: 10

Bryan
Bryan

Reputation: 347

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.

Upvotes: 4

Adnan
Adnan

Reputation: 26350

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

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157896

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

Upvotes: 4

fabrik
fabrik

Reputation: 14365

Just increase an integer on the post you currently serve.

Upvotes: 0

Related Questions