How do I retrieve values from the next SQL result row in PHP?

Question

I'm trying to learn something new whilst stuck on lockdown. I'm trying to create a very basic multiple choice quiz page using my (very) limited knowledge of PHP/Javascript...

So I have a SQL database containing quiz questions. The main fields are the question, correct answer and then three incorrect answers. Currently the database has just one row.

<?php
$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM quiz_db";
$result = $conn->query($sql);
$row = $result->fetch_assoc();

$conn->close();
?>

So the question, the correct answer, the three incorrect answers and anything else in the row are stored to $row.

Next I'm taking all four possible answers into an array and then shuffling the order:

<?php 
$answerslist = array($row["answercorrect"], $row["answerincorrect1"], $row["answerincorrect2"], $row["answerincorrect3"]);
shuffle($answerslist);
?>

Then I'm putting the four possible answers on a set of buttons in their now randomized order:

<div class="row">
    <div class="col-sm-3">
        <button id = "answerA" onclick="clickA()" class="answer"><?php echo $answerslist[0]; ?></button>
    </div>
    <div class="col-sm-3">
        <button id="answerB" onclick="clickB()" class="answer"><?php echo $answerslist[1]; ?></button>
    </div>
    <div class="col-sm-3">
        <button id = "answerC" onclick="clickC()" class="answer"><?php echo $answerslist[2]; ?></button>
    </div>
    <div class="col-sm-3">
        <button id = "answerD" onclick="clickD()"  class="answer"><?php echo $answerslist[3]; ?></button>
    </div>
</div>

You'll see I've assigned an onclick event to each button. This is as follows:

function clickA() {

var clickedAnswerA = "<?php echo $answerslist[0]; ?>"; // Get the value of the clicked answer

var correctAnswer = "<?php echo $row["answercorrect"] ?>"; // Get the value of the correct answer


if (clickedAnswerA == correctAnswer) {
     document.getElementById("result").innerHTML = "CORRECT!"; 
     document.getElementById("result").style.backgroundColor = "#32CD32";   
} else {
    document.getElementById("result").innerHTML = "WRONG!";
    document.getElementById("result").style.backgroundColor = "#CC0000";
}
}

There are four of the above scripts, one for each button. Essentially I wanted it to say, if the clicked answer = the correct answer, make the result CORRECT and green, otherwise WRONG! and red

And to my surprise, this all works as expected!

For the next stage, after the above, I wanted it to go to the next question in the resultset (i.e. the next question in $row). But I'm getting stuck. Currently there's only one question in the DB, but if I add another one, I'm not sure how I make the PHP essentially refresh. Any advice or pointers would be greatly appreciated.

Furthermore, if anyone can recommend any improvements to what I've done so far, perhaps for ease or efficiency, that would be great, thank you!

Answer 1

1. Don't keep everything in one row. Have relation between question and answers:

question
  id | title | ...
   1 | Question 1 | ...
   2 | Question 2 | ...

answer
 id | question_id | content | is_correct
  1 | 1           | Foo     | 1
  2 | 1           | Bar     | 0
  ...

2. Do query for data:

$question = $db->prepare('SELECT * FROM question WHERE id = :id')->queryRow([':id' => $id]);
$answers = $db->prepare('SELECT id, content, is_correct FROM answer WHERE question_id = :id')->queryAll([':id' => $id]);

that way you can have as much answers as you need per question.

3. Bind event for your elements, instead of onClick="" with each function doing the same.

4. var clickedAnswerA = "<?php echo $answerslist[0]; ?>"; will bring more problems than you see by now. Try not to mix JS and PHP code outputs as JS does not see any PHP, just it's output (PHP first outputs code and only later HTML and JS is executed)

5. Use single PHP loop to output all answers instead of writing repetitive code.