How to synchronize viewing records to MySQL with PHP

Recently, I was using PHP to write a video viewing website, and found that a very common requirement is to save the user's viewing history into the database and update it synchronously at any time. Based on this requirement, I tried a variety of implementation methods and finally chose the solution of synchronizing viewing records to the MySQL database. This article will share how I use PHP to synchronize viewing records to MySQL.

First, I created a table named watch_history in the MySQL database, which contains the following fields: id, user_id, video_id, and created_at. Among them, id is the primary key, user_id refers to the user's ID, video_id refers to the ID of the video watched, and created_at refers to the creation time of the record.

Next, in the PHP code, I created a class called WatchHistory, which includes the following methods:

1. add($userId, $videoId): New A user’s viewing record
2. getList($userId): Get the specified user’s viewing record list
3. clean($userId): Clear all viewing records of the specified user

In the add method, I use the INSERT INTO statement to add a record to the watch_history table. The sample code is as follows:

public function add($userId, $videoId) {
    $created_at = date('Y-m-d H:i:s');
    $sql = "INSERT INTO watch_history (user_id, video_id, created_at) VALUE ('$userId', '$videoId', '$created_at')";
    // 执行 MySQL 插入操作
    // ...
}

In the getList method, I use the SELECT statement to get all the viewing records of the specified user from the watch_history table. The sample code is as follows:

public function getList($userId) {
    $sql = "SELECT * FROM watch_history WHERE user_id = '$userId' ORDER BY created_at DESC";
    // 执行 MySQL 查询操作
    // ...
}

In the clean method, I use the DELETE statement to delete all viewing records of the specified user from the watch_history table. The sample code is as follows:

public function clean($userId) {
    $sql = "DELETE FROM watch_history WHERE user_id = '$userId'";
    // 执行 MySQL 删除操作
    // ...
}

Finally, in order to avoid frequent connection and closing of database connections, I used a singleton class named Database to manage the connection to the MySQL database. The sample code is as follows:

class Database {
    private static $_instance;
    private $_pdo;
    private function __construct() {
        // 连接 MySQL 数据库
        // ...
    }
    public static function getInstance() {
        if (!self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
    public function query($sql, $params = null) {
        $stmt = $this->_pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}

So far, I have successfully implemented the function of synchronizing viewing records to the MySQL database. Although this is just a simple example, I believe this solution is also feasible in actual development. I hope my experience can be helpful to all PHP developers.

The above is the detailed content of How to synchronize viewing records to MySQL with PHP. For more information, please follow other related articles on the PHP Chinese website!