How to add a bottom row that tallies total from a column in PHP MySQL PDO and display it?

Question

This is my very first question here in stackoverflow, so please bear with me :)

I am a newbie with PDO PHP. I currently am "maintaining" a bespoke CRM for a company I am working for (got accepted recently after graduating). As a fresh grad, I was able to "fix" and implement things, but this one has me stumped.

I would like to tally the "total hours" in the bottom row as shown in the pic down below for each and every service table.

CRM in question

The code has been drummed up by my predecessor that has left but is still willing to help. I have been trying to make changes but there is none that sticks nor works. I am out of options. I have added comments to it to help me navigate things.

<?php // Start PHP2
// Query SQL Table for the Sum total of hours and display at the bottom of the table for each service
$sqltot = "
SELECT SUM(cn_hours) AS total_hours 
  FROM Casenotes 
 WHERE cn_date_service BETWEEN '" . $agreementstart . "' AND '" . $agreementend . "' 
   AND part_id = :id 
   AND cn_group = :groupid
";

// PDO prepare statement
$st = $pdo->prepare($sqltot);

$st->bindvalue(':id', $partid);
$st->bindvalue(':groupid', $serviceid);

// mysqli_fetch_assoc equivalent for PDO
$st->execute(); 


// this is supposedly what prints our table gathered from SQL Query gathered from above
foreach($st as $rowst) {
    echo $rowst['total_hours'];
}

if($rowservagr['sa_closed'] == 0){
 ?> <!-- End PHP2 -->

    
<form method="post" action="inc/casenote-admin.php" style="float:right;margin-right:20px;">
                        
                        <input type="hidden" name="part_id" value="<?php echo $partid; ?>" />
                        <input type="hidden" name="cnservice_id" value="<?php echo $serviceid; ?>" />
                        <input type="hidden" name="cn_staff_id" value="<?php echo $user_id; ?>" />
                        
                        <input type="hidden" name="newnote" />
                        
                        <button type="submit" name="submitcase" style="border:none;color:#FFFF00;background-color:transparent;font-size:15px;cursor:pointer;">new casenote <i class="fas fa-plus-square"></i>
                        </button>
                    </form>
                        <?php
                        } // closes Agreement is not closed
                        ?>
                    
                </div>
                
            <table style="width:100%;font-size:13px;margin-bottom:20px;">
                    <tr>
                        <th class="cellfirstdocs date-col">service</th>
                        <th class="celldocs date-col">time</th>
                        <th class="celldocs by-col">by</th>
                        <th class="celldocs type-col">type</th>
                        <th class="celldocs">notes</th>
                        <th class="celldocs view-col"></th>
                        <th class="celldocs hours-col">hours</th>
                        <th class="celldocs view-col"></th>
                        <?php if($rowservagr['sa_closed'] == 0){ ?>
                        <th class="celldocs edit-col">edit</th>
                        <?php } ?>
                    </tr>
                    <?php
            
                    // select all records from docs table for this participant id
                    // $sqldc = "SELECT * FROM Casenotes WHERE part_id = :id AND cn_group = :groupid AND cn_staff_id = :staffid ORDER BY cn_date_service DESC";
                    $sqldc = "SELECT * FROM Casenotes WHERE part_id = :id AND cn_group = :groupid ORDER BY cn_date_service DESC, cn_from DESC";
            
                    $sd = $pdo->prepare($sqldc);
        
                    $sd->bindvalue(':id', $partid);
                    $sd->bindvalue(':groupid', $serviceid);
                    // $sd->bindvalue(':staffid', $user_id);
    
                    $sd->execute();

                    foreach($sd as $rowd) {
                    ?>
                    <tr class="parent" id="<?php echo $rowd['cn_id']; ?>">
                        <td class="cellfirstdocs"><?php echo date("d/m/Y", strtotime($rowd['cn_date_service'])); ?></td>
                        <td class="celldocs"><?php echo date("g:i A", strtotime($rowd['cn_from'])); ?></td>
                        <td class="celldocs"><?php echo StaffUsernameInitials($rowd['cn_staff_id']); ?></td>
                        <td class="celldocs"><?php echo CNTypeName($rowd['cn_type']); ?></td>
                        <td class="celldocs"><?php echo $rowd['cn_summary']; ?></td>
                        <td class="celldocs ctr"><span class="btn btn-default"><i class="fas fa-eye"></i></span></td>
                        <td class="celldocs"><?php echo $rowd['cn_hours']; ?></td>
                        <td class="celldocs ctr"><?php if($rowd['cn_attach'] != ''){ ?>
                                    <a href="/dcs/c_note/<?php echo $rowd['cn_attach']; ?>" target="_blank">
                                        <?php
                                        $name =  $rowd['cn_attach'];
                                        $extension = substr($name, strpos($name, '.') + 1);
                                        if($extension == 'pdf'){ ?>
                                        <i class="fas fa-file-pdf"></i>
                                        <?php
                                        } else if($extension == 'jpg' || $extension == 'jpeg' || $extension == 'png'){
                                        ?>
                                        <i class="fas fa-file-image"></i>
                                        <?php
                                        }
                                        ?>
                                    </a>
                                    <?php } ?></td>
                        
                        <?php if($rowservagr['sa_closed'] == 0){
                        ?>
                        <td class="celldocs">
                            <form method="post" action="inc/casenote-admin.php">
                                <input type="hidden" name="cn_id" value="<?php echo $rowd['cn_id']; ?>" />
                                
                                <input type="hidden" name="editnote" />
                                
                                <button type="submit" name="submitcase" style="border:none;background-color:transparent;cursor:pointer;">
                                    <i class="fas fa-edit"></i>
                                </button>
                            </form>
                        </td>
                        <?php
                        } // closes if not admin type
                        ?>
                        
                    </tr>
                    <tr class="child-<?php echo $rowd['cn_id']; ?>">
                        <td class="cellfirstdocs"></td>
                        <td class="celldocs"></td>
                        <td class="celldocs"></td>
                        <td class="celldocs"></td>
                        <td class="celldocs"><?php echo nl2br($rowd['cn_note']); ?></td>
                        <td class="celldocs"></td>
                        <td class="celldocs"></td>
                        <td class="celldocs"></td>
                        <td class="celldocs"></td>
                    </tr>
                    <?php
                    } // closes for each loop
                    ?>
                </table>    

Answer 1

This is the reason why keeping your presentation logic and database logic separate as much as possible is a brilliant idea.

Fetch all your results into an array before displaying any HTML. Do all calculations beforehand and then only display the HTML table.

$sqldc = "SELECT * 
    FROM Casenotes 
    WHERE part_id = :id AND cn_group = :groupid 
    ORDER BY cn_date_service DESC, cn_from DESC";
            
$sd = $pdo->prepare($sqldc);

$sd->execute([
    'id' => $partid,
    'groupid' => $serviceid
]);

$results = $sd->fetchAll(PDO::FETCH_ASSOC);
$total_hours = array_sum(array_column($results, 'cn_hours'));

Then you can just add one more <tr> after your foreach loop and print out the total tally.