Count in php

[Andrew R]

Hi
I'm having a few problems counting in php and displaying the results in the right format.  For example if I want to count these values (below)

[img]http://img459.imageshack.us/img459/3134/1uq9.jpg[/img]

The code I'm using: (below)
[code]<?php
$sql = mysql_query("SELECT SUM(duration) AS count FROM reports");
$count = mysql_result($sql, 0, 'count');

$total = $count;
echo floor($count / 60) . ":" . ($count % 60);
?>[/code]

When I use this code (above) to count the values I get 6:40 which clearly isn't right.
How do i format this above script to display the result in the right format, hh:mm:ss, 00.05.00 ?

Cheers

[bljepp69]

use 'str_pad' - basically, you are just going to pad the string with a 0 on the left side.  Something like this:

[code]
<?php
$sql = mysql_query("SELECT SUM(duration) AS count FROM reports");
$count = mysql_result($sql, 0, 'count');

$total = $count;
$v1 = str_pad( (floor($count/60)) , 2, '0', STR_PAD_LEFT);
$v2 = str_pad( ($count % 60), 2, '0', STR_PAD_LEFT);
echo $v1.':'.$v2;
?>
[/code]

You might even be able to get the right format with just the mySQL query, but I haven't tested this.  Try something like.

[code]
$sql = mysql_query("SELECT DATE_FORMAT(SUM(duration),'%H:%i:%S') AS count FROM reports");
[/code]

[Andrew R]

Thanks for your help although I'm still having problems. The result is not correct as its displaying 06:40 when it should be 00:04.

I simply want to add 00:01:00 and 00:03:00 from the database colunm 'duration' and have a result of 00:04 or another example 00:60:00 and 00:30:00 and have a result of 01:30.

[bljepp69]

if 'duration' is type TIME in your db, you should be able to use the DATE_FORMAT query and get the output.  If you pull out a TIME type and try to do regular math, you won't get the expected results.  You would have to convert it to a timestamp, then do your calcs, then convert back to time.

[kenrbnsn]

Here is one way of solving your problem:
[code]<?php
$times = array(array('00:01:00','00:03:00'), array('01:00:00','00:30:00'));
for ($i=0;$i<count($times);$i++)
    echo implode(' + ',$times[$i]) . ' = ' . add_times($times[$i]) . "\n";

function add_times($ta)
{
        $z = strtotime(date('Y-m-d'),time());
        for ($i = 0;$i<count($ta);$i++) {
            $t = explode(':',$ta[$i]);
            $z += ($t[0] * 3600)  + ($t[1] * 60) + $t[2]; }
        return(date('H:i:s',$z));
}
?>[/code]

Ken