Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Apr 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Total Hours

    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

  • #2
    New Coder
    Join Date
    Nov 2010
    Location
    California
    Posts
    42
    Thanks
    6
    Thanked 2 Times in 2 Posts
    I think the syntax for a sum statement is more like this.

    PHP Code:
    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.
    Puppet Master + Programming = Eternal Bliss

  • #3
    New Coder
    Join Date
    Sep 2011
    Posts
    80
    Thanks
    0
    Thanked 13 Times in 12 Posts
    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:

    PHP Code:
    echo mysql_result($result1); 
    This will grab the result from the first row returned, you should only ever get one row returned when you only select sum().

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    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:
    PHP Code:
    $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):
    PHP Code:
    $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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •