I have a HTML table which is generated by PHP, Each table has a dynamic ID assigned to it
What i would like is a total of the time in the "Worked Time" column for each table.
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="author" content="">
<title>Visitor Sign In Admin</title>
<!-- Bootstrap Core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Custom CSS -->
<!--link href="css/sb-admin.css" rel="stylesheet">
<!-- Morris Charts CSS -->
<link href="css/plugins/morris.css" rel="stylesheet">
<!-- Custom Fonts -->
<link href="font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">
<link rel="stylesheet" href="css/ionicons.min.css"> <!-- needs to be made local -->
<head>
<body>
<div class="content-wrapper">
<section class="content-header">
<h1>
<div class="row">
<div >Cleaner Report (Today)</div>
<div ></section>
Todays report for cleaners
<section class="content">
<div class="row">
<div >
<div class="box box-primary">
<div class="box-body">
<?php
date_default_timezone_set('Europe/London');
include $con=mysqli_connect($hostname,$username,$password,"visitorsignin");
$sql = "SELECT staff_badgeid,first_name,last_name,signintime,signouttime FROM `signin_entries` WHERE iscleaner ='YES' AND signindate = curdate() ORDER BY staff_badgeid ASC";
$list_visitors_result=mysqli_query($con, $sql);
$count_visitors = mysqli_num_rows($list_visitors_result);
if ($count_visitors != 0) {
$current_badgeid = '';
while ($row = mysqli_fetch_array($list_visitors_result)) {
if ($current_badgeid == '') {
$current_badgeid = $row['staff_badgeid']; //define if empty, for the first table
echo "<table id='".$row['staff_badgeid']."' class='table table-bordered table-hover'><thead>
<tr>
<th>Name</th>
<th>Signin Time</th>
<th>Signout Time</th>
<th>Worked Time</th></section>
</tr>
</thead>
<tbody>";
}
if($row['staff_badgeid'] != $current_badgeid){
echo "<hr><br><tfoot><td class='totalCol'>Total:</td></tfoot></tr></table><table id='".$row['staff_badgeid']."' class='table table-bordered table-hover'><thead>
<tr>
<th>Name</th>
<th>Signin Time</th>
<th>Signout Time</th>
<th>Worked Time</th></section>
</tr>
</thead>
<tbody>";
$current_badgeid = $row['staff_badgeid'];
}
$signintime = $row['signintime'];
$signouttime = $row['signouttime'];
$firstname = $row['first_name'];
$lastname = $row['last_name'];
echo " <tr><td>$firstname $lastname</td><td>$signintime</td>";
if ($signouttime == "") {
echo "<td>Not Signed Out Yet</td>";
} else {
echo "<td>$signouttime</td>";
}
$timeFirst = strtotime(date("Y/m/d") . " " . $signintime);
$timeSecond = strtotime(date("Y/m/d") ." " . $signouttime);
//below checks if th second time is less than the first time than it must be from the day before so add 24 hours eg (signin time 23:30:00 signout time 07:30:30 would be 08:00:30 difference)
if ($timeSecond < $timeFirst) {
$timeSecond = $timeSecond + 86400;
}
if ($signouttime == "") {
echo "<td>Cleaner Has Not Signed Out Yet</td>";
} else {
$differenceInSeconds = $timeSecond - $timeFirst;
echo "<td class='rowDataSd'>".converttime($differenceInSeconds)."</td>";
}
echo "</tr>";
}
echo "<tfoot><td class='totalCol'>Total:</td></tfoot></tr></table>";
}
//below function converts the seconds difference into hh:mm:ss format to the nearest second
function converttime($seconds) {
$t = round($seconds);
return sprintf('%02d:%02d:%02d', ($t/3600),($t/60%60), $t%60);
}
?>
</div>
</div>
</div>
</div>
</div>
</section>
</div>
<?php include_once ('bottom.php'); ?>
What would be the best solution? I'm currently stuck on the logic of referring to each table i did have this code which only applied to the first table due to the code referring to a single ID so only the first table worked correctly....is there anyway i could amend this to apply to the dynamic ID's now being applied?
<script type="text/javascript">
//below script taken from http://jsfiddle.net/unKDk/192/ looks at table id then column id called id rowDataSd
//<![CDATA[
$(window).load(function(){
var totals = [[0,0,0], [0,0,0], [0,0,0]];
$(document).ready(function () {
var $dataRows = $("#sum_table tr:not('.totalColumn, .titlerow')");
$dataRows.each(function () {
$(this).find('.rowDataSd').each(function (i) {
time = $(this).html().split(":")
totals[i][2] += parseInt(time[2]);
if(totals[i][2] > 60)
{
totals[i][2] %= 60;
totals[i][1] += parseInt(time[1]) + 1;
}
else
totals[i][1] += parseInt(time[1]);
if(totals[i][1] > 60)
{
totals[i][1] %= 60;
totals[i][0] += parseInt(time[0]) + 1;
}
else
totals[i][0] += parseInt(time[0]);
});
});
$("#sum_table td.totalCol").each(function (i) {
console.log(totals[i]);
$(this).html("" + totals[i][0] + ":" + totals[i][1] + ":" + totals[i][2]);
});
});
});
//]]></script>
If i change the script to refer to the table class as suggested i get a total of all tables "Worked Time" in the first table, I want a total of "Worked Time" per table
This is incorrect as the first table show a total of both tables "Worked Time" columns, each table should have separate totals from their own tables.