...

View Full Version : Calculating Total Hours



JMALLY
04-21-2012, 02:39 PM
Hi,

I have had a look and can not seem to get a code to work. I would really appriciate some help.

I am trying to get a code to calculate the total number of hours inputted into the database.

The table is called 'pireps' and the field I am trying to calculate is 'duration'.

I need this then to show the answer in a number format for the stats page.

This is what I have got..

function CountFlightTime() {

$result = mysql_query("SELECT * FROM pireps, SUM(duration) WHERE approved = '1'");
$number = mysql_num_rows($result);

return $number;

}

?>

Any help please :)

Puppet Master
04-21-2012, 04:35 PM
I think the syntax for a sum statement is more like this.


SELECT SUM(duration) AS total FROM pireps WHERE approved='1'

Then query it, return the array, then you have the ['total'] key with the sum.

MarkR
04-21-2012, 05:05 PM
As puppet master has said you need to alter your sql statement, then a problem resides in how you handle the information you are collecting.

You are calling mysql_num_rows, that will display the number of rows the query has found. you need to look at using:


echo mysql_result($result, 1);

This will grab the result from the first row returned, you should only ever get one row returned when you only select sum().

Fou-Lu
04-21-2012, 05:19 PM
I'll suggest that you alias and pull from a fetch instead of a result. Result won't be substantially slower on a single record result set like this, but should be avoided for anything with multiple records. Plus, using aggregates will often be done in a grouping of other data, and its easier to fetch by name than location, and this would just keep the handling consistent for any queries done.
So as the query provided by puppet master, use fetch with it:


$sQry = "SELECT SUM(duration) AS total FROM pireps WHERE approved=1";
if ($qry = mysql_query($sQry))
{
$record = mysql_fetch_assoc($qry);
printf('Sum: %0.2f', $record['total']);
}

And an example of a group instead (and why I suggest alias with fetch):


$sQry = "SELECT approved, SUM(duration) AS total FROM pireps GROUP BY approved ORDER BY approved ASC";
if ($qry = mysql_query($sQry))
{
while ($row = mysql_fetch_assoc($qry))
{
printf('Approved: %d, total: %0.2f' . PHP_EOL, $row['approved'], $row['total']);
}
}

You may also fetch_row and pull numerically by field location in the query, but I'd suggest avoiding that as much as you can since you may add or remove additional fields to your query which may break the code when using numeric offsets.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum