Yearly totals for all months by year-CodePudding

From what I have I'm needing totals for the year:

I used following query;

SELECT
    strftime('%Y', Timestamp) AS year,
    substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1   3*strftime('%m', Timestamp), -3) AS month,
    SUM(snowDepth) AS depth
FROM DiaryData
GROUP BY year, month
ORDER BY year DESC

I have following columns :

Timestamp | entry | snowFalling | snowLying | snowDepth

in a dataDiary table. I need to get the total snowDepth for each year, not just for each month. My code uses the query I already have and puts result in an HTML table:

while ($row = $result->fetch(PDO::FETCH_ASSOC)) { //fetch the query and put into array(s)
  $year                   = $row['year'];
  $month                  = $row['month'];
  $years[$year][$month][] = $row;
  //print_r($something);  -- used for debugging --
}

$totalSQLtime = (microtime(true) - $startSQLtime); // ends time for time it took to get query result(s)

$monthx = array('Jan', 'Feb', 'Mar', 'Apr', 'May',
 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

foreach ($years as $year => $months) {
 echo "<tr><td>" . $year . "</td>";

 foreach ($monthx as $month) {

  if (isset($months[$month])) {
   foreach ($months[$month] as $item) {
    if ($item['month'] === 'Jan') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Feb') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Mar') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Apr') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'May') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Jun') {
     echo "<td>" . $item['depth'] . "</td>";

    }if ($item['month'] === 'Jul') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Aug') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Sep') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Oct') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Nov') {
     echo "<td>" . $item['depth'] . "</td>";
    }

    if ($item['month'] === 'Dec') {
     echo "<td>" . $item['depth'] . "</td>";
    }

   }

  } else {echo "<td>" . $item['month'] = '0' . "</td>";}
 }
 echo '</tr>';
}

Below code causes error:

Fatal error: Uncaught TypeError: Unsupported operand types: int array

    while ($row = $result->fetch(PDO::FETCH_ASSOC)) { //fetch the query and put into array(s)
 $year                   = $row['year'];
 $month                  = $row['month'];
 $years[$year][$month][] = $row;
 //print_r($something);  -- used for debugging --
}
$totalSQLtime = (microtime(true) - $startSQLtime); // ends time for time it took to get query result(s)

$monthx = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

foreach ($years as $year => $months) {
 echo "<tr><td>" . $year . "</td>";

foreach ($monthx as $month) {

 if (isset($months[$month])) {
  foreach ($months[$month] as $item) {
   echo "<td>" . $item['depth'] . "</td>";
  }
 } else {echo "<td>" . $item['month'] = '0' . "</td>";}
}
  //var_dump("thisMonthCount");
  //echo '<pre>' . print_r($total, true) . '</pre>';
 
foreach ($years as $year => $months) {   // <-------- This is the part that's giving me difficulties ------------>
 $total = 0;
 foreach ($monthx as $monthShort) {
  $thisMonthCount = $months[$monthShort] ?? 0;           // Trying to sum each monthly snow amount for a yearly
  $total  = (int) $thisMonthCount;                       // total and hav it placed in the proper row.
  echo "<td>" . $thisMonthCount . "</td>", PHP_EOL;
 }
 echo "<td>" . $total . "</td>";
}  // <--------------------------------------------  End of the part that's giving me difficulties ----------------->

var_dump($total);
 echo '</tr>';
}

// end of the code for displaying results in html table
echo '</tbody>';
echo '</table>';
echo '</div>';

?>
<!-- query time result displayed below the data table -->
<p style="font-size:0.9em; padding: 10px 0 0 0;">
        <?php
echo 'Queries took: ' . number_format($totalSQLtime, 6) . ' secs<br>' . PHP_EOL;
//End of page execution timer
$totaltime = (microtime(true) - $starttime);
echo 'Total gen time: ' . number_format($totaltime, 6) . ' secs'; ?>
</p>

CodePudding user response：

You can just keep track of the total per year. The below code also includes the null coalescing operator ?? to see if the source array has an item, and falls back to 0 if it doesn't.

$data = [
    2022 => [
        'Jan' => 1,
        'Mar' => 2,
    ]
];

$allMonths = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];

foreach($data as $year => $months) {
    $total = 0;
    foreach($allMonths as $monthShort) {
        $thisMonthCount = $months[$monthShort] ?? 0;
        $total  = $thisMonthCount;
        echo $thisMonthCount, PHP_EOL;
    }
    echo 'Total:' . $total;
}

Output:

Demo here: https://3v4l.org/ahHonY