StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL INSERT query isn't updating column from a PHP web application
text
Body
copied!<p>I recently modified some code to allow for my quiz.php script to accommodate multiple quizzes as opposed to just one. To do this I sent along the <code>quiz_id</code> and <code>quiz_title</code> variables when the user clicks the link for the quiz and I receive them using $_GET. However, once the quiz form is submitted the quiz_id column no longer updates in the high_score table.</p> <p>Here is the code for quiz.php </p> <pre><code><?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'); ?> </code></pre> <p>Here is the code for quizlist.php</p> <pre><code> // 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); </code></pre> <p>My problem has to do with the piece of code </p> <pre><code>$query = "INSERT INTO high_score " . "VALUES ('0', '" . $_SESSION['user_id'] . "', '" . $quiz_id . "', '" . $correct_As . "', NOW())"; </code></pre> <p>It works when I substitute a number (i.e. 2) in the place of <code>$quiz_id</code>, but in order for the script to work for different quizzes I need to be able to use a different quiz_id for different quizzes. </p> <p>I'm having trouble taking the variable from quizlist.php using $_GET and then passing it along as a hidden value when the form is submitted. <strong>Am I doing something incorrect? Or am I missing something completely obvious?</strong> I'd appreciate any help! Thanks...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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