Hi,
I have a query that display's results from my sql database. I have it listing a start time and an end time. And I have a script that calculates the duration of how long the service was an outage or was just degraded. I am wanting to display a time total, so gathering the row results and the script that calculates the duration. How can I make it calculate ALL of the displayed services total time based upon each individual service name? Every time I try, it's only calculating each row's hours and minutes and not counting all of the $hours and $minutes and calculating them to provide a total.
Here's my query thus far:
// ##### View DB data ##### Start ->
$str = str_replace("\n", "<BR />", $str);
if(!isset($cmd))
{
$result = mysql_query("SELECT *, DATE_FORMAT(ss_datestart, '%d-%m-%Y') AS ss_datestartf, DATE_FORMAT(ss_dateend, '%d-%m-%Y') AS ss_dateendf
FROM tbl_services
WHERE ss_datestart
BETWEEN '$datemonthdate'
AND '$datetodaydate'
ORDER BY ss_closed ASC, ss_datestart DESC, ss_timestart DESC");
echo "<table width='100%' cellpadding='0' cellspacing='0' border='1'>";
echo "<tr>";
echo "<td valign='top'>Service Name</td>";
echo "<td valign='top'>RT Raised?</td>";
echo "<td valign='top'>Service Resolved?</td>";
echo "<td valign='top'>Date</td>";
echo "<td valign='top'>Time</td>";
echo "</tr>";
while($r=mysql_fetch_array($result))
{
$ss_id=$r["ss_id"];
$ss_status=$r["ss_status"];
$ss_closed=$r["ss_closed"];
$ss_service=str_replace("\r\n","<br>",$r[ss_service]);
$ss_comment=str_replace("\r\n","<br>",$r[ss_comment]);
$ss_rt=$r["ss_rt"];
$ss_rt_raised=$r["ss_rt_raised"];
$ss_useropen=$r["ss_useropen"];
$ss_userclose=$r["ss_userclose"];
$ss_datestart=$r["ss_datestartf"];
$ss_dateend=$r["ss_dateendf"];
$ss_timestart=$r["ss_timestart"];
$ss_timeend=$r["ss_timeend"];
echo "<tr>";
echo "<td valign='top'>$ss_service</td>";
echo "<td valign='top'>$ss_rt_raised</td>";
echo "<td valign='top'>$ss_closed</td>";
echo "<td valign='top'>";
echo "Date Start: $ss_datestart<br>Date End: $ss_dateend";
echo "</td>";
echo "<td valign='top'>";
echo "Time Start: $ss_timestart<br>Time End: $ss_timeend<br>";
$date1 = "$ss_datestart"; $time1 = "$ss_timestart";
$date2 = "$ss_dateend"; $time2 = "$ss_timeend";
$before = strtotime($date1 . " " . $time1);
$after = strtotime($date2 . " " . $time2);
$diff = $after - $before;
$hours = floor($diff / 3600);
$minutes = floor(($diff - $hours * 3600) / 60);
$seconds = $diff - $hours * 3600 - $minutes * 60;
echo "Duration: $hours hours and $minutes minutes";
echo "</td>";
echo "</tr>";
}
echo "</table>";
echo "<br>";
}
// ##### View DB data ##### End <-