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

0

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.

    
asked by anonymous 06.01.2018 / 18:33

3 answers

1

The problem is in while($recordCurso = mysqli_fetch_array($resultCurso)) because resultset $resultCurso , after finishing the first student, will be in the last record. So when you get to the second student, you can not return the next one (it's already over).

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

<?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 @WellingtonRogati's answer, which is to join the two queries you make into one. The query would look something like this:

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

This 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 must 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 mount this aray you can do this:

<?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
}
?>  
    
07.01.2018 / 04:01
1

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

SELECT * FROM student a, course c, enrollment i         WHERE i.sector_alunoID = a.aluno_id           AND i.inscricao_cursoID = c.curso_id

NowtoshowinPHPIdonotliketoplayhereonmymachinenow,butIcanexplainhowyoucandoit:

1-Loopasyoudidwithwhile,butitdoesnotprinttherecordatthetimeitwasfetchedfromthequery.

2-Addanexamplevariable:$courses="". $ record ['course_name'];

3 - Check with if the loop has changed "aluno_id", if it prints the result on the screen or saves it in an array, it depends on your need.

In summary, although the query brings repeated records in the case of "Mary" 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 to have helped, anything is sent there.

    
07.01.2018 / 02:05
1

You can 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 the concatenated values through GROUP BY. If it does not have value, it returns NULL

     

LEFT JOIN = Merge the contents of the 3 tables

     

GROUP BY = You 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 at SQL Fiddle

The GROUP_BY is not related to LEFT JOIN , but to GROUP BY .

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

When you use GROUP BY in a column, you are telling MySQL to group all the records in that column, which are equal. Example: If you have two "john" in the nome column, it will group all records related to "john" in a row only and will keep only one of them in the show, with GROUP_CONCAT in a column, I'll report to the MySQL capture this remaining information, which would not be displayed, and concatenate everything into one information only.

  

More in-depth explanation on using (LEFT) JOIN link

    
07.01.2018 / 05:42