How do I use an posted input array in a mysql query?

Question

I'm working on a training course application, and this is a first for me so I'm hitting a bit of a snag. I have a form, the training assessment, which when submitted, sends an array of id's and selected answers to the php page for processing. My thoughts are to run a mysql query selecting a count of correct answers, then another to select a count of distinct questions, then divide the two to calculate the score. That part is simple enough, but my mind is melting trying to figure out the query for selecting the count of correct answers.

Here is the code for my form...

<form name="assessment" method="post" action="scripts/scoreAssessment.php">
  <ul class="assessment">
    <li class='question'>This is a question? 
      <ul style='list-style:none;'>
        <li><input name='trainingCourseAnswer[]' type='radio' value='A1'/>This is a multiple choice answer.</li>
        <li><input name='trainingCourseAnswer[]' type='radio' value='B1'/>This is a multiple choice answer.</li>
        <li><input name='trainingCourseAnswer[]' type='radio' value='C1'/>This is a multiple choice answer.</li>
        <li><input name='trainingCourseAnswer[]' type='radio' value='D1'/>This is a multiple choice answer.</li>
      </ul>
    </li>
    <li class='question'>This is a question? 
      <ul style='list-style:none;'>
        <li><input name='trainingCourseAnswer[]' type='radio' value='A2'/>This is a multiple choice answer.</li>
        <li><input name='trainingCourseAnswer[]' type='radio' value='B2'/>This is a multiple choice answer.</li>
        <li><input name='trainingCourseAnswer[]' type='radio' value='C2'/>This is a multiple choice answer.</li>
        <li><input name='trainingCourseAnswer[]' type='radio' value='D2'/>This is a multiple choice answer.</li>
      </ul>
    </li>
    <!--continue pattern for total number of questions-->
  </ul>
  <input name='submitAssessment' type='submit' value='Finish Test'/>
</form>

In scoreAssessment.php, I can split the value for the selected radio input answers using the PHP substr function to get two variables, $selectedAnswer and $answerID. The query that I need to run to select all correct answers would be as follows...

$selectedAnswer=substr($_POST['trainingCourseAnswer'], 0, 1);
$answerID=substr($_POST['trainingCourseAnswer'], 1);
$query="SELECT COUNT(correctAnswer) WHERE answerID='$answerID' AND correctAnswer='$selectedAnswer'"

The issue I have with that is that both $answerID and $selectedAnswer are arrays with multiple values...

Can anyone offer some guidance? I would greatly appreciate it!

"arrays with multiple values" — Since every radio button has the same name, and radio groups are determined by the name of the radio button, and you can only select one radio button in each radio group it seems more likely that you'll get an array with one value. — Quentin, Dec 26 '15 at 18:50
**Danger**: You are **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Dec 26 '15 at 18:51
as @Quentin mentioned..You need to make two form and two submit button..Or you need to use Ajax to handle with 1 submit button..In both situations you need two form. — , Dec 26 '15 at 19:16
@ErenArdahan — What? I didn't say that. You don't need multiple forms. You need different names for each group of radio buttons (which, as a bonus, eliminates the need for splitting on the value because you wouldn't need to encode the radio group in the name) — Quentin, Dec 26 '15 at 19:23
Taking both correct answer and the user answer from the user is not a good idea..You need different roles.One for adding questions and question choices..One for anwsering the question.. — , Dec 26 '15 at 19:44

score 1 · Accepted Answer · answered Dec 26 '15 at 20:21

First, you will notice that with the HTML provided, the user can only select one answer out of the 8 options. This is not what you want: you'll want to allow for one selection per question. This you solve by giving the radio controls different names: one unique name per question. And you don't need the array notation then either, as you will have never more than one answer per question:

<form name="assessment" method="post" action="scripts/scoreAssessment.php">
  <ul class="assessment">
    <li class='question'>This is a question? 
      <ul style='list-style:none;'>
        <li><input name='q1' type='radio' value='A'/>This is a multiple choice answer.</li>
        <li><input name='q1' type='radio' value='B'/>This is a multiple choice answer.</li>
        <li><input name='q1' type='radio' value='C'/>This is a multiple choice answer.</li>
        <li><input name='q1' type='radio' value='D'/>This is a multiple choice answer.</li>
      </ul>
    </li>
    <li class='question'>This is a question? 
      <ul style='list-style:none;'>
        <li><input name='q2' type='radio' value='A'/>This is a multiple choice answer.</li>
        <li><input name='q2' type='radio' value='B'/>This is a multiple choice answer.</li>
        <li><input name='q2' type='radio' value='C'/>This is a multiple choice answer.</li>
        <li><input name='q2' type='radio' value='D'/>This is a multiple choice answer.</li>
      </ul>
    </li>
    <!--continue pattern for total number of questions-->
  </ul>
  <input name='submitAssessment' type='submit' value='Finish Test'/>
</form>

As a consequence you don't have to add the question number to the ABCD value of the radio buttons (you could, but it is not necessary). Also I have chosen for a shorter name (q1, q2) as longer names make the URL long as well.

Then in PHP I would suggest to read all the correct answers from the database table in an array, and compare these with the user's answers one by one, keeping a count. I am here assuming mysqli_ functions, but it should be easy to convert this to the method of your preference (like PDO):

// Connection to database...
$con = mysqli_connect("my_host", "my_user", "my_password", "my_db");

// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Read all correct answers from the database:
$query = "SELECT answerID, correctAnswer FROM answerTable";
$result = mysqli_query($con, $sql);

// Fetch all records into an associative array
$correctAnswers = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Count correct answers provided by user (via POST)
$correctCount = 0;
foreach ($correctAnswers as $correctAnswer) {
    $answerID = $correctAnswer['answerID'];
    if (isset($_POST["q$answerID"])
            && $_POST["q$answerID"] == $correctAnswer['correctAnswer']) {
        $correctCount++;
    }
}
// Score is fraction between 0 and 1. Multiply by 100 for a percentage
$score = $correctCount / count($correctAnswers);

Note that this way you avoid the vulnerability you had for SQL injection, as the SQL statement above is not defined by the values submitted via POST.

How do I use an posted input array in a mysql query?

1 Answers1