unable to get the correct sub total time

[lovephp]

so this is calculating time differences but something about the total_time is not ok it wont display correct number of total hours

 

<?php
$usr = $_REQUEST['uname'];
$yr = $_REQUEST['year'];
$mn = $_REQUEST['month'];
$chk = "$yr-$mn";
$query = "SELECT hours_id, member_id, member_name, team,
time_in, time_out,
SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(time_out, time_in))) AS totalhours
FROM hours
where time_in LIKE '%".$chk."%'
and member_id = '".$usr."'";

$result = mysql_query($query)or die(mysql_error());
$rowNo = 1;
$total_time="00:00:00";
while($row = mysql_fetch_assoc($result)){
$time = $row['totalhours'];

$secs = strtotime($time)-strtotime("00:00:00");
$total_time = date("H:i:s",strtotime($total_time)+$secs);

echo "<tr align='left'>";
echo"<td><font color='white'>" .$rowNo++."</font></td>";
echo"<td><font color='white'>" .$row['member_name']."</font></td>";
echo"<td class='edit_td'><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_in']))."</font></td>";
if($row['time_in'] == $row['time_out']){
echo"<td><font color='green'>STILL LOGGED IN</font></td>"; 
}else{
echo"<td class='edit_td'><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_out']))."</font></td>";
}
if($row['time_out'] == '0000-00-00 00:00:00'){
echo"<td><font color='white'></font></td>"; 
}else{
echo"<td><font color='white'>" .$time." Hrs</font></td>";
echo"<td><font color='white'><a href ='edit-logins.php?id=".$row['hours_id']."&mid=".$row['member_id']."' target='_blank'><img src='images/edit.png' alt='Edit' height='16' width='16' title='Edit'></a> | <a href='javascript:delete_id(".$row['hours_id'].")'><img src='images/x.gif' alt='Delete' height='16' width='16' title='Delete'></a></font></td>"; 
}
echo "</tr>"; 
}
?> 
</table>
<?php echo '<p align="right"><font size="4" color="black">Sub Total:</font> '.$total_time.' Hrs </p>'; ?>// This line where i wish to display the all total count of time

 

attached image has the result how it is displaying. any idea what could be wrong? my check was to select from db where time_in like 2016-07 and user id 2

Edited August 1, 2016 by lovephp

[Jacques1]

What is this code? I've seen you use prepared statements, now you're suddenly back writing awful vulnerability-infested 90s amateur scripts.

[lovephp]

What is this code? I've seen you use prepared statements, now you're suddenly back writing awful vulnerability-infested 90s amateur scripts.

Its just an old offline script i do use prepared statements on my new script

[ginerjm]

I think Jacques point was to use you 'new' skills to correct the old script. Progress!

[lovephp]

I think Jacques point was to use you 'new' skills to correct the old script. Progress!

Yes yes i agree infact after all you guys told me i have started using pdo but his is an very old script too much time it will take to convert and moreover this is not for online purpose

[lovephp]

Anyway what exactly seems to be wrong in this code that i don't get the exact count for total hours?

[kicken]

You cannot use the date/strtotime functions to deal with a duration. They are for dealing with an absolute time. What you've done might happen to work for a period less than 24-hours but will fail for more than that. Crossing the 24-hour mark will result in an increase to the date and reset the hours back to 0.

 

Probably the simplest thing to do would be to have your query just return a total number of seconds rather than a formatted duration. Then you can write a function to take those seconds and format it for display.

 

function formatDuration($sec){
    $h = floor($sec/(60*60));
    $sec -= $h*(60*60);

    $m = floor($sec/60);
    $sec -= $m*60;

    return sprintf("%02d:%02d:%02d", $h, $m, $sec);
}

That would give you a simple HH:mm:ss display for your durations. If you do a little searching you can find all sorts of duration formatting functions.

 

Then in your code you change the query to return seconds and use normal math to sum them and the function to display the result.

$query = "
SELECT hours_id, member_id, member_name, team, time_in, time_out, TIME_TO_SEC(TIMEDIFF(time_out, time_in)) AS totalhours
FROM hours
where time_in LIKE '%".$chk."%'
and member_id = '".$usr."'
";

$total_time = 0;
while($row = mysql_fetch_assoc($result)){
	$total_time += $row['totalhours'];
	
	//...
	echo"<td><font color='white'>" .formatDuration($row['totalhours'])." Hrs</font></td>";
}

Edited August 1, 2016 by kicken

[Barand]

 

SEC_TO_TIME(TIME_TO_SEC(

WTF ???

[lovephp]

WTF ???

sorry missed out to remove the time_to_sec

[lovephp]

You cannot use the date/strtotime functions to deal with a duration. They are for dealing with an absolute time. What you've done might happen to work for a period less than 24-hours but will fail for more than that. Crossing the 24-hour mark will result in an increase to the date and reset the hours back to 0.

 

Probably the simplest thing to do would be to have your query just return a total number of seconds rather than a formatted duration. Then you can write a function to take those seconds and format it for display.

 

function formatDuration($sec){
    $h = floor($sec/(60*60));
    $sec -= $h*(60*60);

    $m = floor($sec/60);
    $sec -= $m*60;

    return sprintf("%02d:%02d:%02d", $h, $m, $sec);
}

That would give you a simple HH:mm:ss display for your durations. If you do a little searching you can find all sorts of duration formatting functions.

 

Then in your code you change the query to return seconds and use normal math to sum them and the function to display the result.

$query = "
SELECT hours_id, member_id, member_name, team, time_in, time_out, TIME_TO_SEC(TIMEDIFF(time_out, time_in)) AS totalhours
FROM hours
where time_in LIKE '%".$chk."%'
and member_id = '".$usr."'
";

$total_time = 0;
while($row = mysql_fetch_assoc($result)){
	$total_time += $row['totalhours'];
	
	//...
	echo"<td><font color='white'>" .formatDuration($row['totalhours'])." Hrs</font></td>";
}

thanks alot #Kicken that function is a magic really appreciate it mate