Milage Question

quite similar
http://www.asprunner.com/forums/topic/21091-implementing-running-total-in-phprunner/

Either I set it up wrong or that is not really what I am trying to do.
It is showing:
ID | Odometer | Tolls | Mileage

1 | 20 | 0 | 20

2 | 40 | 5 | 40

3 | 60 | 0 | 60
What I was looking for is:
ID | Odometer | Tolls | Mileage

1 | 20 | 0 | 0

2 | 40 | 5 | 15

3 | 60 | 0 | 20
Row ID 1 will be the starting mileage.

Row ID 2 is leaving the state. which was 20 miles but 5 miles toll so the mileage none toll was 15

Row ID 3 is leaving the next state, which was 20 miles but no tolls so my none toll mileage is 20
I had the code working for tolls but I can not figure out how to take ID 2 - Odometer field to subtract from ID 1 Odometer field and post the difference in ID 2 Mileage field.

Then take ID 3 Odometer field and subtract ID 2 Odometer field and post that to ID 3 Mileage field. If I can figure that one out, I think the code:
$values["mileage"] = $values["odometer"] - $values["tolls"];

to Add page - Before record added.
Will work again for the tolls.

Guess the only way to do it is self join? Now trying to figure out the self join process.

I can add the odometer reading if I add this code:
$rs = CustomQuery("SELECT sum(odometer) AS miles FROM mileage WHERE id <= '".$data['id']."'");

$record = db_fetch_array($rs);
$value = $record['miles'];
In custom views. Is there a way to subtract instead of add? I have tried min instead of sum but that did not work.

In my understanding you are trying to implement a running total, where result in each row depends on previous row data.
So link provided by mbintex is what you looking for. It is just your calculation will be a bit more complicated as you need to calculate the difference between current and previous readings of odometer and subtract tolls value.

I tried and tried to do that post for days. I can get everything to add but not subtract.
I found this post at: sql - MySQL
And put this as my Query:

SELECT

this.id,

this.odometer,

this.tolls,

(this.odometer - prev.odometer) as miles

FROM mileage this

INNER JOIN mileage prev on prev.id =

(

SELECT max(t.id)

FROM mileage t

WHERE t.id < this.id

)
This does work but keeps crashing PHPRunner?

Is there an error in this code that crashes PHPRunner?
Thank you all for all the help you are trying to give.

James Tandy

For inspiration purposes only ...

https://xlinesoft.com/phprunner/docs/how_to_calculate_values_on_the_fly.htm

respectively on field "mileage" oriented wished content:

"price" = odometer

"quantity" = toll

"total" = mileage