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>
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…