php – Do not repeat data when doing a SELECT on MySql

Question:

What I want is this. I'm working on a system, which registers an employee and the days he worked in the month. So far so good, but it turns out that in some cases, the employee keeps 2 or more records in the same month, for example, I registered, between May 21st and 25th, and May 28th and 30th. Right on my table there are the following records:

1 - Funcionario1 - 5 (dias) - 5 (mês de maio)
2 - Funcionario1 - 3 (dias) - 5 (mês de maio)

Okay, now what I need is to calculate the total number of days in the middle of May that this employee worked. I can more or less with this code:

for($i=0; $i<$mesatual; $i++) {
            echo "<h2>Plantões / ".$meses[$i]."</h2><br><br>";

            $query = "SELECT diasplantao.*, funcionarios.nome, funcionarios.atribuicao FROM diasplantao "
                    . "INNER JOIN funcionarios ON (diasplantao.funcionario_id = funcionarios.funcionario_id) WHERE (diasplantao.mes = $i+1) "
                    . "ORDER BY funcionarios.atribuicao DESC";

            $resultado = mysql_query($query);

            while($mostrar=mysql_fetch_array($resultado)) {
                echo "<b>Nome:</b> ".$mostrar['nome']." -- ";
                echo "<b>Atribuição:</b> ".$mostrar['atribuicao']." -- ";
                echo "<b>Mês:</b> ".$mostrar['mes']." -- ";
                echo "<b>Dias:</b> ".$mostrar['dias']."<br><br>";

            }
        }  
    ?>

The code displays the month, and below the days employees worked that month. But he returns it to me, in March for example.

Nome: George Cruijff Sales da Costa -- Atribuição: Analista -- Mês: 3 -- Dias: 12

Nome: George Cruijff Sales da Costa -- Atribuição: Analista -- Mês: 3 -- Dias: 10

How can I not repeat the name, and what is the best way to calculate the total number of days? I was thinking of just putting a helper variable and adding it, but the name will keep repeating.

Does anyone shed some light on the best way to do this?

Answer:

You must use SUM to sum the days and GROUP to bring the grouped rows

SELECT diasplantao.mes, 
       SUM(diasplantao.dias) as dias,
       funcionarios.id,
       funcionarios.nome, 
       funcionarios.atribuicao 
FROM 
    diasplantao 
    INNER JOIN funcionarios ON (diasplantao.funcionario_id = funcionarios.funcionario_id) 
WHERE 
    (diasplantao.mes = $i+1)
GROUP BY
    funcionarios.id, diasplantao.mes, funcionarios.nome, funcionarios.atribuicao 
ORDER BY 
    funcionarios.atribuicao DESC
Post Views: 0
Scroll to Top