2. PHPRunner General questions
  3. [SOLVED]  Copy last record to new record
This topic is locked
[SOLVED]

 Copy last record to new record

 		8/28/2019 7:12:45 AM
PHPRunner General questions
Tandy author

Hello all,Hope I can get a little info from someone.

What I am trying to do is take the last row field end_odometer and have it auto populate the next row to start_odometer. What would be the best way of do that? Example below
id | start | end

1 | 1000 | 1500

2 | 1500 | 1567

3 | 1567 | 2001

4 | 2001 | 2523

And so on
Thanks A lot for any kind of help on this.

James Tandy
D
 		david22585 8/28/2019

Try something like this:



$sql = "SELECT(end) AS endmileage FROM TABLE_NAME ORDER BY id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['start'] = $data["endmileage"];


I'm assuming that this will be a field that is not entered, and you're only entering the end mileage. If you want to add one more step in and have mileage calculated, you can use the same code in a different context:

$sql = "SELECT(end) AS start FROM TABLE_NAME ORDER BY id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['totalmileage'] = $values["end"] - $data["start"];
Tandy author 8/29/2019



Try something like this:



$sql = "SELECT(end) AS endmileage FROM TABLE_NAME ORDER BY id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['start'] = $data["endmileage"];


I'm assuming that this will be a field that is not entered, and you're only entering the end mileage. If you want to add one more step in and have mileage calculated, you can use the same code in a different context:

$sql = "SELECT(end) AS start FROM TABLE_NAME ORDER BY id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['totalmileage'] = $values["end"] - $data["start"];




Thank You for your help david22585. I tried adding the code to Events - Add Page Before Process. It did not fill in the end_odometer from the start_odometer. Here is the code I tried:

$sql = "SELECT(end_odometer) AS start_odometer FROM trip_report ORDER BY trip_report_id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['start_odometer'] = $values["end_odometer"];
Tandy author 8/29/2019

Question. Is there a way to put a code in the fields as custom to auto populate the start_odometer with the last trip_report_id end_odometer? So that way if the odometer has changed for some reason they can replace the start_odometer with the correct start_odometer?
Thanks again for any help..
S
 		smez 8/30/2019

Your last line is incorrect. It should be:

$values['start_odometer'] = $data["start_odometer"];




Thank You for your help david22585. I tried adding the code to Events - Add Page Before Process. It did not fill in the end_odometer from the start_odometer. Here is the code I tried:

$sql = "SELECT(end_odometer) AS start_odometer FROM trip_report ORDER BY trip_report_id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['start_odometer'] = $values["end_odometer"];
Tandy author 8/30/2019

Thank You smez,

Just tried and still it is not putting anything in the start_odometer from the end_odometer. I am wondering maybe if the code is right if I am not putting it in the right place. I am adding:

$sql = "SELECT(end_odometer) AS start_odometer FROM trip_report ORDER BY trip_report_id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['start_odometer'] = $data["end_odometer"];

to the add page :: Before process and I tried Add page :: Before Display. Nether one put the odometer in?

Thank You guys from trying to help me out on this..

Your last line is incorrect. It should be:


$values['start_odometer'] = $data["start_odometer"];
S
 		smez 8/30/2019

You copied my suggestion incorrectly. Since you extracted the value using sql "AS start_odometer" - you must reference it using the $data variable as in my example.



Thank You smez,

Just tried and still it is not putting anything in the start_odometer from the end_odometer. I am wondering maybe if the code is right if I am not putting it in the right place. I am adding:

$sql = "SELECT(end_odometer) AS start_odometer FROM trip_report ORDER BY trip_report_id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['start_odometer'] = $data["end_odometer"];

to the add page :: Before process and I tried Add page :: Before Display. Nether one put the odometer in?

Thank You guys from trying to help me out on this..
D
 		david22585 8/30/2019

That code would insert the field into the database, not fill it in automatically into the box. I haven't tried the code, but try something like this:
Events -> Before Proces

global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["start_odometer"] = $data["end_odometer"];

}


On the start_odometer field on the designer page, set it to whatever field you want or even read only, and set the default value to:

$_SESSION["start_odometer"]
Tandy author 8/30/2019

Thank you david22585,That worked out great.. Does what I need it to do..

Thank You guys very much for all your help and knowledge.James Tandy

That code would insert the field into the database, not fill it in automatically into the box. I haven't tried the code, but try something like this:

Events -> Before Proces

global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["start_odometer"] = $data["end_odometer"];

}


On the start_odometer field on the designer page, set it to whatever field you want or even read only, and set the default value to:

$_SESSION["start_odometer"]
Tandy author 8/30/2019

Is there a way to lock it down to just that account? Something like this work?



global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report where trip_id=".$values["trip_id"]." ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["start_odometer"] = $data["end_odometer"];

}



Also How long is that session good for? They will not be adding fields in every day. Might be every two days but others will be adding theirs in.

Thank You again
D
 		david22585 8/31/2019



Is there a way to lock it down to just that account? Something like this work?



global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report where trip_id=".$values["trip_id"]." ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["start_odometer"] = $data["end_odometer"];

}



Also How long is that session good for? They will not be adding fields in every day. Might be every two days but others will be adding theirs in.

Thank You again


The session is only good for when they load the add page. Every time the add page is loaded, it will retrieve the data end_odometer and set it as a new session variable just for the add page.
Tandy author 8/31/2019



The session is only good for when they load the add page. Every time the add page is loaded, it will retrieve the data end_odometer and set it as a new session variable just for the add page.

Thank you very much david22585Hmmm.. This will work for now but will have to see if I can lock it done to the truck_id that stays good for that truck.. Do you know where I could find something like that?

ThanksJames
D
 		david22585 9/2/2019



Thank you very much david22585Hmmm.. This will work for now but will have to see if I can lock it done to the truck_id that stays good for that truck.. Do you know where I could find something like that?

ThanksJames


I'm assuming that you want to read the last odometer for a certain truck and use that value, correct?
Tandy author 9/2/2019



I'm assuming that you want to read the last odometer for a certain truck and use that value, correct?


Yes, exactly. I have truck underscore number to lock the odometer readings.

Thank you for all of your help.

James Tandy
D
 		david22585 9/2/2019



Yes, exactly. I have truck underscore number to lock the odometer readings.

Thank you for all of your help.

James Tandy


The biggest thing is how are you accessing the truck value, by a link on the view or list page of a truck, or by a drop down selection on the add page?
Tandy author 9/2/2019



The biggest thing is how are you accessing the truck value, by a link on the view or list page of a truck, or by a drop down selection on the add page?



This table is a details of a master. The link is truck_id and truck_number that is filled in as a new record is made
D
 		david22585 9/2/2019

Try this:

global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report where truck_id = ".$values["truck_number"]." ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["start_odometer"] = $data["end_odometer"];

}


Without seeing the project and how the truck id field is passed along, it makes it a little more complicated.
Tandy author 9/3/2019



Try this:

global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report where truck_id = ".$values["truck_number"]." ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$_SESSION["start_odometer"] = $data["end_odometer"];

}


Without seeing the project and how the truck id field is passed along, it makes it a little more complicated.



Thank You very much for all your help david22585. You helped out so much.. Still learning all this.. Here is the code that works out great. Put in ADD and EDIT pages on the Process Record Values:

// Take end_odometer and auto put into start_odometer - Start

// Will have to put start_odometer field as custom and $values["start_odometer"]

global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report where truck_number = ".$values["truck_number"]." ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$values["start_odometer"] = $data["end_odometer"];

}

// Take end_odometer and auto put into start_odometer - Start

Maybe it can help someone else. Also a big thanks to the Admin on letting me know where it goes.

Thanks All

James
D
 		david22585 9/3/2019



Thank You very much for all your help david22585. You helped out so much.. Still learning all this.. Here is the code that works out great. Put in ADD and EDIT pages on the Process Record Values:

// Take end_odometer and auto put into start_odometer - Start

// Will have to put start_odometer field as custom and $values["start_odometer"]

global $strTableName, $conn;

$strSQLExists = "select end_odometer from trip_report where truck_number = ".$values["truck_number"]." ORDER BY trip_report_id DESC LIMIT 1";

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$values["start_odometer"] = $data["end_odometer"];

}

// Take end_odometer and auto put into start_odometer - Start

Maybe it can help someone else. Also a big thanks to the Admin on letting me know where it goes.

Thanks All

James


Any time. I'm not a programmer at all, just learning all this stuff from searching what others have done and try to make it work for my project with zero programing experience. If others never posted their solutions to problems, I'd be stuck and wouldn't have a clue what I'm doing. Glad to see it worked for you!