Combine multiple rows and put in HTML table-CodePudding

I have table DiaryData with columns Timestamp, entry, snowFalling, snowLaying, snowDepth. I'm only interested in Timestamp and snowDepth columns.

By running this code:

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

I get :

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

I would like header structure like :

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

The above structure will be in a HTML table using PHP. If I GROUP BY just year I get a total of snowDepth for that whole year under the Oct column. I was able to get snowDepth for each individual year and it looked like :

Year  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec

2022   6.15.25
2021   7.5

but I could never get it to pad out the rest of the months so that the data was in its proper column and I lost how I accomplished that.

CodePudding user response：

When you group by year your data array has two rows and for 3 columns as following

Year | Sep | Oct | Dec 2022 | 6.15| 5.25 | 2011 | | | 7.5

Try this

<?php echo empty($row['Jan']) === false ? $row['Jan'] : 0 ;?>

replace for each month and you should get 0 printed for 0 non existing values