Help - Logic Formulation - SQL, PHP, HTML

Asked

Viewed 31 times

1

I have a table in the bank with team registration, where each line corresponds to an employee and their respective team. For example,

id_staff, employee

1, Mario

1, Sergio

2, John

2, Joseph

I want to bring the data on the screen in a table so that each row corresponds to each team, and the staff column is a concatenation of the names. That is to say:

id_staff, staff

1, Mario, Sérgio

2, John, Joseph

What I have so far:

 <table id="myDatatable" class="table table-striped table-bordered" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                          <th>Nome Equipe</th>
                          <th>Funcionários</th>
                          <th>Ações</th>
                        </tr>
                      </thead>

                  <?php
                    

                     $id_agencia = $_SESSION['id_agencia'];
                   


              $pdo = Conexao::getInstance();



                $dados = $pdo->prepare("SELECT DISTINCT(nome_equipe),id_equipe,id_eletricista FROM quadro WHERE id_agencia=:id AND status=1 GROUP BY nome_equipe");
                $dados->bindParam(':id',$id_agencia, PDO::PARAM_INT);

                $dados->execute();

                        if($dados->rowCount()>=1){

                            echo '<tbody>';
                             while($table = $dados->fetch(PDO::FETCH_OBJ)){
                          
                                                
                              echo '<tr>';
                              
                              
                              $id_equipe=$table->id_equipe;
                              
                              echo'<td>'.$table->nome_equipe.'</td>';
                              echo'<td>'.$table->id_eletricista.'</td>';
                              
                              echo '<td><a class="btn btn-success" href="editando_equipe.php?id='.$id_equipe.'"><i class="fa fa-edit"></i> Editar</a>

                              <a class="btn btn-warning" href="Quadro/desativar_equipe.php?id='.$id_equipe.'&Acesso=0" title="Desativar equipe"><i class="fa fa-minus-circle"></i></a>


                              </td>';
                                                                               }
                              }
                                                                            


                                                    
                            
                                      
                        echo '</tr>';
                                            
                      echo '</tbody>';

                   
                    

                      ?>
                    </table>
                  </div>

                </div>
              </div>
                </div><!-- div da row anterior class="col-md-6 col-xs-12"-->
              </div>

Who can give me tips on how to achieve this goal, I will thank you very much! Any help is welcome!

php mysql sql
  • 1

    In short: SELECT id_equipe, GROUP_CONCAT( funcionario ) AS funcionarios FROM tabela GROUP BY id_equipe - Adjust to your logic. The important thing is that the GROUP BY either for the repeating value, and the GROUP_CONCAT applied in the comma-separated field. More details on the blue closure links.

  • 1

    Thank you so much for your help!

1 answer

0


Using the GROUP_CONCAT, follows below as you can make your query:

SELECT nome_equipe, id_equipe, GROUP_CONCAT(funcionario) funcionarios
FROM quadro
WHERE id_agencia=:id AND status=1
GROUP BY id_equipe

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.