2. PHPRunner General questions
  3. Milage Question
This topic is locked

Milage Question

 		2/1/2017 6:30:21 AM
PHPRunner General questions
Tandy author

Hello all,

Have a question and if it can be done.

I have a field made up with Odometer Readings. Then another field made up for Mileage.

What I would like to see if can be done is take that columns odometer reading and subtract from the last odometer reading and auto post into the mileage field?
Here is a better lay to see if I can get help..

There are a few things I need to try and figure out. I have a table called report. The layout is below with how I would like to make it look. I would like to see if I can take the odometer reading and subtract from the last line and auto fill in at mileage but if the member adds how many miles in tolls then the non_toll auto recalculates as below.
+--+--------+-------------+------+----------+

| id | state | odometer | tolls | mileage |

+--+--------+-------------+------+----------+

| 1 | TN | 209538 | 0 | 0 |

+--+--------+-------------+------+----------+

| 2 | TN | 209929 | 0 | 391 |

+--+--------+-------------+------+----------+

| 3 | AR | 210207 | 0 | 278 |

+--+--------+-------------+------+----------+

| 4 | TX | 210820 | 13 | 600 |

+--+--------+-------------+------+----------+

| 5 | NM | 211198 | 32 | 346 |

+--+--------+-------------+------+----------+
I have already figured out where I put in tolls it take the tolls away from the mileage with adding:

$values["mileage"] = $values["odometer"] - $values["tolls"];

to Add page - Before record added.
Just need to see if I can subtract the Odometer Readings and add that total in mileage.
I am running PHPRunner Enterprise 9.6 (Build 27826 x64)

Thank you very much for any kind of input on this one.

James
mbintex 2/1/2017

quite similar
http://www.asprunner.com/forums/topic/21091-implementing-running-total-in-phprunner/
Tandy author 2/1/2017



quite similar
http://www.asprunner...l-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.
Tandy author 2/2/2017

Guess the only way to do it is self join? Now trying to figure out the self join process.
Tandy author 2/2/2017

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.
Sergey Kornilov admin 2/3/2017

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.
Tandy author 2/4/2017

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
HJB 2/4/2017

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