Count number of views for each user

Question

I have table 'viewlogs' which is formed from three field: ViewLogId, VideoId and UserId. ViewLogId has a primary key and AUTO_INCREMENT value. When a user watches a video its value increments. When a user watch a same video the viewLogId increments, but I want this value to increase only once and uniquely. How do I fix this problem. I've attached the table picture.

I used the following php code for updating the view_counts in the "videos" table. The above picture belongs to the viewlogs table.

public function updateStatistics($videoId, $fieldName, $userId)
{
    $this->db->reconnect();
    $this->db->trans_begin();
    $this->db->query("UPDATE videos SET {$fieldName}={$fieldName}+1 WHERE VideoId=?", array($videoId));
    if ($fieldName == "ViewCount" && $userId > 0) {
        $this->db->insert('viewlogs', array('UserId' => $userId, 'VideoId' => $videoId, 'ViewDateTime' => date('Y-m-d H:i:s')));
    }
    if ($this->db->trans_status() === false) {
        $this->db->trans_rollback();
        return false;
    } else {
        $this->db->trans_commit();
        return true;
    }
    return false;
}

can you explain more about issue? because it seems no issue here — nilesh, Feb 22 '17 at 10:49
Possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — JNevill, Feb 22 '17 at 14:45
@nilesh I want the ViewLogId for the same UserId with same VideoId not changed. — RoboticEng, Feb 22 '17 at 19:17

score 0 · Accepted Answer · answered Feb 28 '17 at 15:15

I solved the problem, just need to check the user_id and video_id before updating the view.

public function updateStatistics($videoId, $fieldName, $userId)
{
    $this->db->reconnect();
    $this->db->trans_begin();

    $sql = "SELECT * FROM viewlogs WHERE UserId ={$userId} AND VideoId ={$videoId}";
    $query = $this->db->query($sql);
    $result = $query->result_array();
    if (empty($result)){
        $this->db->query("UPDATE videos SET {$fieldName}={$fieldName}+1 WHERE VideoId=?", array($videoId));

        if ($fieldName == "ViewCount" && $userId > 0) {
            $this->db->insert('viewlogs', array('UserId' => $userId, 'VideoId' => $videoId, 'ViewDateTime' => date('Y-m-d H:i:s')));
        }
    }
    if ($this->db->trans_status() === false) {
        $this->db->trans_rollback();
        return false;
    } else {
        $this->db->trans_commit();
        return true;
    }
    return false;
}

Count number of views for each user

1 Answers1