Count the number of times post has been viewed

Question

I am working on a project where only title of posts are shown on main page and on clicking the title, full post is loaded on another page posts.php code for this is:

<a href="posts.php?postId=<?php echo $row['posts_id'] ?>"><?php echo $row['title']; ?></a>

Now to count post views I have a column hits in my posts table ,initially value of hits is set to 0 and whenever a post is opened value of hits is increased by 1, for this my code is in posts.php

$id = $_GET['postId'];
$sql = "UPDATE posts SET hits = hits + 1 WHERE post_id = $id"; 

But this is not a good practice for tracking post views as views increase whenever page is refreshed. I want a clean system to track post views where, for every distinct user or visitor views increase by one irrespective of fact how many times the same user/visitor views same post (as in stackoverflow). Like tracking them by their IP address or something else, just an idea (how these guys do it) or how the stuff works would be enough to let me start my work.

Answer 1

You cannot solve your problem so simply. Your problem is counting unique users who view a page (with, perhaps a time component). You have several problems, as described in the comments. The first is determining what you are counting. For a prototype, IP address is good as anything else for getting started. However, it has many short-comings, and you will need to think hard about identifying a "visitor".

There is a way to solve your problem in SQL, sort of efficiently. But, it requires an additional table at the level of post/visitor. It will have one row per combination, and then you will need to count from there. To quickly get the unique counts, you then need an insert trigger on that table.

Here is a sketch of the code:

create unique index unq_postvisitors_post_visitor on postvisitors(postid, visitorid);

insert into PostVisitors (postid, visitorid)
    select $postid, $visitorid
    on duplicate key update set counter = counter + 1;

delimiter $$
create trigger trig_PostVisitors
    after insert on PostVisitors
begin
    update posts
        set numvisitors = numvisitors + 1
        where posts.post_id = new.post_id;
end;$$

delimiter ;