php - 使用 SQL 和 PHP 检查特定数据是否已在表中

Question

我有一个数据库，我想检查数据是否已输入其中。用户可以使用表单输入数据，然后将其添加到表中。然而，当我查看数据库并且可以看到重复项时，似乎没有进行检查。

这是我想要进行的检查:

$sqlCheck = "SELECT ActivID, DateRequired, TimeRequired FROM activBooking WHERE ActivID = '$_POST[ActivID]' AND DateRequired = '$_POST[DateRequired]' AND TimeRequired = '$_POST[TimeRequired]'";

if($sqlCheck->num_rows > 0) {
    echo "Sorry, this time slot is unavailable for this activity";
} else {
    $sql = "INSERT INTO activBooking (CustomerID, ActivID, DateRequired, TimeRequired) VALUES ('$_POST[CustomerID]','$_POST[ActivID]','$_POST[DateRequired]','$_POST[TimeRequired]')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
        echo "Press back to return";
    } else {
        echo "Error" . $sql . "<br>" . $conn->error;
    }
}

但是，看起来好像第一个 if 语句被跳过，而只是继续向表中添加数据，无论它是否重复。

感谢您的帮助!

Answer 1

在检查行数返回之前执行查询

$sqlCheck = "SELECT ActivID, DateRequired, TimeRequired FROM activBooking WHERE ActivID = '$_POST[ActivID]' AND DateRequired = '$_POST[DateRequired]' AND TimeRequired = '$_POST[TimeRequired]'";

$sqlCheck = $conn->query($sqlCheck); //execute before counting num rows

if($sqlCheck->num_rows > 0) {
    echo "Sorry, this time slot is unavailable for this activity";
} else {
    $sql = "INSERT INTO activBooking (CustomerID, ActivID, DateRequired, TimeRequired) VALUES ('$_POST[CustomerID]','$_POST[ActivID]','$_POST[DateRequired]','$_POST[TimeRequired]')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
        echo "Press back to return";
    } else {
        echo "Error" . $sql . "<br>" . $conn->error;
    }
}