I have this table(demo):
questions
----------
question_id | question | choice1 | choice2 | choice3 | choice3 | choice4 | answer
---------------------------------------------------------------------------------------
1 | what is.. | apple | orange | grapes | pinea | blah | 2
2 | what is.. | apple | orange | grapes | pinea | blah | 4
3 | what is.. | apple | orange | grapes | pinea | blah | 1
4 | what is.. | apple | orange | grapes | pinea | blah | 2
5 | what is.. | apple | orange | grapes | pinea | blah | 3
6 | what is.. | apple | orange | grapes | pinea | blah | 4
A set of 5 random questions will be displayed to user and then user selects the answer. So, when user submits the form(answersheet), how to check if the question_id
& answer
that user had submitted is correct ?
What I do now is, loop through each question_id
in the submitted answersheet, then do a query to check if the answer is correct like this(psuedocode):
$wrong_questions = $total_questions_in_answersheet;
foreach($question in $answersheet)
{
$c = SELECT COUNT(*) FROM `questions` WHERE `question_id` = $question_id AND `answer` = $answer;
if($c > 0)
$wrong_questions--;
}
if($wrong_questions > 0)
echo 'FAILED';
else
echo 'WELL DONE';
Is it possible to do checking of answers in a single query? If so, would it better to do it in that way?
$where = implode(' OR ', array_map(function($question) {
return "(question_id = $question[question_id] AND answer != $question[answer])";
}, $answersheet));
$sql = "SELECT COUNT(*) AS wrong_questions FROM questions WHERE $where";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
if ($row['wrong_questions'] > 0) {
echo 'FAILED';
} else {
echo 'WELL DONE';
}
Running query in the loop is generally considered to be a bad practice; so, in fact, I would make only two things: First, SELECT
all the correct answer linked to id
, in associative array, like $answers['id']['answer']
; Second, compare submitted answers with $answers
array by id
.