获取总行数小时和分钟

Question

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

Your help is much appreciated.

Answer 1

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;