Display data in the same row

Question

Hello! I have a table setting another table and I’m trying to display the same content set in the same row.

I did a while but the result is getting like this:

André Cargo: Operário
André Cargo: Auxiliar
Julia Cargo: Secretária
Julia Cargo: Auxiliar

I want it to stay that way:

André Cargo: Operário e Auxiliar
Julia Cargo: Secretária e Auxiliar

$sql = "select * from funcionarios join cargos on funcionarios.id = cargos.id_funcionario";

$result = mysqli_query($con, $sql);

 while ($row = mysqli_fetch_array($result)) {
  ?>

    <div class="card-stacked">
    <div class="card-content">
      <strong><?php echo $row['nome']; ?></strong>
      <p>Cargo: <?php echo $row['cargo']; ?></p>
  </div>
  </div>


 <?php } ?>

Answer 1

You can use the function GROUP_CONCAT together with the GROUP BY in the MySQL:

<?php
    $sql = "
        select funcionarios.nome, group_concat(cargos.cargo) as cargo from funcionarios
        join cargos on funcionarios.id = cargos.id_funcionario
        group by funcionarios.id
    ";

    $result = mysqli_query($con, $sql);

    while ($row = mysqli_fetch_array($result)) {
        ?>
            <div class="card-stacked">
                <div class="card-content">
                    <strong><?php echo $row['nome']; ?></strong>
                    <p>Cargo: <?php echo $row['cargo']; ?></p>
                </div>
            </div>    
        <?php
    }
?>

EDIT:

To limit the amount of jobs per employee, you will have to change your SELECT for that reason:

select funcionarios.nome, (
    select group_concat(cargo) from cargos
    where id_funcionario = funcionarios.id
    limit 2
) as cargo
from funcionarios