Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Calculation of Miles per gallon - HELP!!

1. ## 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. 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. 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```

4. ## 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. 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.

6. 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>```

7. 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`

#### Posting Permissions

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