Adding an average time in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
arjcvg
Forum Newbie
Posts: 5
Joined: Fri Mar 18, 2005 5:37 am
Contact:

Adding an average time in PHP

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

lookup the avg function and the groupby clause (as avg is a set function instead of a scalar function)
arjcvg
Forum Newbie
Posts: 5
Joined: Fri Mar 18, 2005 5:37 am
Contact:

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
arjcvg
Forum Newbie
Posts: 5
Joined: Fri Mar 18, 2005 5:37 am
Contact:

Post by arjcvg »

I really appreciate your help but can you give me a sample code of what you just said? Thanks!
arjcvg
Forum Newbie
Posts: 5
Joined: Fri Mar 18, 2005 5:37 am
Contact:

Post 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!
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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
arjcvg
Forum Newbie
Posts: 5
Joined: Fri Mar 18, 2005 5:37 am
Contact:

Post 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...
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post 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
Phoenixheart
Forum Contributor
Posts: 123
Joined: Tue Nov 16, 2004 7:46 am
Contact:

Post 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]
Post Reply