effective counter for unique number of visits in PHP & MySQL

Question

Hello,

I am creating a counter for unique number of visits on a post, so what I have until now is a table for storing data like this;

cvp_post_id  | cvp_ip | cvp_user_id

In cases a registered user visits a post, for the first time a record is inserted with cpv_post_id and cvp_user_id, so for his next visit I query the table and if the record is available I do not count him as a new visitor.

In cases of an anonymous user the same happens but now the cvp_ip and cpv_post_id are used.

My concerns is that I do a query every time anyone visits a post for checking if there has been a visit, what would be a more effective way for doing this?

Answer 1

Create a unique index containing your three columns and execute your insert using IGNORE keyword:

INSERT IGNORE INTO your_table (cvp_post_id, cvp_user_id)
VALUES(1, 1);

Most users will only visit once so you avoid a SELECT followed by an INSERT.

Answer 2

There is a very simple way that can help you to save a lot of queries using sessions, it's should be something like that:

if(!isset($_SESSION['visited_post'][$yourPostID]))
{
    //Perform your code
    $_SESSION['visited_post'][$yourPostID] = true;
}

Answer 3

Make a session for the user and while the session is on you need to check but once (when opening the session). From now on when the user visits a post you already know he is in the database.