每年所有月份的年度總計

根據我所擁有的，我需要今年的總數：

我使用了以下查詢；

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

我有以下列：

Timestamp | entry | snowFalling | snowLying | snowDepth 

在一張dataDiary桌子上。我需要得到snowDepth每年的總數，而不僅僅是每個月。我的代碼使用我已有的查詢并將結果放入 HTML 表中：

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

下面的代碼導致錯誤：

致命錯誤：未捕獲的型別錯誤：不支持的運算元型別：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>

uj5u.com熱心網友回復：

您可以只跟蹤每年的總數。下面的代碼還包含 null 合并運算子，以查看源陣列是否有專案，如果沒有則??回退。0

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

輸出：

1
0
2
0
0
0
0
0
0
0
0
0
Total:3

在這里演示：https ://3v4l.org/ahHonY

標籤：phphtmlsqlsqlite

上一篇：為什么我會收到“sqlite3.ProgrammingError：提供的系結數量不正確”。即使我使用了一個元組？

下一篇：ASP.NETCoreWebAPI中的連接字串