Generating HTML Form from database using PHP

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.

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:

// 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.respons开发者_如何转开发e, 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!

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:

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:

<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.

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

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)

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

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