Multi-Row Calculations (Miles Per Gallon)

[soulfire]

Greetings friends!

I’d like to preface this by saying I’m quite new to php, but I’ve come to love it so far. I’ve never worked with php or html (or javascript, really) until a month ago, and I’m really interested in advancing my knowledge of the subject.

So, my question: I need to calculate miles per gallon given two entries from a table.

I have a table named fuelups with columns that contain an entry for odometer reading and the amount of fuel added to a vehicle. Specifically, it would be fuelups.odometer and fuelups.gallons

What needs to happen is:

1. Nothing if there is only 1 record [as it requires two points to generate a number]

2. If 2+ records are found, pull the recent two (they are date/timestamped by fuelups.date and are assigned to a user id fuelups.UID) and perform the mpg calculation and store it in the most recent record.

I’ve not much clue about how to do calculations across two records, so a general concept works and then we can perhaps get into specific code?

Thank you in advance for your assistance!

[m11]

Post your code. You have to try before asking for help

[soulfire]

Well, we will start here then. I’ve got a syntax error near the ORDER BY date LIMIT 2 clause. I decided to use this because I need to pick the most recent two fuelups. What I decided to do was select the two most recent fuelups and store the odometer readings and fuel amounts of each entry into an array.

From the array, I will draw and assign the pieces of information to variables, do the calculation, and insert the final calculated number into the database in the appropriate location.

Relevant info:
sql.php contains the database connection information

[php]<?php
include(“sql.php”);
$stmt = “SELECT odometer, gallons FROM fuelups WHERE UID = " .$_SESSION[‘UID’]. " ORDER BY date LIMIT 2”;
$result = mysql_query($stmt) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
	$mpg_array[] = $row;
}

?>[/php]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY date LIMIT 2' at line 1

Also, I’m very new to php, looking to learn it because it’s so useful!

[soulfire]

Well, after I posted that and read through it again I changed a few things (changes made in bold):
[php]<?php
include(“sql.php”);
$stmt = “SELECT date, odometer, gallons FROM fuelups WHERE UID = " .$_SESSION[‘UID’]. " ORDER BY date DESC LIMIT 2”;
$result = mysql_query($stmt) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
	$mpg_array[] = $row;
}

?>[/php]

Kinda hard to order by a field you didn’t select. Oops :-[

Now, no SQL error (I think) but this:

Fatal error: Cannot use [] for reading in /home/a7201959/public_html/insertfuelup.php on line 8

[m11]

What is $mpg_array? Did you define it as an array first? e.g. $mpg_array = array();

[soulfire]

I think I got it to work after playing around with it a bit, here’s my final code:

[php]$stmt = “SELECT date, odometer, gallons, fuelup_id FROM fuelups WHERE UID= '” . $_SESSION[‘UID’] . “’ ORDER BY time DESC LIMIT 2”;
$result = mysql_query($stmt) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
$mpg_array[] = $row;
}

$result = count($mpg_array);
echo $result;
if ($result == 2) {

	$calcmpg = round(calculateMPG($mpg_array[0]['odometer'], $mpg_array[1]['odometer'], $newgallons = $mpg_array[0]['gallons']),2);

	$stmt2 = "UPDATE fuelups SET mpg = $calcmpg WHERE fuelup_id = '" . $mpg_array[0]['fuelup_id'] . "'";
	mysql_query($stmt2) or die(mysql_error());

}

header(“Location: member.php”);

function calculateMPG($newodometer, $oldodometer, $newgallons){
$mpg = ($newodometer - $oldodometer) / ($newgallons);
return $mpg;

}[/php]

So, basically, what it does is read in the two most recent records. These records are assigned to the array $mpg_array. The necessary parameters (new odometer reading, old odometer reading, and gallons of gas) are passed to the function calculateMPG which then returns the miles per gallon based on these. The MPG is written into the more recent fuelup.

The if $result == 2 is checking to make sure 2 results are returned, if there is only 1 then it was the first entry and MPG is not calculated until another is added.