Page 1 of 1
Adding an average time in PHP
Posted: Fri Mar 18, 2005 5:41 am
by arjcvg
hi there!
i got a little problem.
in a mysql database i have the following information in exactly the same format:
00:12:15
00:20:10
00:32:13
00:17:25
00:04:10
how can i get the average of those data in php to get this result: 00:17:15
? Should be in that format as well..
Thanks for helping me out!
Posted: Fri Mar 18, 2005 5:56 am
by timvw
lookup the avg function and the groupby clause (as avg is a set function instead of a scalar function)
Posted: Fri Mar 18, 2005 11:12 pm
by arjcvg
Hi! I did that already with this format:
select avg(fieldname) as cool FROM table
and i got this result 1714.6, not formatted as time... it should output like 00:17:15...
Can you tell me what exactly should I do? Can you write here the code? Thanks so much! Also, how would i print the result in php... thanks!!!
Coz i tried doing this:
$sql="SELECT AVG(fieldname) AS field FROM table";
$result=mysql_query($sql);
echo "$result";
but it did not print the result in php...
Thanks for all the help!
Posted: Fri Mar 18, 2005 11:34 pm
by feyd
mysql_query() returns a resource identifier to the results of the select query, nothing more. (other than false) You need to "fetch" the data. I typically recommend mysql_fetch_assoc(), but in this case, mysql_fetch_row() works well.
by the way, the average of those is 00:17:14.6 which is a direct mapping from 1714.6 .. worst case, you would need php to more easily format this answer to what you want.. by using round() along with some math functions.
Posted: Sat Mar 19, 2005 1:01 am
by arjcvg
I really appreciate your help but can you give me a sample code of what you just said? Thanks!
Posted: Sat Mar 19, 2005 1:08 am
by arjcvg
let me restate what i posted:
In my database, i have a timely column with the following data and format:
00:15:20
00:12:36
00:18:20
00:12:20
00:36:12
I want to generate the average of this column to output this result: 00:18:58?
I tried doing this:
SELECT AVG(timely) AS timely FROM table_name
Running this query in mysql, you will get the result but its not on on this format hh:mm:ss. It is in decimal format. But what I need is on the same hh:mm:ss format.
Now, I tried putting the query result in php but then it did not show anything. Here's what I did:
$sql="SELECT AVG(timely) AS timely FROM table_name";
$result=mysql_query($result);
$aver=mysql_result($result,0,"timely");
echo "$aver";
Thanks a lot in advance!
Posted: Sat Mar 19, 2005 6:26 am
by phpScott
look up date_format() in the mysql manual and it will explain how to format the out put into something you want.
http://dev.mysql.com/doc/mysql/en/date- ... tions.html
look for date_format() in about the top quarter in the link
Posted: Tue Mar 22, 2005 2:06 am
by arjcvg
Hi I did that.. but the format of date that i'm trying to format is like this: 2078.142857... when i used the formula that you suggested, it returned NULL...
Posted: Tue Mar 22, 2005 2:32 am
by n00b Saibot
A very simple and straight-forward solution for ya!
Simply convert all the time values to seconds, average them and convert it back into original format. Works wonders.

Posted: Tue Mar 22, 2005 7:46 am
by Phoenixheart
I think this should work (but these are not codes, just the way)
About print out the result set:
Code: Select all
<?php
//connect and select database
$rs = mysql_query($query);
//fetch into an array
$row = mysql_fetch_assoc($rs);
//And just use $row['your_column_name']
echo $row['your_column_name']
?>
That's the way I did. I've been coding php only for 4 months ^_^
feyd | Please review how to post code using Code: Select all
tags. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]