Andrew R Posted November 29, 2006 Share Posted November 29, 2006 HiI'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 Quote Link to comment Share on other sites More sharing options...
bljepp69 Posted November 29, 2006 Share Posted November 29, 2006 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] Quote Link to comment Share on other sites More sharing options...
Andrew R Posted November 29, 2006 Author Share Posted November 29, 2006 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. Quote Link to comment Share on other sites More sharing options...
bljepp69 Posted November 29, 2006 Share Posted November 29, 2006 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. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted November 29, 2006 Share Posted November 29, 2006 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.