我有DiaryData列Timestamp, entry, snowFalling, snowLaying,的表格snowDepth。我只對Timestamp和snowDepth專欄感興趣。
通過運行此代碼：
<?php
$inipath = php_ini_loaded_file();
if ($inipath)
{
echo 'This script (' . basename(__FILE__) . ') is running with php.ini: ' . $inipath;
} else {
echo 'A php.ini file is not loaded';
}
echo PHP_EOL;
$myDbase = 'sqlite:\CumulusMX\data\diary.db';
$pdo = new PDO($myDbase); // connect to db ('sqlite:'.__DIR__.'/mytest.db');
if($pdo === false) {
$liteError = sqlite_last_error ($pdo);
echo ' Error: ' . sqlite_error_string ($liteError);
}else{
echo nl2br (("Success, database ") . $myDbase . (" is open \n\n")) . PHP_EOL;
/*SQLite query used to search for the year, month and total snow for the year by month*/
$result = $pdo->query(
"SELECT strftime('%Y', Timestamp) AS year, substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 3*strftime('%m', Timestamp), -3) AS month,
CASE WHEN strftime('%m', Timestamp) = '01' THEN SUM(snowDepth) ELSE '0' END AS Jan,
CASE WHEN strftime('%m', Timestamp) = '02' THEN SUM(snowDepth) ELSE '0' END AS Feb,
CASE WHEN strftime('%m', Timestamp) = '03' THEN SUM(snowDepth) ELSE '0' END AS Mar,
CASE WHEN strftime('%m', Timestamp) = '04' THEN SUM(snowDepth) ELSE '0' END AS Apr,
CASE WHEN strftime('%m', Timestamp) = '05' THEN SUM(snowDepth) ELSE '0' END AS May,
CASE WHEN strftime('%m', Timestamp) = '06' THEN SUM(snowDepth) ELSE '0' END AS Jun,
CASE WHEN strftime('%m', Timestamp) = '07' THEN SUM(snowDepth) ELSE '0' END AS Jul,
CASE WHEN strftime('%m', Timestamp) = '08' THEN SUM(snowDepth) ELSE '0' END AS Aug,
CASE WHEN strftime('%m', Timestamp) = '09' THEN SUM(snowDepth) ELSE '0' END AS Sep,
CASE WHEN strftime('%m', Timestamp) = '10' THEN SUM(snowDepth) ELSE '0' END AS Oct,
CASE WHEN strftime('%m', Timestamp) = '11' THEN SUM(snowDepth) ELSE '0' END AS Nov,
CASE WHEN strftime('%m', Timestamp) = '12' THEN SUM(snowDepth) ELSE '0' END AS Dec
FROM DiaryData
GROUP BY year, strftime('%m', Timestamp) = '01', strftime('%m', Timestamp) = '02', strftime('%m', Timestamp) = '03',
strftime('%m', Timestamp) = '04', strftime('%m', Timestamp) = '05', strftime('%m', Timestamp) = '06',
strftime('%m', Timestamp) = '07', strftime('%m', Timestamp) = '08', strftime('%m', Timestamp) = '09',
strftime('%m', Timestamp) = '10', strftime('%m', Timestamp) = '11', strftime('%m', Timestamp) = '12'
ORDER BY year");
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link href="lib/bootstrap/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<table class="table table-bordered">
<thead>
<tr>
<th>Year</th>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>Jun</th>
<th>Jul</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
<th>Nov</th>
<th>Dec</th>
</tr>
</thead>
<tbody>
<?php
$i=0;
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
?>
<tr>
<td><?php echo $row["year"]; ?></td>
<td><?php echo $row["Jan"]; ?></td>
<td><?php echo $row["Feb"]; ?></td>
<td><?php echo $row["Mar"]; ?></td>
<td><?php echo $row["Apr"]; ?></td>
<td><?php echo $row["May"]; ?></td>
<td><?php echo $row["Jun"]; ?></td>
<td><?php echo $row["Jul"]; ?></td>
<td><?php echo $row["Aug"]; ?></td>
<td><?php echo $row["Sep"]; ?></td>
<td><?php echo $row["Oct"]; ?></td>
<td><?php echo $row["Nov"]; ?></td>
<td><?php echo $row["Dec"]; ?></td>
</tr>
<?php
$i ;
} ?>
</tbody>
</table>
</div>
</body>
</html>
我得到：
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2021 0 0 0 0 0 0 0 0 0 0 0 7.5
2022 0 0 0 0 0 0 0 0 0 5.25 0 0
2022 0 0 0 0 0 0 0 0 6.1 0 0 0
我想要像這樣的標題結構：
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2022 0 0 0 0 0 0 0 0 6.1 5.25 0 0
2021 0 0 0 0 0 0 0 0 0 0 0 7.5
上面的結構將在一個使用 PHP 的 HTML 表中。如果我GROUP BY只是一年，我會在該列snowDepth下獲得整年的總數。Oct我能夠獲得snowDepth每一年，它看起來像：
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2022 6.15.25
2021 7.5
但我永遠無法讓它填補剩下的幾個月，以便資料在正確的列中，我失去了我是如何做到這一點的。
uj5u.com熱心網友回復：
PHP 代碼看起來像這樣來獲取和準備資料
<?php
$inipath = php_ini_loaded_file();
if ($inipath)
{
echo 'This script (' . basename(__FILE__) . ') is running with php.ini: ' . $inipath;
} else {
echo 'A php.ini file is not loaded';
}
echo PHP_EOL;
$myDbase = 'sqlite:\CumulusMX\data\diary.db';
$pdo = new PDO($myDbase); // connect to db ('sqlite:'.__DIR__.'/mytest.db');
if($pdo === false) {
$liteError = sqlite_last_error ($pdo);
echo ' Error: ' . sqlite_error_string ($liteError);
}else{
echo nl2br (("Success, database ") . $myDbase . (" is open \n\n")) . PHP_EOL;
$sql = "SELECT Timestamp, DATE_FORMAT(Timestamp,'%Y') fYear, SUM(snowDepth)fSnowDepth, DATE_FORMAT(Timestamp, '%m')fMonth FROM diarydata d
GROUP BY fYear, fMonth
HAVING fSnowDepth>0 ORDER BY fYear DESC, fMonth";
/*SQLite query used to search for the year, month and total snow for the year by month*/
$raw = $pdo->query($sql);
$result = array();
while($row = $raw->fetch(PDO::FETCH_ASSOC)){array_push($result['raw'],$row) ;}
}
if(empty($result['raw']) === false){
$year = '';
foreach($result['raw'] as $item){
if($year !== $item['fYear']){
$result['data']['years'][] = $item['fYear'];
$year = $item['fYear'];
}
}
foreach($result['data']['years'] as $key=>$value){
foreach($result['raw'] as $item){
if($value == $item['fYear']){
for($i = 0; $i <= 11; $i ){
if($i 1 == $item['fMonth']){
$result['data'][$value][$i] = array('fYear' =>$value, 'fMonth' => $months_list[$item['fMonth']-1], 'fSnowDepth' => $item['fSnowDepth']);
}elseif(empty($result['data'][$value][$i])){
$result['data'][$value][$i] = array('fYear' => $value, 'fMonth' => $months_list[$i], 'fSnowDepth' => '0');
}
}
}
}
}
}
unset($result['data']['years']);
?>
然后這里是html
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>combine-multiple-rows-and-put-in-html-table</title>
</head>
<body>
<div class="container">
<table class="table table-bordered" border="2"; cellpadding="5">
<thead>
<tr>
<th>Year</th>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>Jun</th>
<th>Jul</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
<th>Nov</th>
<th>Dec</th>
</tr>
</thead>
<tbody>
<?php
foreach($result['data'] as $year => $months) {
?>
<tr>
<td><?php echo $year; ?></td>
<?php foreach($months as $month){ ?>
<td><?php echo $month["fSnowDepth"]; ?></td>
<?php } ?>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</body>
</html>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/514724.html