PHP lê linhas com base no mês

Question

Esta é a aparência da minha tabela (este é apenas um exemplo, minha tabela contém uma grande quantidade de linhas):

ID    |          date           |     transfer    |   prov   |
--------------------------------------------------------------
1     |    2020-08-21 14:13:14  |    2020-08-31   |    100   |
2     |    2020-08-21 14:13:14  |    2020-08-31   |    200   |
3     |    2020-08-21 14:13:11  |    2020-07-29   |    100   |
4     |    2020-08-20 13:05:59  |    2020-08-29   |     50   |

Este é o meu código:

<table>
          <thead>
              <tr>
                  <th>Januar</th>
                  <th>Februar</th>
                  <th>Mars</th>
                  <th>April</th>
                  <th>Mai</th>
                  <th>Juni</th>
                  <th>Juli</th>
                  <th>August</th>
                  <th>September</th>
                  <th>Oktober</th>
                  <th>November</th>
                  <th>Desember</th>
                </tr>
                  
                  <?php
                  $res = $link -> query("SELECT SUM(prov) as sum 
                        FROM sales 
                        WHERE YEAR(transfer) = YEAR(CURRENT_DATE())
                        GROUP BY MONTH(transfer)
                        ORDER BY MONTH(transfer)"); 
                        
                        echo '<tr>';
                        while( $row = $res->fetch_assoc() ) {
                            echo "<td>$row[sum]</td>";
                        }
                        echo '</tr>';
                  
  ?>
  </thead>
  </table>

Portanto, isso deve retornar basicamente 250 para o mês de agosto, 100 para julho e 0 para o restante dos meses. Mas ao usar isso, apenas a célula Januar e Februar exibe o valor, e as outras células não estão lá. O que estou fazendo errado?

É assim que parece ao vivo: https://ibb.co/8bd6JWP (o 12750 tem data de transferência em agosto, não janeiro, então todas as células devem ter valor 0, mas agosto deve ter 12.750)

Answer 1

Você não está verificando os valores retornados. Você precisa selectdo mês, além dos valores agregados. Você pode então verificar se o mês em que você está é o mês em que você tem valores. Gostaria de selecionar todos os seus valores primeiro, colocá-los em uma matriz, em seguida, iterar ao longo dos meses e verificar a matriz para ver se ela tem valores.

Ideia aproximada:

$monthdata = array(7 => 10, 8 => 22 ); for($x =1; $x <= date('n'); $x++){
    echo date('F', strtotime('2020-' . $x . '-01')) . PHP_EOL; if(!empty($monthdata[$x])) { echo $monthdata[$x];
    } else {
        echo 0;
    }
    echo PHP_EOL;
}

e altere a consulta para:

SELECT SUM(prov) as sum, MONTH(transfer) as month

para que você possa usar $row['month']. O $monthdatadeve ser construído como:

$monthdata[$row['month']] = $row['sum'];

durante a iteração do select.

Answer 2

Lembre-se de que o SQL lida com conjuntos de linhas de dados. Seus dados de amostra e consulta geram um conjunto de duas linhas de dados. E seu código php lê esse conjunto e gera uma <td>nnn</td>coluna de tabela HTML para cada linha em seu conjunto. Existem apenas dois deles. Portanto, apenas duas colunas em sua tabela HTML. Isso é PORQUE seu código não fazer o que quiser. Este db-fiddle.https://www.db-fiddle.com/f/nfv5wDnBWWgP8dq8E26QtZ/0 demonstra.

A pergunta mais interessante: como você faz isso funcionar? É surpreendentemente complicado até você se acostumar. Existem várias soluções. Meu favorito é usar uma consulta que gere o conjunto de doze linhas que você deseja e, em seguida, LEFT JOINs seus dados reais para esse conjunto.

MONTH(sometdate)gera inteiros no intervalo [1-12]. Portanto, se você puder usar um conjunto desses doze inteiros em sua consulta, terá o que deseja.

Experimente isso.

                    SELECT SUM(prov) as sum
                    FROM seq_1_to_12
                    LEFT JOIN sales ON seq_1_to_12.seq = MONTH(transfer)
                                   AND YEAR(transfer) = YEAR(CURRENT_DATE())
                    GROUP BY seq_1_to_12.seq
                    ORDER BY seq_1_to_12.seq

Se você usa a variante MariaDB do MySQL, essa tabela misteriosa seq_1_to_12 já está definida para você. Hooray. Caso contrário, você mesmo precisa defini-lo em sua consulta. Uma boa maneira é esta.https://www.db-fiddle.com/f/nfv5wDnBWWgP8dq8E26QtZ/1

    SELECT seq_1_to_12.seq, IFNULL(SUM(prov),0) as sum
    FROM (
           SELECT 1 AS seq UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
           SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6 UNION ALL
           SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL
           SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         ) seq_1_to_12
    LEFT JOIN sales ON seq_1_to_12.seq = MONTH(transfer)
                   AND YEAR(transfer) = YEAR(CURRENT_DATE())
    GROUP BY seq_1_to_12.seq
    ORDER BY seq_1_to_12.seq 

Observe algumas coisas sobre isso.

• IFNULL(SUM(prov,0))faz com que o conjunto de resultados mostre zeros para os dados dos meses ausentes em vez de nulos. Isso torna seu relatório um pouco mais claro.
• EXPANDED Você precisa inserirYEAR(transfer) = YEAR(CURRENT_DATE()) a ONcláusula de seu em LEFT JOINvez de uma WHEREcláusula. Por quê? Quando uma cláusula WHERE (ou cláusula ORDER BY ou GROUP BY) menciona uma coluna em uma tabela LEFT JOINed, ela converte LEFT JOIN em um JOIN interno comum. As operações JOIN internas suprimem linhas do lado direito do JOIN; LEFT JOINs preservam-nos.
• Você deve GROUP BYe ORDER BYseus valores de sequência.

Eu escrevi um artigo sobre isso há algum tempo.

Answer 3

Experimente algo assim (uma solução um pouco mais limpa e compreensível):

<?php
   $months = array( 1 => 'Januar', 2 => 'Februar', 3 => 'Mars', 4 => 'April', 5 => 'Mai', 6 => 'Juni', 7 => 'Juli', 8 => 'August', 9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'Desember'); ?> <table> <thead> <tr> <?php foreach($months as $month) echo "<th>$month</th>";
                  ?>
              </tr>                    
                  <?php
                        $res = $link -> query("SELECT MONTH(transfer) as month, SUM(prov) as sum 
                        FROM sales 
                        WHERE YEAR(transfer) = YEAR(CURRENT_DATE())
                        GROUP BY MONTH(transfer)
                        ORDER BY MONTH(transfer)"); // you don't need to order the results anymore
                        $results = array(); while($row = $res->fetch_assoc()){ $results[$row['month']] = $row['sum'];
                        }                        
                        echo '<tr>';
                        foreach($months as $m_id => $month){ if(isset($results[$m_id])) echo "<td>".$results[$m_id]."</td>";
                            else
                                echo "<td>0</td>";
                        }
                        echo '</tr>';
                   ?>
          </thead>
  </table>