John Davidson

Message:

I have a query which returns me some data.

//I loop through the query's result and write all data out



while($row =$result->fetch_assoc()){

    echo $row['place'] . '|';

    echo $row['gets_busy'] . '|';

    echo $row['gets_empty '] . '|';

    echo $row['time_between'] . '|';

    echo "<br>";

}



ROOM01|2021-03-05 12:02:56|2021-03-05 12:04:02|00:01:06|

ROOM01|2021-03-05 12:05:42|2021-03-05 12:07:48|00:02:06|

ROOM01|2021-03-05 12:07:48|2021-03-05 12:12:54|00:05:06|

ROOM02|2021-03-05 12:15:54|2021-03-05 12:17:00|00:01:06|

ROOM02|2021-03-05 12:17:01|2021-03-05 12:23:17|00:05:16|

ROOM02|2021-03-05 12:23:59|2021-03-05 12:25:45|00:01:46|

I want something to write for the user like:

ROOM01 was busy for -> 00:08:18 during 2021-03-05

ROOM02 was busy for -> 00:08:08 during 2021-03-05

Can this be accomplished by PHP?

---

My Answer:
Yes, this can be accomplished by PHP. You can calculate the total time each room was busy by adding up the time_between values for each room and then formatting the total time in H:i:s format. Here's how you can do it:

php
$busyTimes = array();

while($row = $result->fetch_assoc()){
$room = $row['place'];
$timeBetween = $row['time_between'];

if(isset($busyTimes[$room])){
$busyTimes[$room] += strtotime($timeBetween) - strtotime('00:00:00');
} else {
$busyTimes[$room] = strtotime($timeBetween) - strtotime('00:00:00');
}
}

foreach($busyTimes as $room => $totalTime){
$formattedTime = date('H:i:s', $totalTime);
echo $room . " was busy for -> " . $formattedTime . " during 2021-03-05
";
}


This code will calculate the total time each room was busy for on the specified date and then format the total time in H:i:s format before displaying it to the user.