PHP视频标签管理实现技巧

CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE video_tags (
  video_id INT,
  tag_id INT,
  PRIMARY KEY (video_id, tag_id),
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

$pdo = new PDO('mysql:host=localhost;dbname=video_db', $user, $pass);

foreach ($tagNames as $tagName) {
  $tagName = trim($tagName);
  if (empty($tagName)) continue;

  // 检查标签是否已存在
  $stmt = $pdo->prepare("SELECT id FROM tags WHERE name = ?");
  $stmt->execute([$tagName]);
  $tag = $stmt->fetch();

  if (!$tag) {
    // 插入新标签
    $insertTag = $pdo->prepare("INSERT INTO tags (name) VALUES (?)");
    $insertTag->execute([$tagName]);
    $tagId = $pdo->lastInsertId();
  } else {
    $tagId = $tag['id'];
  }

  // 关联视频和标签
  $linkStmt = $pdo->prepare("INSERT IGNORE INTO video_tags (video_id, tag_id) VALUES (?, ?)");
  $linkStmt->execute([$videoId, $tagId]);
}

$sql = "SELECT v.* FROM videos v
  JOIN video_tags vt ON v.id = vt.video_id
  JOIN tags t ON vt.tag_id = t.id
  WHERE t.name = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([$tagName]);
$videos = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($videos as $video) {
  echo $video['title'] . "
";
}

// 然后重新执行上面的添加逻辑