I have multiple HTML tables 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.
If anybody has any easier way of totaling Time Worked any suggestions would be greatly appreciated