Calculation of Miles per gallon - HELP!!

• 03-27-2013, 03:37 PM
toconnell
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
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 :)
• 03-27-2013, 03:41 PM
toconnell

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?```
• 03-27-2013, 08:08 PM
Old Pedant
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```
• 03-28-2013, 02:31 PM
toconnell
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) {     } }```
• 03-28-2013, 08:56 PM
Old Pedant
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.
• 03-28-2013, 09:05 PM
Old Pedant
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>```
• 03-28-2013, 09:07 PM
Old Pedant
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`