php - SQL查询插入两次

Question

我正在创建一个测验页面，该页面会提出问题，最后会显示最高分表。我通过 Ajax 访问此页面以插入用户名和分数，并且插入了两次。

<?php
    $servername = "localhost";
    $username = "root";
    $password = "pswd";
    $dbname = "mydb";
    $toJsonArr = array();

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    if (isset($_GET["username"])) {
        $username = $_GET["username"];
        $score = $_GET["score"];

        $sql = "INSERT INTO `fullstackQuiz` (`id`, `place`, `username`, `points`, `now`) VALUES (NULL, '0', '$username', '$score', CURRENT_TIMESTAMP);";
        $result = $conn->query($sql);

        if ($conn->query($sql) === TRUE) {
            echo "1";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }

        $conn->close();

    } else {
        $sql = "SELECT * FROM `fullstackQuiz` ORDER BY `fullstackQuiz`.`points` DESC LIMIT 10";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                  $toJsonArr[] = $row;
            }
        } else {
            echo "0 results";
        }

        echo json_encode($toJsonArr);
        $conn->close();
    }
?>

我的 Ajax 代码:

$.ajax({
  type: "GET",
  url: "sql.php",
  data: { username: "abc", score: "99" },
  success: function(data) {
    console.log("success");
  }
});

每次 Ajax 运行时，由于某种原因，它都会创建两次记录。

Answer 1

您重复了$conn->query($sql)两次。删除一个就可以了:

//$result = $conn->query($sql); <== Remove this line.

if ($conn->query($sql) === true) {
    echo "1";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

也不要将 NULL 传递给自动增量 ID，只需将其从查询中排除即可。如果 place 字段是数字，则将该值作为不带撇号的数字传递:

$sql = "INSERT INTO `fullstackQuiz` (`place`, `username`, `points`, `now`)
        VALUES (0, '$username', '$score', CURRENT_TIMESTAMP);";

最后，您的查询容易受到 SQL 攻击。请改用参数化查询。