lalthung lalthung - 4 months ago 8
MySQL Question

Get total rows hours and minutes

So I get total login hours of a user in the following way. Now I wish to get the total count of all both time_in and time_out datetime fields and display all total hours and minutes.

$query = "
SELECT member_id
, member_name
, team
, time_in
, time_out
, SEC_TO_TIME(UNIX_TIMESTAMP(time_out) - UNIX_TIMESTAMP(time_in)) totalhours
FROM hours
WHERE member_id = 7;
";

$result = mysql_query($query)or die(mysql_error());
$rowNo = 1; //Increment Row Number
while($row = mysql_fetch_assoc($result)){
$time = $row['totalhours'];

echo "<tr align='left'>";
echo"<td><font color='white'>" .$rowNo++."</font>.</td>";
echo"<td><font color='white'>" .$row['member_name']."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_in']))."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_out']))."</font>.</td>";
echo"<td><font color='white'>" .$time." Hrs</font>.</td>";
echo "</tr>";
}


Here is an image output

Output of current query

Your help is much appreciated.

Answer

Use this code, I hope this helps

$query = "select member_id, member_name, team, time_in, time_out, sec_to_time(unix_timestamp(time_out) - unix_timestamp(time_in)) AS totalhours from hours WHERE member_id ='7'";

$result = mysql_query($query)or die(mysql_error());
$rowNo = 1; //Increment Row Number
$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><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_in']))."</font>.</td>";
echo"<td><font color='white'>" .date('Y-M-d - h:i:s a ', strtotime($row['time_out']))."</font>.</td>";
echo"<td><font color='white'>" .$time." Hrs</font>.</td>"; 
echo "</tr>";          
}

echo $total_time;