Display more than one information in the same table with PHP and MYSQL

Question

I would like to display in the table the information of the enrolled student and also the courses that he is enrolled.

Final result that I would like to be shown:

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.

Table Student: Register 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

Registration table: Here is where I connect the courses that the student is registered, where inscribed and inscribed are foreign keys linked to the id of both the student table and the course table, so it looks like this::

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 above information, 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, inside the while it would go through each student’s index and, if the current index was equal to that within the index of my second query, then it would display the course name!

My queries that are stored in the $result and $resultCurso variables 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 "
);

This way I made the result appears right only for the first student! The next students are all going white!

Note: If there is another way to do this that I wish I am also accepting better suggestions.

Answer 1

Wesley, I reproduced the tables you mentioned in my environment and with the query below I bring the records the way you can show via PHP as you want :

SELECT * FROM student to, course c, enrollment i WHERE i.inscricao_alunoID = a.aluno_id AND i.inscrip_cursoID = c.curso_id

Now to show in PHP I can not play here on my machine now, but I can explain how to do:

1 - Make a loop like you did with while even, only it does not print the record at the time it was brought from the query.

2 - Accumulate in an example variable: $cursos .= " " . $record['curso_name'];

3 - Check with "if" if the loop has changed from "aluno_id", if yes print on the screen the result or save in an array, depending on your need.

In short, although the query brings repeated records in the case of "Maria" what matters in accumulating is the name of the course because it is the information that varies, remember that in this accumulation we use concatenation.

I hope I’ve helped, anything can send.

Answer 2

The problem is in the while($recordCurso = mysqli_fetch_array($resultCurso)) for the resultset $resultCurso, after finishing the first student, will be on the last record. So when you arrive at the second student, there is no way to return the next (because it is already at the end).

One possible solution is to use the function mysqli_data_seek() to return to the start of the resultset (position 0). This will allow your structure to function correctly. Applying is thus:

<?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'].", ";
               }
         }
         //logo após o while que lista os cursos, 
         //faça o curso voltar para o inicio do resultset
         mysqli_data_seek($resultCurso, 0);
         ?>
        </td>
    </tr>
<?php } ?>

A more complete solution would be to use the query presented in the @Wellingtonrogati response, which is to join the two queries you make into one. The consultation would be something similar to this:

SELECT * FROM aluno, curso, inscricao WHERE 
inscricao.inscricao_alunoID = aluno.aluno_id AND  
inscricao.inscricao_cursoID = curso.curso_id

That would return:

aluno_id | aluno_nome | aluno_telefone | curso_id | curso_nome | curso_dataInicio | curso_dataFim | inscricao_aluno_curso_id | inscricao_alunoID | inscricao_cursoID

From here you could assemble an array (before mounting the table in html), to organize your data. The array should have this structure:

[
    'aluno_id' => [
         'aluno' => [
             'aluno_nome' => 'valor', 'aluno_telefone' => 'valor'
         ], 
         'cursos' => [
             ['curso_id' => valor, 'curso_nome' => 'valor'],
             ['curso_id' => valor, 'curso_nome' => 'valor']
         ]
     ],
     'aluno_id' => [
         'aluno' => [
             'aluno_nome' => 'valor', 'aluno_telefone' => 'valor'
         ], 
         'cursos' => [
             ['curso_id' => valor, 'curso_nome' => 'valor'],
             ['curso_id' => valor, 'curso_nome' => 'valor']
         ]
     ]
]

to ride this Aray you can do so:

<?php
$resultado = mysqli_query($con, 'SELECT * FROM aluno, curso, inscricao WHERE 
inscricao.inscricao_alunoID = aluno.aluno_id AND  
inscricao.inscricao_cursoID = curso.curso_id');

$novoArray = [];

//a partir desse array (que tem varias repetições) criaremos o array    
//descrito acima. A estrutura de $novoArray segue a ideia de
// [conjuntos][2], onde não há elementos repetidos
while($linha = mysqli_fetch_assoc($resultado);){
    //a cada execução do while, se houver mais de uma tupla com 
    //informaçoes do aluno, a ultima sobrescrevera a anterior 
    $novoArray['aluno_id']['aluno'] = [
         'aluno_nome' => $linha['aluno_nome'],
         'aluno_telefone' => $linha['aluno_telefone'],
    ];
    //no caso dos cursos não haverá sobrescrita, pois são informações
    // distintas. Basicamente, para cada tupla que
    $novoArray['aluno_id']['cursos'][] = [
          'curso_id' => $linha['curso_id'],
          'curso_nome' => $linha['curso_nome']
    ]
}

// e imprima a tabela
foreach($novoArray as aluno){
?>
    <tr>
        <td><?php echo $aluno['aluno']['aluno_nome']; ?></td>
        <td><?php echo $aluno['aluno']['aluno_telefone']; ?></td>

        <td><?php 
        foreach($aluno['cursos'] as $curso){
            echo $curso['curso_nome'].", ";
        }
        ?>
        </td>
</tr>
<?php
}
?>  

Answer 3

It is possible to bring the result as you want, using only the Mysql query.

You can run the following query:

SELECT
  a.`nome` AS aluno_nome,
  a.`telefone` AS aluno_telefone,
  GROUP_CONCAT(c.`curso`) AS cursos
FROM
  `aluno` a
LEFT JOIN
  `aluno_has_cursos` ac ON(ac.aluno_id = a.id)
LEFT JOIN
  `cursos` c ON(ac.curso_id = c.id)
GROUP BY
  a.`nome`;

Explaining the code

GROUP_CONCAT = This function returns all values concatenated through GROUP BY. If it has no value, it returns NULL

LEFT JOIN = Will merge the contents of the 3 tables

GROUP BY = Will group the values. This will avoid bringing several "Maria" with equal information (only changing the name of the course).

You can see the code working on the website SQL Fiddle

The GROUP_BY it is not related to the LEFT JOIN, but rather with the GROUP BY.

The LEFT JOIN it will merge the contents of various tables by comparing the data in the ON, with this you can return several identical data with certain different columns.

When you use the GROUP BY in a column, you are informing for the MySQL group all records, of that particular column, which are equal. Example: if you have two "John" in the column nome, it will group all the records related to "John" in one line and will keep only one of them the show, with the GROUP_CONCAT in a column, I inform to the MySQL capture this remaining information, which would not be displayed, and concatenate everything into one information.

More detailed explanation about the use of (LEFT) JOIN /a/6448/99718