使用PHP从数据库生成HTML表单

I'm building a basic website that will offer a quiz dynamically generated from a MySQL database. Based on my current database schema, I'm having trouble understanding how I will generate the 'choices' to different questions in a Quiz Web App.

我正在构建一个基本网站，它将提供从MySQL数据库动态生成的测验。根据我当前的数据库架构，我无法理解如何在Quiz Web App中为不同的问题生成“选择”。

Here is the database schema:

这是数据库模式：

CREATE TABLE user (
    user_id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(32) NOT NULL UNIQUE,
    password VARCHAR(128) NOT NULL,
    ...
) Engine=InnoDB;

CREATE TABLE quiz (
    quiz_id INT UNSIGNED PRIMARY KEY,
    title VARCHAR(64)
) Engine=InnoDB;

CREATE TABLE question (
    question_id INT UNSIGNED PRIMARY KEY,
    quiz_id INT UNSIGNED NOT NULL,
    question VARCHAR(1024),
    FOREIGN KEY (quiz_id) REFERENCES quiz (quiz_id)
) Engine=InnoDB;

CREATE TABLE question_choices (
    choice_id INT UNSIGNED PRIMARY KEY,
    question_id INT UNSIGNED NOT NULL,
    is_correct_choice TINYINT(1),
    choice VARCHAR(512),
    FOREIGN KEY (question_id) REFERENCES question (question_id)
) Engine=InnoDB;

CREATE TABLE quiz_response (
    response_id INT UNSIGNED PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    question_id INT UNSIGNED NOT NULL,
    response INT UNSIGNED NOT NULL,
    is_correct TINYINT(1),
    answer_time FLOAT,
    UNIQUE KEY (user_id, question_id)
    FOREIGN KEY (user_id) REFERENCES user (user_id),
    FOREIGN KEY (question_id) REFERENCES question (question_id),
    FOREIGN KEY (response) REFERENCES question_choices (choice_id),
) Engine=InnoDB;

Here is the code I have produced so far in my quiz.php script:

这是我在quiz.php脚本中生成的代码：

// If this user has never taken this quiz, insert empty responses into the quiz_response table
    $query = "SELECT * FROM quiz_response WHERE user_id = '" . $_SESSION['user_id'] . "'";
    $data = mysqli_query($dbc, $query);
    if (mysqli_num_rows($data) == 0) {
        //First grab the list of questions to create empty responses
        //Grab all questions from question table
        //Rework code in the future to accommodate multiple quizes
        $query = "SELECT question_id from question";
        $data = mysqli_query($data, $query);
        $questionIDs = array();
        while ($row = mysqli_fetch_array($data)) {
            array_push($questionIDs, $row['question_id']);
        }

        // Insert empty response rows into the response table, one row per question
        foreach ($questionIDs as $question_id) {
            $query = "INSERT INTO quiz_response (user_id, question_id) VALUES ('" . $_SESSION['user_id']. "', '$question_id')";
            mysqli_query($dbc, $query);
        }   
    }

    // If the quiz form has been submitted, write the form responses to the database
    if (isset($_POST['submit'])) {
        // Write the quiz response rows to the response table
        foreach ($_POST as $response_id => $response) {
            $query = "UPDATE quiz_response SET response = '$response' WHERE response_id = '$response_id'";
            mysqli_query($dbc, $query);
        }
        echo '<p>Your responses have been saved.</p>
    }

    // Grab the response data from the database to generate the form
    $query = "SELECT qr.response_id, qr.question_id, qr.response, q.question, quiz.quiz " . 
        "FROM quiz_response AS qr " . 
        "INNER JOIN question AS q USING (question_id) " . 
        "INNER JOIN quiz USING (quiz_id) " . 
        "WHERE qr.user_id = '" . $_SESSION['user_id'] . "'";
    $data = mysqli_query($dbc, $query);
    $responses = array();
    while ($row = mysqli_fetch_array($data)) {
        // 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);
        $choices = array();
        while ($row2 = mysqli_fetch_array($data2)) {
            array_push($choices, $row2);
        }
        // Rename choices 




        // Eventually push choices into $responses array
        // array_push($responses, $row);
    }

    mysqli_close($dbc);

    // Generate the quiz form by looping through the response array
    echo '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">';
    echo '<h2>' . $page_title . '</h2>';
    $question_title = $responses[0]['question'];
    echo '<label for="' . $responses[0][response_id'] . '">' . $responses[0]['question'] . '</label><br />';
    foreach ($responses as $response) {
        // Only start a new question if the question changes
        if ($question_title != $response['question']) {
            $question_title = $response['question'];
            echo '<br /><label for="' . $response['response_id'] . '">' . $response['question'] . '</label><br />';
        }
        // Display the choices
        // Choice 1
        // Choice 2
        // Choice 3
        // Choice 4


    }
    echo '<br /><br />';
    echo '<input type="submit" value="Grade Me!" name="submit" />';
    echo '</form>';

I'm having trouble pulling the choice options out of the question_choice table and using them to populate the form. Can I put the choice_id and choice columns into the $responses array and access them in the generating form section without renaming them? At this point I feel I need to rename. Any help would be greatly appreciated!

我无法从question_choice表中提取选项并使用它们来填充表单。我可以将choice_id和choice列放入$ responses数组并在生成表单部分访问它们而不重命名它们吗？此时我觉得我需要重命名。任何帮助将不胜感激！

2 个解决方案

#1

I hope I'm understanding your question correctly. It seems like you're asking given the structure of your data, how would you represent choices to the user.

我希望我能正确理解你的问题。您似乎在询问数据的结构，您将如何向用户表示选择。

Let's say your choice data for a particular question #27801 looks like this in your question_choice table:

假设您在question_choice表中查找特定问题＃27801的选择数据：

choice_id    question_id    is_correct_choice    choice
1            27801          0                    Blue
2            27801          0                    Green
3            27801          1                    Red
4            27801          0                    Shoe

After you've tokenized the data, you can output a group of choices as a radio group with the question_id as part of the group name, and the choice_id as the individual values:

在对数据进行标记后，您可以输出一组选项作为无线电组，其中question_id作为组名称的一部分，choice_id作为单个值：

<input type="radio" name="27801" value="1" /> Blue  <br />
<input type="radio" name="27801" value="2" /> Green <br />
<input type="radio" name="27801" value="3" /> Red   <br />
<input type="radio" name="27801" value="4" /> Shoe  <br />

Then when the quiz has been submitted, you can determine the $correct_choice_num by iterating through each choice looking at the value of is_correct_choice. You can get around having to do this iteration if you store corrent_choice_num in your database, but that might mean having one more table.

然后，当提交测验时，您可以通过迭代查看is_correct_choice值的每个选项来确定$ correct_choice_num。如果在数据库中存储corrent_choice_num，则可以绕过必须执行此迭代，但这可能意味着再增加一个表。

Anyway, once your script has $correct_choice_num, you can compare that against the choice that the user selected.

无论如何，一旦你的脚本有$ correct_choice_num，你可以将它与用户选择的选项进行比较。

if ( $correct_choice_num == $_POST["$question_id"] )
{
  // This was the correct choice, do something
}

(The benefit of doing the scoring server-side is that the user can't cheat to find the correct choices by looking at the source of the HTML document)

（进行评分服务器端的好处是用户不能通过查看HTML文档的来源来欺骗以找到正确的选择）

This is just an example to get you started. Hope that helps!

这只是一个让你入门的例子。希望有所帮助！

#2

SELECT the table, get the options out of the question_choice via a MySQL query, make the rows variables and then echo them.

选择表，通过MySQL查询从question_choice中获取选项，创建行变量然后回显它们。