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 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Mar 2013
    Location
    Lehigh Acres, FL, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation of Miles per gallon - HELP!!

    I have two fields I want to use all in one table called "fuel"
    Fields are entered when fuel is purchased, bus unit number, odometer reading, date, driver and gallons, cost per gallon and total cost.

    I need to use this data to calculate the MPG (miles per gallon) for each bus.
    Two main fields being used..
    Fuel Table
    1. Odometer Reading
    2. Gallons

    I figured out that I want to take the first and the last odometer reading to calculate total miles in SQL and divide into the total gallons to get my MPG and got this far..

    Code:
    <?php
    $con=mysqli_connect("choolbus.com","school_busUSER","FAKEPASS11","school_FAKEDATABASEName");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
      
     //$_POST['MPG'] is the array of forumla below
         foreach($_POST['BusUnitNumber'] as $Bus_Number)
         {
            if($BusUnitNumber) $selected[]=intval($BusUnitNumber);
         }
         $selectedlist=implode(",",$selected);
    
        //The SQL Query
    $x = mysql_query("SELECT MIN(OdometerReading) AS MilesStart FROM fuel");
    $y = mysql_query("SELECT MAX(OdometerReading) AS MilesLast FROM fuel");
    $z = mysql_query("SELECT SUM(Gallons) AS TotalGallons FROM fuel");
    
    $TOTALMPG='($x-$y)/$z'
    But I keep getting a parse error when running the report.
    Maybe it is the output.. I am a fairly new coder and could use all the suggestions and help I can get PRETTY PLEASE.

    Thanks from the Blonde Mermaid in Florida

  • #2
    New to the CF scene
    Join Date
    Mar 2013
    Location
    Lehigh Acres, FL, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I added this.. still getting error.. UG please help me out here.. this should be simple but its not..


    Code:
    <?php
    $con=mysqli_connect("choolbus.com","school_busUSER","FAKEPASS11","school_FAKEDATABASEName");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
      
     //$_POST['MPG'] is the array of forumla below
         foreach($_POST['BusUnitNumber'] as $Bus_Number)
         {
            if($BusUnitNumber) $selected[]=intval($BusUnitNumber);
         }
         $selectedlist=implode(",",$selected);
    
        //The SQL Query
    $x = mysql_query("SELECT MIN(OdometerReading) AS MilesStart FROM fuel");
    $y = mysql_query("SELECT MAX(OdometerReading) AS MilesLast FROM fuel");
    $z = mysql_query("SELECT SUM(Gallons) AS TotalGallons FROM fuel");
    while ($x_array = mysql_fetch_array($x)){$MilesStart[] = $x_array;}
    while ($y_array = mysql_fetch_array($y)){$MilesLast[] = $y_array;}
    while ($z_array = mysql_fetch_array($z)){$TotalGallons[] = $z_array;}
    $TOTALMPG='($x-$y)/$z'
    ?>
    Alternatively I may want to show total miles and have a new field that is odometer reading last - odometer reading first = total miles field.. is that possible? Then I could just use..

    Code:
    SELECT SUM(fuel.miles/fuel.gallons)
    right? 
    or not?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Nothing to do with MySQL. You don't know how to use PHP to connect to MySQL and make queries.

    Just to illustrate one of your queries and what to do:
    Code:
    $x = mysql_query("SELECT MIN(OdometerReading) AS MilesStart FROM fuel") 
         or die( mysql_error() );
    $rows = mysql_fetch_assoc($x);
    $MilesStart = $rows["MilesStart"];
    HOWEVER...There is no reason at all to do three separate queries and no reason at all that you couldn't do the MPG calculation in MySQL, if you'd like.

    Example:
    Code:
    SELECT ( MAX(OdometerReading) - MIN(OdometerReading) ) / SUM(Gallons) AS MPG
    FROM fuel
    If you want all five meaningful values:
    Code:
    SELECT MAX(OdometerReading) AS finalMileage,
           MIN(OdometerReading) AS startMileage,
           SUM(Gallons) AS fuelUsed,
           MAX(OdometerReading) - MIN(OdometerReading) AS totlaMiles, 
          ( MAX(OdometerReading) - MIN(OdometerReading) ) / SUM(Gallons) AS MPG
    FROM fuel
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Mar 2013
    Location
    Lehigh Acres, FL, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    You rock!

    Just saying you totally rock. I am so new at coding anything so this is very refreshing to have this work! I used option 3 and it looked great.. however one little hiccup.. It only shows the first bus. I have 157 busses I would like to show the MPG for.. I was thinking I might need presql php code??

    Like this..
    Code:
    foreach($rows as $row) { 
         foreach($row as $field <> $0) { 
    
         } 
    }

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    LOL! Actually, that was *NOT* showing the "first bus". It was showing the totals and averages for *ALL* buses!

    It's a peculiarity of MySQL that it allows you to do SUM and MAX and so on queries (called "aggregate functions", by the by) *without* requiring that you get the GROUP BY correct. Any other DB would have given you an error.

    SO...

    Since you didn't bother to show the fields in your table, I'll make a guess:
    Code:
    SELECT busNumber, driverName, 
           MAX(OdometerReading) AS finalMileage,
           MIN(OdometerReading) AS startMileage,
           SUM(Gallons) AS fuelUsed,
           MAX(OdometerReading) - MIN(OdometerReading) AS totlaMiles, 
          ( MAX(OdometerReading) - MIN(OdometerReading) ) / SUM(Gallons) AS MPG
    FROM fuel
    GROUP BY busNumber, driverName
    ORDER BY busNumber
    The prinicipal is simple: Any *NON* aggregate fields in your SELECT *must* then be mentioned in your GROUP BY.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    And I don't really use PHP, but you would then want something like this:
    Code:
    <table border="1" cellpadding="3">
    <tr>
       <th>bus #</th><th>driver</th><th>start miles</th><th>end miles</th>
       <th>miles driven</th><th>fuel used</th><th>MPG</th>
    </tr>
    <?PHP
    ... make your db connection ...
    $sql = "
    SELECT busNumber, driverName, 
           MAX(OdometerReading) AS finalMileage,
           MIN(OdometerReading) AS startMileage,
           SUM(Gallons) AS fuelUsed,
           MAX(OdometerReading) - MIN(OdometerReading) AS totalMiles, 
          ( MAX(OdometerReading) - MIN(OdometerReading) ) / SUM(Gallons) AS MPG
    FROM fuel
    GROUP BY busNumber, driverName
    ORDER BY busNumber";
    
    $result = mysql_query( $sql ) or die( mysql_error() );
    while ( $row = mysql_fetch_assoc($result) )
    {
        $bus = $row["busNumber"];
        $driver = $row["driverName"];
        $start = $row["startMileage"];
        $finish = $row["finalMileage"];
        $miles = $row["totalMiles"];
        $fuel = $row["fuelUsed"];
        $mpg = $row["MPG"];
       
        echo "<tr><td>$busNumber</td><td>$driver</td><td>$start</td>";
        echo "<td>$finish</td><td>$miles</td><td>$fuel</td><td>$mpg</td></tr>\n";
    
    }
    ?>
    </table>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    By the by, if you would prefer, you could order the buses by MPG.

    That is, change
    Code:
    ORDER BY busNumber
    to (say) best MPG first:
    Code:
    ORDER BY MPG, busNumber
    or worst MPG first:
    Code:
    ORDER BY MPG DESC, busNumber
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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