Random questions (id) in a record

Question

Hello, I’d like some help. And if you have any better ideas, you’re welcome to ;) Basically the idea is to search a question bank, randomly choose 5 of them and link them in the record of a candidate who will take this test.

<?php 
require_once("conecta.php");

$nome = "Maria";
$query = "SELECT * FROM questoes ORDER BY RAND() limit 5";

$sql = mysqli_query($conexao, $query);
while ($perguntas = mysqli_fetch_array($sql)) {
    echo $perguntas['id'];    
}

$query2 = "INSERT INTO avaliacao (nome, q1, q2, q3, q4, q5)
VALUES ('maria', '{$perguntas['id']}', '6', '7', '8', '9')";

return mysqli_query($conexao, $query2);
?>

I have this bench of questions:

I would like to take these random questions and insert them into Q1,Q2, etc

Basically a different quiz or vestibular for each candidate.

---

Running the query:

SELECT * FROM questoes ORDER BY RAND() limit 5;

it returns 5 id of the questions.. Ex.: 8 26 24 30 4

I’m not getting (I don’t know, right) take these 5 ids and make the insert into the registration of this new candidate.

Answer 1

First, if you need the primary key of the question, you do not need to select all the columns in the database, just select the one you will use. Also it is not necessary to make a repeat loop to go through all the selected issues, you can get a array direct with the function mysqli_fetch_all:

$result = mysqli_query($conexao, "SELECT id FROM questao ORDER BY RAND() LIMIT 5");
$questoes = mysqli_fetch_all($result, MYSQLI_ASSOC);
$ids = array_column($questoes, 'id');

Thus, $ids will be a array of the kind [8, 2, 5, 7, 3].

To pass these values by inserting in the other table, you can do:

if ($stmt = mysqli_prepare($conexao, "INSERT INTO avaliacao (nome, q0, q1, q2, q3, q4) VALUES (?, ?, ?, ?, ?, ?)")) {
    mysqli_stmt_bind_param($stmt, 'siiiii', $nome, ...$ids);
    mysqli_stmt_execute($stmt);
}

So, all of your ids array will pass to SQL, as well as the name of the student who will take the test.

Documentations:

• mysqli_fetch_all;
• array_column;
• mysqli_prepare;
• mysqli_stmt_bind_param;
• mysqli_stmt_execute;

Answer 2

From what I understand the problem is time to register the questions to the candidate, since the select is correct, you could do:

First go through the data coming from the database and place it in an array:

$q = [];
while ($perguntas = mysqli_fetch_array($sql)) {
    array_push($q, $perguntas);
}

Then put them in the evaluation table:

$query2 = "INSERT INTO `avaliacao` (`nome`, `q1`, `q2`, `q3`, `q4`, `q5`)
VALUES ('maria', '{$q[0]['id']}', '{$q[1]['id']}', '{$q[2]['id']}', '{$q[3]['id']}', '{$q[4]['id']}')";

So you take the 5 results from the bank, which will come randomly and put them in the evaluation table.