PHP And MySQL Tricks - How to increment article or image views

We will use the MySQL function TIMESTAMPDIFF, the process is simple enough : we check the difference between the last time a user identified by its IP adress viewed our page and the current time, if the said difference is greater than a defined value, we increment the view.

the TIMESTAMPDIFF function:

The MySQL TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another.
It is not necessary that both the expression are of the same type. One may be a date and another is datetime. A date value is treated as a datetime with a default time part '00:00:00'. The unit for the result is given by another argument.
The unit should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
The syntax is :

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

Getting the user ip adress

We need to have a way to identify our visitors in order to count our page views, and the best way to do it is by saving the ip adresses of our visitors. the following PHP function will allow us to do so:

function getUserIP() {
    if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    } else {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}

Incrementing the views

consider the following elements to understand this tutorial:
our database consists of the following two tables :

articletable(id,views)
viewstable(articleid,vieweripadress,viewdate)

Here is what we are going to do :

• 1 - we will try to insert a new record of (articleid,vieweripadress) into our viewstable, if the insert query succeeds then it is a new record, we create a boolean variable $newrecord = true and it will help us decide whether we will increment the views or not.
• 2 - we will make a query to get the difference in hours between the last view by our user and the current time. we simply select a $increment value of "yes" or "no" depending on the time difference.
• 3 - if either $newrecord is true or $increment is "yes" we will update the viewdate and incement the views

Inserting a new record:

// for this code to work you need to have the pdo error mode set to exception
// with this:
// $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
// the $id is the article id
// I assume that the viewdate default value is set to CURRENT_TIMESTAMP() when we created
// our tables.

$ip = getUserIP();
$query = " INSERT INTO viewstable (articleid,vieweripadress) VALUES (?,?) ";
try{
    $sql = $pdo->prepare($query);
    $sql->execute([$id,$ip]);
    $newrecord = 1 ;
} catch(PDOException $e) {
    $newrecord = 0 ;
}

At this point, either we inserted a new record for a new visitor and $newrecord is true, or the visitor is new and a record has already been inserted before and $newrecord is false.
Now we compare the record( old or new) to the current datetime :

$query = " SELECT IF(TIMESTAMPDIFF(HOUR,`viewdate`,CURRENT_TIMESTAMP())> 4  ,'yes','no') as status
           FROM viewstable WHERE articleid = ? AND vieweripadress = ? ";
$sql = $this->dbh->prepare($query);
$sql->execute([$id,$ip]);
$increment = $sql->fetch()["status"];

Finally, we check if we can update the viewdate and increment the views:

if ( $increment == "yes" || $newrecord == 1 ) {
    $query="update articletable set views = views + 1 where id= ? ";
    $sql = $this->dbh->prepare($query);
    $sql->execute([$id]);
    $query=" update viewstable set viewdate = CURRENT_TIMESTAMP()
             where articleid= ? and vieweripadress = ? ";
    $sql = $this->dbh->prepare($query);
    $sql->execute([$id,$ip]);
}