SQL INSERT 查询未更新 PHP Web 应用程序中的列

Question

我最近修改了一些代码，使我的 quiz.php 脚本能够容纳多个测验，而不是只容纳一个。为此，当用户单击测验链接时，我发送了 quiz_id 和 quiz_title 变量，并使用 $_GET 接收它们。但是，一旦提交测验表单，high_score 表中的 quiz_id 列就不再更新。

这是 quiz.php 的代码

<?php
    // Start the session
    require_once('startsession.php');

    // Insert the Page Header
    $page_title = "Quiz Time!";
    require_once('header.php');

    require_once('connectvars.php');

    // Make sure user is logged in
    if (!isset($_SESSION['user_id'])) {
        echo '<p>Please <a href="login.php">log in</a> to access this page.</p>';
        exit();
    }

    // Show navigation menu
    require_once('navmenu.php');

    // Connect to database
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

    // Declare $quiz_id
    $quiz_title = $_GET['title'];
    $quiz_id = $_GET['id'];
    // print_r($quiz_title);
    // print_r($quiz_id);

    // Grab list of question_id's for this quiz
    $query = "SELECT question_id FROM question WHERE quiz_id = '" . $quiz_id . "'";
    $data = mysqli_query($dbc, $query);
    $questionIDs = array();
    while ($row = mysqli_fetch_array($data)) {
        array_push($questionIDs, $row['question_id']);
    }


    // Create empty responses in 'quiz_response' table
    foreach ($questionIDs as $questionID) {
        $query = "INSERT INTO quiz_response (user_id, question_id) VALUES ('" . $_SESSION['user_id'] . "', '" . $questionID . "')";
        mysqli_query($dbc, $query);
    }


    // If form is submitted, update choice_id column of quiz_response table
    if (isset($_POST['submit'])) {
        // Inserting choices into the response table
        foreach ($_POST as $choice_id => $choice) {
            $query = "UPDATE quiz_response SET choice_id = '$choice', answer_time=NOW() " .
            "WHERE response_id = '$choice_id'";
            mysqli_query($dbc, $query);
        }

        // Update the 'is_correct' column
        // Pull all is_correct data from question_choice table relating to specific response_id
        $total_Qs = 0;
        $correct_As = 0;
        foreach ($_POST as $choice_id => $choice) {
            $query = "SELECT qr.response_id, qr.choice_id, qc.is_correct " . 
            "FROM quiz_response AS qr " . 
            "INNER JOIN question_choice AS qc USING (choice_id) " . 
            "WHERE response_id = '$choice_id'"; 
            $data=mysqli_query($dbc, $query);
            // Update is_correct column in quiz_response table
            while ($row = mysqli_fetch_array($data, MYSQLI_ASSOC)) {
                $total_Qs ++;
                if ($row['is_correct'] == 1) {
                    $query2 = "UPDATE quiz_response SET is_correct = '1' " . 
                    "WHERE response_id = '$row[response_id]'";
                    mysqli_query($dbc, $query2);
                    $correct_As ++;
                }
            }   
        }       

        // Update high_score table with $correct_As
        $quiz_id = $_POST['quiz_id'];
        $query = "INSERT INTO high_score " . 
        "VALUES ('0', '" . $_SESSION['user_id'] . "', '" . $quiz_id . "', '" . $correct_As . "', NOW())";
        mysqli_query($dbc, $query);

        // Display score after storing choices in database
        echo 'You got ' . $correct_As . ' out of ' . $total_Qs . ' correct';

        exit();
        mysqli_close($dbc);
    }



    // Grab the question data from the database to generate the form
    $Q_and_Cs = array();
    foreach ($questionIDs as $questionID) {
        $query = "SELECT qr.response_id AS r_id, qr.question_id, q.question " . 
           "FROM quiz_response AS qr " . 
           "INNER JOIN question AS q USING (question_id) " . 
           "WHERE qr.user_id = '" . $_SESSION['user_id'] . "' " .
           "AND qr.question_id = '" . $questionID . "'";
        $data = mysqli_query($dbc, $query) 
            or die("MySQL error: " . mysqli_error($dbc) . "<hr>\nQuery: $query");
        // Store in $questions array, then push into $Q_and_Cs array
        while ($row = mysqli_fetch_array($data, MYSQL_ASSOC)) {
            print_r($row);
            $questions = array();
            $questions['r_id'] = $row['r_id'];
            $questions['question_id'] = $row['question_id'];
            $questions['question'] = $row['question'];
            // Pull up the choices for each question
            $query2 = "SELECT choice_id, choice FROM question_choice " .
            "WHERE question_id = '" . $row['question_id'] . "'";
            $data2 = mysqli_query($dbc, $query2);
            while ($row2 = mysqli_fetch_array($data2, MYSQL_NUM)) {
                $questions[] = $row2[0];
                $questions[] = $row2[1];
            }
            array_push($Q_and_Cs, $questions);
        }
    }
    mysqli_close($dbc);


    // Generate the quiz form by looping through the questions array
    echo '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">';
    echo '<h2>' . $quiz_title . '</h2>';
    $question_title = $Q_and_Cs[0]['question'];
    echo '<label for="' . $Q_and_Cs[0]['r_id'] . '">' . $Q_and_Cs[0]['question'] . '</label><br />';
    foreach ($Q_and_Cs as $Q_and_C) {
        // Only start a new question if the question changes
        if ($question_title != $Q_and_C['question']) {
            $question_title = $Q_and_C['question'];
            echo '<br /><label for="' . $Q_and_C['r_id'] . '">' . $Q_and_C['question'] . '</label><br />';
        }
        // Display the choices
        // Choice #1
        echo '<input type="radio" id="' . $Q_and_C['r_id'] . '" name="' . $Q_and_C['r_id'] . '" value="' . $Q_and_C[0] . '" />' . $Q_and_C[1] . '<br />';
        // Choice#2
        echo '<input type="radio" id="' . $Q_and_C['r_id'] . '" name="' . $Q_and_C['r_id'] . '" value="' . $Q_and_C[2] . '" />' . $Q_and_C[3] . '<br />';
        // Choice #3
        echo '<input type="radio" id="' . $Q_and_C['r_id'] . '" name="' . $Q_and_C['r_id'] . '" value="' . $Q_and_C[4] . '" />' . $Q_and_C[5] . '<br />';
        // Choice #4
        echo '<input type="radio" id="' . $Q_and_C['r_id'] . '" name="' . $Q_and_C['r_id'] . '" value="' . $Q_and_C[6] . '" />' . $Q_and_C[7] . '<br />';
    }
    echo '<br /><br />';
    echo '<input type="hidden" name="quiz_id" value"'.$quiz_id.'" />';
    echo '<input type="submit" value="Grade Me!" name="submit" />';
    echo '</form>';
    // echo 'Quiz_id: '.$quiz_id.'<br />';


    // Insert the page footer
    require_once('footer.php'); 

?>

这是 quizlist.php 的代码

  // Determine number of quizes based on title in quiz table
  $query = "SELECT * FROM quiz";
  $data = mysqli_query($dbc, $query);
  // Loop through quiz titles and display links for each
  while ($row = mysqli_fetch_array($data, MYSQL_ASSOC)) {
    echo '<a href="quiz.php?id='.$row['quiz_id'].'&amp;title='.$row['title'].'">' . $row['title'] . '</a><br />';
  }

  mysqli_close($dbc);

我的问题与这段代码有关

$query = "INSERT INTO high_score " . 
            "VALUES ('0', '" . $_SESSION['user_id'] . "', '" . $quiz_id . "', '" . $correct_As . "', NOW())";

当我用数字（即 2）代替 $quiz_id 时它就起作用了，但为了使脚本适用于不同的测验，我需要能够对不同的测验使用不同的 quiz_id 。

我无法使用 $_GET 从 quizlist.php 获取变量，然后在提交表单时将其作为隐藏值传递。 我做错了什么吗？或者我错过了一些完全明显的东西？我将不胜感激任何帮助！谢谢...

Answer 1

在第一个线索上，在我看来，您正在收到 $quiz_id 表单 GET 请求（这是正确的），但您有一个条件

if (isset($_POST['submit'])) {

，只有在提交表单（POST 请求）时才满足，而不是单击链接。所以当你点击链接时，这种情况下的所有代码都不会被执行