Hi all.

I'm a newbie to PHP and Mysql, and I hope you can help me.
I have a Mysql table called "jos_log", in that table I have a column called "total", and it's a TIME type which stores total time like this 14:00.
I already have a PHP script that calculate startTime and endTime and calculate the Total time for each row (14:00 - 12:00 = 02:00).
Now I need a PHP script that will automatic SUM the column Total from the table, and will represented the SUM in a text field. The format should be HH:mm.

One thing I forgot to mansion! I'm doing this to save my Filght Time (I'm a Pilot). Which means, The SUM time is a lot higher the 24:00. Right now I have 60:00 hours.

Looking forward to hear from someone 🙂

Cheers.

    Are you storing the start and end time as DATETIME fields ? Would make it a lot easier to calculate the total.

      Hi Planetsin

      No, I'm using TIME type. I have a script which calculate the diff minutes from to field and then convert the minutes to hours and minutes like this HH:mm. It took me long time to figure out how to do that!!! and this is how it looks like:

      $to_time=strtotime("{stime}");
$from_time=strtotime("{etime}");
$total_time=round(abs($to_time - $from_time) / 60,2);

function ConvertMinutes2Hours($Minutes)

{
    if ($Minutes < 0)
    {
        $Min = Abs($Minutes);
    }
    else
    {
        $Min = $Minutes;
    }
    $iHours = Floor($Min / 60);
    $Minutes = ($Min - ($iHours * 60)) / 100;
    $tHours = $iHours + $Minutes;
    if ($Minutes < 0)
    {
        $tHours = $tHours * (-1);
    }
    $aHours = explode(".", $tHours);
    $iHours = $aHours[0];
    if (empty($aHours[1]))
    {
        $aHours[1] = "00";
    }
    $Minutes = $aHours[1];
    if (strlen($Minutes) < 2)
    {
        $Minutes = $Minutes ."0";
    }
    if ($iHours < 10)
    {
    $iHours = "0" . $iHours;
    }
    $tHours = $iHours .":". $Minutes;
    return $tHours;
}

{total}=ConvertMinutes2Hours($total_time);

      I have also found a script that calculates the total time from the db column 'total', but I can't figure out how to insert the result in my detail page, only as a pop up window with ECHO!!!

      $query1 = "SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`total`))) As total FROM `jos_log`";
$result1 = mysql_query($query1);
$row = mysql_fetch_assoc($result1);
echo $row['total'];

      Any ideas?

        Write a Reply...