CODEWITHSUNDEEP
phphtmlmysqldatetime
Anelka
Anelka

Reputation: 11

PHP read rows based on month

This is how my table looks like (this is just an example, my table contains a huge amount of rows):

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   |

This is my code:

<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>

So this should basically return 250 for August month, 100 for July and 0 for the rest of the months. But when using this only the cell Januar and Februar displays the value, and the other cells is not there. What am I doing wrong?

This is how it looks like live: https://ibb.co/8bd6JWP (the 12750 has transfer date in August, not January, so all cells should have value 0, but August should have 12750)

Upvotes: 0

Views: 185

Answers (3)

Flash Thunder
Flash Thunder

Reputation: 12036

Try something like this (a bit cleaner and more understandable solution):

<?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>

Upvotes: 1

O. Jones
O. Jones

Reputation: 108676

Keep in mind that SQL handles sets of data rows. Your sample data and query generate a set of two rows of data. And, your php code reads that set and generates a <td>nnn</td> HTML table column for each row in your set. There's only two of them. Hence only two columns in your HTML table. That's WHY your code doesn't do what you want. This db-fiddle. https://www.db-fiddle.com/f/nfv5wDnBWWgP8dq8E26QtZ/0 demonstrates.

The more interesting question: how do you get it to work? It's surprisingly tricky until you get used to it. There are various solutions. My favorite is to use a query that generates the set of twelve rows you want and then LEFT JOINs your actual data to that set.

MONTH(sometdate) generates integers in the range [1-12]. So if you can use a set of those twelve integers in your query you'll have what you want.

Try this.

                    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

If you use the MariaDB variant of MySQL, that mysterious table seq_1_to_12 is already defined for you. Hooray. Otherwise, you need to define it yourself in your query. A good way is this. 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

Notice some things about this.

  • IFNULL(SUM(prov,0)) makes the result set show zeros for your missing months' data rather than nulls. It makes your report a little cleaner.
  • EXPANDED You need to put YEAR(transfer) = YEAR(CURRENT_DATE()) in the ON clause of your LEFT JOIN rather than in a WHERE clause. Why? When a WHERE clause (or ORDER BY or GROUP BY clause) mentions a column in a LEFT JOINed table, it converts the LEFT JOIN to an ordinary inner JOIN. Inner JOIN operations suppress rows from the right side of the JOIN; LEFT JOINs preserve them.
  • You must GROUP BY and ORDER BY your sequence values.

I wrote up an article about this a while ago.

Upvotes: 1

user3783243
user3783243

Reputation: 5224

You aren't checking the returned values. You need to select the month in addition to the aggregated values. You then can check if the month your on is the month you have values. I would select all your values first, put them in an array, then iterate over the months and check the array to see if it has values.

Rough idea:

$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;
}

and change the query to:

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

so you can use $row['month']. The $monthdata should be built as:

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

during iteration of the select.

Upvotes: 1

Related Questions