Databases
StackOverflow2013
Postsdbo
POGenerating HTML Form from database using PHP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerating HTML Form from database using PHP
    primarykey
    data
    text
    <p>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. </p> <p>Here is the database schema:</p> <pre><code>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; </code></pre> <p>Here is the code I have produced so far in my quiz.php script:</p> <pre><code>// 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 =&gt; $response) { $query = "UPDATE quiz_response SET response = '$response' WHERE response_id = '$response_id'"; mysqli_query($dbc, $query); } echo '&lt;p&gt;Your responses have been saved.&lt;/p&gt; } // 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 '&lt;form method="post" action="' . $_SERVER['PHP_SELF'] . '"&gt;'; echo '&lt;h2&gt;' . $page_title . '&lt;/h2&gt;'; $question_title = $responses[0]['question']; echo '&lt;label for="' . $responses[0][response_id'] . '"&gt;' . $responses[0]['question'] . '&lt;/label&gt;&lt;br /&gt;'; foreach ($responses as $response) { // Only start a new question if the question changes if ($question_title != $response['question']) { $question_title = $response['question']; echo '&lt;br /&gt;&lt;label for="' . $response['response_id'] . '"&gt;' . $response['question'] . '&lt;/label&gt;&lt;br /&gt;'; } // Display the choices // Choice 1 // Choice 2 // Choice 3 // Choice 4 } echo '&lt;br /&gt;&lt;br /&gt;'; echo '&lt;input type="submit" value="Grade Me!" name="submit" /&gt;'; echo '&lt;/form&gt;'; </code></pre> <p>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 <code>$responses</code> 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!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USoutis
    1. USAbundnce10
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGenerating HTML Form from database using PHP
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat exactly is the problem you are having? It looks like you are heading in the right direction with `$choices`.
      singulars
      1. POGenerating HTML Form from database using PHP
      1. USRusty Fausak
    2. COI'm having trouble differentiating between the 4 different choices. If I pull them out of the table and stick them into the $responses array I can't come up with a way to distinguish the difference. So, I think I need to alter their title before pushing them into the $responses array. Not sure exactly how I'll do that...
      singulars
      1. POGenerating HTML Form from database using PHP
      1. USAbundnce10
    3. COInstead of inventing your own way of showing schema, use standard SQL statements. In addition to following a well-known standard, your [example](http://sscce.org/) will be self-contained. Rather than interpolating values directly into statements, use prepared statements, which are safer and more performant when repeating a query. Don't use `<br/>` to lay out your form, use CSS (or a list element, which would be semantic). Is the missing single quote representative of your production code?
      singulars
      1. POGenerating HTML Form from database using PHP
      1. USoutis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload