PHP实现视频观看记录功能_PHP实现视频观看记录功能

CREATE TABLE `video_watch_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `video_id` int(11) NOT NULL,
  `watch_time` int(11) DEFAULT 0 COMMENT '已观看秒数',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_video_unique` (`user_id`, `video_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php
header('Content-Type: application/json');

// 模拟登录用户（实际项目中从 session 或 token 获取）
$user_id = 1; // 应该动态获取

$data = json_decode(file_get_contents('php://input'), true);
$video_id = (int)$data['video_id'];
$watch_time = (int)$data['watch_time'];

if (!$video_id || $watch_time < 0) {
    echo json_encode(['code' => 400, 'msg' => '参数错误']);
    exit;
}

try {
    $pdo = new PDO("mysql:host=localhost;dbname=test", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 插入或更新观看记录
    $stmt = $pdo->prepare("
        INSERT INTO video_watch_log (user_id, video_id, watch_time) 
        VALUES (?, ?, ?)
        ON DUPLICATE KEY UPDATE watch_time = VALUES(watch_time), updated_at = CURRENT_TIMESTAMP
    ");
    $stmt->execute([$user_id, $video_id, $watch_time]);

    echo json_encode(['code' => 200, 'msg' => '保存成功']);
} catch (Exception $e) {
    echo json_encode(['code' => 500, 'msg' => '服务器错误: ' . $e->getMessage()]);
}
?>

<video id="myVideo" width="800" height="450" controls>
  <source src="example.mp4" type="video/mp4">
</video>

<script>
const video = document.getElementById('myVideo');
let timer = null;

// 视频开始播放时启动定时记录
video.addEventListener('play', () => {
    timer = setInterval(() => {
        const data = {
            video_id: 1001,  // 实际从页面获取
            watch_time: Math.floor(video.currentTime)
        };

        fetch('save_progress.php', {
            method: 'POST',
            body: JSON.stringify(data),
            headers: { 'Content-Type': 'application/json' }
        });
    }, 5000); // 每5秒保存一次
});

// 暂停或结束时清除定时器
video.addEventListener('pause', () => {
    if (timer) clearInterval(timer);
});
video.addEventListener('ended', () => {
    if (timer) clearInterval(timer);
});
</script>

<?php
// get_progress.php
header('Content-Type: application/json');
$user_id = 1;
$video_id = (int)$_GET['video_id'];

if (!$video_id) {
    echo json_encode(['code' => 400, 'msg' => '缺少参数']);
    exit;
}

try {
    $pdo = new PDO("mysql:host=localhost;dbname=test", "root", "");
    $stmt = $pdo->prepare("SELECT watch_time FROM video_watch_log WHERE user_id = ? AND video_id = ?");
    $stmt->execute([$user_id, $video_id]);
    $row = $stmt->fetch();

    $time = $row ? (int)$row['watch_time'] : 0;
    echo json_encode(['code' => 200, 'watch_time' => $time]);
} catch (Exception $e) {
    echo json_encode(['code' => 500, 'msg' => '查询失败']);
}
?>

fetch(`get_progress.php?video_id=1001`)
  .then(res => res.json())
  .then(data => {
      if (data.watch_time > 0) {
          const resume = confirm(`您上次看到 ${formatTime(data.watch_time)}，是否继续？`);
          if (resume) myVideo.currentTime = data.watch_time;
      }
  });

function formatTime(seconds) {
    const min = Math.floor(seconds / 60);
    const sec = seconds % 60;
    return `${min}:${sec.toString().padStart(2, '0')}`;
}