Hello, I am new here and to php. I have built a gradebook that has 4 tables.
users
|id|name|
category
|id|name|
questions
|id|question|answer|categoryid|
answers
|id|userid|questionid|studentanswer|
The goal is to show each student as the first column and each question in a selected category as the first row.
I have been successful doing this by creating a for loop, looping through each user and then executing a query for all the answers matching that user.
I know this is bad practice and very slow, I am trying to execute this without querying inside of the loop.
Right now my code is:
$sql = 'SELECT users.id, users.name, answers.answer, answers.userid, answers.questionid, questions.id, questions.question, questions.answer, questions.categoryid
FROM users
INNER JOIN answers ON answers.userId = users.id
INNER JOIN questions ON questionId = questions.id
WHERE mid = 3';
$result = $pdo->query($sql);
foreach ($result as $row){
$grade[] = array(
'id' => $row['id'],
'name' => $row['firstName'],
'answer' => $row['studentAnswer'],
'correctAnswer' => $row['cans'],
'question' => $row['question']
);
This works well to create a table of each entry, but I need one row per student.
How can this be done with out creating many queries inside of a loop?