...

View Full Version : Calculation of Miles per gallon - HELP!!



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



<?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 :)

toconnell
03-27-2013, 03:41 PM
I added this.. still getting error.. UG please help me out here.. this should be simple but its not..




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


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

Old Pedant
03-27-2013, 08:08 PM
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:


$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:


SELECT ( MAX(OdometerReading) - MIN(OdometerReading) ) / SUM(Gallons) AS MPG
FROM fuel

If you want all five meaningful values:


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

toconnell
03-28-2013, 02:31 PM
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..

foreach($rows as $row) {
foreach($row as $field <> $0) {

}
}

Old Pedant
03-28-2013, 08:56 PM
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:


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.

Old Pedant
03-28-2013, 09:05 PM
And I don't really use PHP, but you would then want something like this:


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

Old Pedant
03-28-2013, 09:07 PM
By the by, if you would prefer, you could order the buses by MPG.

That is, change

ORDER BY busNumber
to (say) best MPG first:


ORDER BY MPG, busNumber

or worst MPG first:


ORDER BY MPG DESC, busNumber



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum