Selecting random questions from MySQL database; "correct answer" messed up

Question

I am building a simple quiz program, using PHP and MYSQL.

The quiz is designed to display one question at a time; the questions are multiple-choice (each question has 4 possible answers)

If the player picks the correct one, he proceeds to the next question; if he picks the wrong one, the quiz comes to an end.

At first, I designed the quiz as follows :

(1) created a database table, which contains 1500 questions. The table has the following columns :

ID (primary key)
QUESTION (the question itself)
A1 (first answer)
A2 (second answer)
A3 (third answer)
A4 (fourth answer)
CORRECT  (the correct answer --- which is one of the above A1 to A4)

(2) Then, my PHP code was designed to pick questions, one by one, in SEQUENTIAL order (using the ID as reference).

So, when the user starts playing, he begins with Question 1, then Question 2, etc, etc.

(3) To make it more interesting, I added an extra column to the database (begin_id), which has a default value of "1", when a user registers as a player on my quiz-website. Each time the user answers a question, this column is updated with that question's "ID". Meaning, it records the LAST question the user answered (whether wrongly or correctly). So that : the next time the user logs on, and plays the quiz, he does not begin from Question-1. Instead, he begins from the NEXT question in the list. (Meaning, the user never sees the same question twice!)

Here is the code :

// Query database
$get_question = "SELECT * from questions_table where id = $begin_id";
$result_get_question = mysqli_query($conn, $get_question);
$row_get_question = mysqli_fetch_array($result_get_question);

// Assign database response to variables
$question = $row_get_question['question'];
$a1 = $row_get_question['a1'];
$a2 = $row_get_question['a2'];
$a3 = $row_get_question['a3'];
$a4 = $row_get_question['a4'];
$correct = $row_get_question['correct'];

// Check user answer
if (isset($_POST['submit'])) {   
    $selected_radio = $_POST['response'];

    if ($selected_radio == $correct)
        echo "THAT ANSWER IS CORRECT";
    else
        echo "THAT ANSWER IS WRONG!";
}

And, everything worked beautifully !!

Now, I decided to make it even more "efficient"; instead of selecting questions sequentially, I decided to simply modify my PHP-SELECT statement, to select questions RANDOMLY.

This was easy, especially as there are so many online tutorials on how this is done.

Here is the code I used (the simplest one) :

SELECT * from questions_table order by rand() limit 1;

This works, as far as selecting random questions goes. However, something is wrong : no matter what answer the user selects, it is always the WRONG answer!!

For some reason, selecting random questions from the database has messed up the "Correct answer / Wrong answer" logic in my PHP code.

No idea what could be wrong.

UPDATE

   $get_question = "SELECT * from questions_table where id = $begin_id";

   $result_get_question = mysqli_query($conn,$get_question);

   $row_get_question = mysqli_fetch_array($result_get_question);  

   $question_id = $row_get_question['question_id'];

   $question = $row_get_question['question'];

   $a1 = $row_get_question['a1'];

   $a2 = $row_get_question['a2'];

   $a3 = $row_get_question['a3'];

   $a4 = $row_get_question['a4'];

   $correct = $row_get_question['correct'];

  <br>

  <form method ="post" action ="">

  <input type="radio" name="response" value="<?=$a1?>"><?=$a1?><br>

  <input type="radio" name="response" value="<?=$a2?>"><?=$a2?><br>

  <input type="radio" name="response" value="<?=$a3?>"><?=$a3?><br>

  <input type="radio" name="response" value="<?=$a4?>"><?=$a4?><br>
          <input type="hidden" name="question_id" value="<?= echo $question_id?>" 
    />        <br>

  <Input type = "submit" Name = "submit" Value = "Answer">        </form>

Solution

When you ask your question to the user, a question is randomly selected from the database.

Then, the user submits your form, another question is randomly selected, and that's the question you are using to check the answer, instead of the question you asked to the user.

You need to add an hidden input in your form, that contains the question id

<input type="hidden" name="question_id" value="<?php echo $question_id ?>" />

And then when you check the answer, be sure to fetch the right question from the database

The code would look like this

<?php

// Check user answer for previous question
if (isset($_POST['submit'])) {   
    // Retrieve the id of the question you asked
    $previous_question_id = (int) $_POST['question_id']; // cast to integer to prevent sql injection.

    // Query database
    $get_question = "SELECT * from questions_table where id = $previous_question_id";
    $result_get_question = mysqli_query($conn, $get_question);
    $row_get_question = mysqli_fetch_array($result_get_question);

    // Assign database response to variables
    $correct = $row_get_question['correct'];
    $selected_radio = $_POST['response'];

    if ($selected_radio == $correct)
        echo "THAT ANSWER IS CORRECT";
    else
        echo "THAT ANSWER IS WRONG!";
}


// Load new question to ask to the user
$get_question = "SELECT * from questions_table order by rand() limit 1";
$result_get_question = mysqli_query($conn,$get_question);
$row_get_question = mysqli_fetch_array($result_get_question);  

// assign thing we want to print in the template to variable
$question_id = $row_get_question['question_id'];
$question = $row_get_question['question'];
$a1 = $row_get_question['a1'];
$a2 = $row_get_question['a2'];
$a3 = $row_get_question['a3'];
$a4 = $row_get_question['a4'];

?>

<PASTE YOUR TEMPLATE HERE>

Comments

[klaar]

Well spotted, haven't thought that he fetches the data AGAIN after processing the form. With non-experienced people you should be expecting anything.

[phpnewbie2015]

@Eloims : thanks for your response. I updated my original post to show my form. I'm not quite sure how to implement the "question_id" logic in the SQL select statement, seeing as it's selected randomly.

[Kez]

You would need to query the database from within the POST if statement. e.g. if (isset($_POST['submit'])) { //query database for correct answer where id = $questionId }

[phpnewbie2015]

@Eloims : Thanks a bunch Eloims. It worked beautifully. However, I have a question : what is the purpose of this line : <input type="hidden" name="question_id" value="<?php echo $question_id ?>">; also, if it's an "echo" statement, shouldn't it end with a semi-colon?

[phpnewbie2015]

Should it be written as such : <input type="hidden" name="question_id" value="<?= $question_id ?>">

[Eloims]

It can be written as both. You are using short tags which does not always work, but is convenient for cleaner code. here is a related post programmers.stackexchange.com/questions/151661/…

[phpnewbie2015]

@Eloims. Thanks for explaining this to me in such detail. I've marked your answer as the "correct" one. But, I'm still curious about the need for that statement in general (you didn't reply to that, I guess you missed it :) Why is this line needed in my form : <input type="hidden" name="question_id" value="<?=$question_id?>">

[phpnewbie2015]

It's a "hidden" attribute, and the "question_id" has already been obtained from the database. So, why do we need this attribute itself in the form? Thanks.

[Eloims]

The $previous_question_id is not obtained from the database, it's obtained from the form. You need the hidden input because it's a way to remember which question you asked to the user, so that you can check the answer (you could have used the user session). Your previous code version did not need that because your asked the questions always in the same order and used $begin_id to retrieve last question and $beginId + 1 for next question (i suppose).

[Eloims]

add "echo $previous_question_id" and "echo $question_id" to your script and play with it until you get it :)

[phpnewbie2015]

Haha :) I get it now. Thanks again