php - 如何防止数据库中存在多个这样的条目？

Question

有没有办法阻止用户多次点击而不像这样将它们插入到数据库中？

我知道我可以禁用该按钮，但我想在不重新加载页面的情况下完成这一切。

    $dbh = mysqli_connect("localhost", "root", "", "testdb");
$postid = $_POST['postid'];
if(isset($_POST['liked'])&& empty($_SESSION[$postid])){
    $_SESSION[$postid] = TRUE;

    $userid = $_SESSION['username'];
    $result = mysqli_query($dbh,"SELECT * FROM user_images WHERE id=$postid");
    $row = mysqli_fetch_array($result);
    $n = $row['likes'];
    mysqli_query($dbh,"INSERT INTO likes(username, postid) VALUES('$userid', '$postid')");
    mysqli_query($dbh,"UPDATE user_images SET likes=$n+1 WHERE id=$postid");
    echo $n+1;
    exit();
}


if (isset($_POST['unliked'])){
    $postid = $_POST['postid'];
    $result = mysqli_query($dbh,"SELECT * FROM user_images WHERE id=$postid");
    $row = mysqli_fetch_array($result);
    $n = $row['likes'];
    $userid = $_SESSION['username'];
    //delete from likes before updating
    mysqli_query($dbh,"DELETE FROM likes WHERE postid=$postid AND username=$userid");
    mysqli_query($dbh,"UPDATE user_images SET likes=$n-1 WHERE id=$postid");
    echo $n-1;
    exit();
}

Answer 1

首先您需要创建一个unique index在表中，以确保即使您的 PHP 代码中有错误，也不允许出现重复的条目。由于用户可以喜欢多个帖子，因此您必须使用两列创建索引:

create unique index IDU_likes on likes (username,postid);

然后你必须修改你的代码并检查插入是否失败(无论如何你应该总是这样做)。另外，您使用的代码对 SQL injection 开放。 ，你应该使用prepared statements和 bind the variables而不是手动构建查询，例如:

$stmt=mysqli_prepare($dbh,"INSERT INTO likes(username, postid) VALUES(?, ?)");
mysqli_stmt_bind_param($stmt,'si',$userid,$postid);
if(mysqli_stmt_execute($stmt) {
  $n=$n+1;
  $stmt=mysqli_prepare($dbh,"UPDATE user_images SET likes=? WHERE id=?");
  mysqli_stmt_bind_param($stmt,'ii',$n,$postid);
  mysqli_stmt_execute($stmt)
}
echo $n;