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
phpScott wrote: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
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. :D

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

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]