I would like to display in the table the information of the registered student and also of the courses that he is enrolled in.
Final result you'd like displayed:
Nome Aluno | Telefone Aluno | Cursos
---------------------------------------------------
João | 12988776655 | Algoritmos
Maria | 12988776655 | Algortimos, IA, UX
Carlos | 12988776655 | UX, IA
I have Three Tables: Student, Courses and Registration.
Student Table: Record student information! The structure is:
aluno_id | aluno_nome | aluno_telefone
----------------------------------------
1 | João | 12988776655
2 | Maria | 12988776655
3 | Carlos | 12988776655
Table Courses: Register the course information! The structure is:
curso_id | curso_nome | curso_dataInicio | curso_dataFim
-------------------------------------------------------
1 | Algoritmos | 12/01/2018 | 12/06/2018
2 | IA | 12/01/2018 | 12/06/2018
3 | UX | 12/01/2018 | 12/06/2018
Enrollment table: Here is where I link the courses that the student is enrolled, where inscricao_alunoID and inscricao_cursoID are foreign keys linked to the id of both the student table and the course table, ::
inscricao_aluno_curso_id | inscricao_alunoID | inscricao_cursoID
1 | 1 | 1
2 | 2 | 1
3 | 2 | 2
4 | 2 | 3
5 | 3 | 3
6 | 3 | 2
Given the information above, I would like to display the student's courses in the same cell in the table! For this I made this code snippet for displaying information:
<table>
<thead>
<tr>
<th>Nome Aluno</th>
<th>Nome Telefone</th>
<th>Cursos</th>
</tr>
</thead>
<tbody>
<?php while($record = mysqli_fetch_array($result)){ ?>
<tr>
<td><?php echo $record['nome_aluno']; ?></td>
<td><?php echo $record['telefone_aluno']; ?></td>
<td><?php while($recordCurso = mysqli_fetch_array($resultCurso)){
if($recordCurso['inscricao_alunoID'] == $record['aluno_id']){
echo $recordCurso['nome_curso'].", ";
}
}
?></td>
</tr>
<?php } ?>
</tbody>
</table>
Where, within the while it would pass through each student index, and if the current index were equal to the index of my second query, then it would display the course name! p>
My queries that are stored in the variables $ result and $ resultCourse are:
$result = mysqli_query($con, "SELECT * FROM aluno");
$resultCurso = mysqli_query(
$con, " SELECT c.nome_curso, i.inscricao_alunoID
FROM inscricao i
INNER JOIN curso c ON i.inscricao_cursoID = c.curso_id "
);
In this way I have made the result just right for the first student! Next students are getting all white!
Note: If there is another way to do this, I'm also accepting better suggestions.