2. PHPRunner General questions
  3. Store SQL generated value into table
This topic is locked

Store SQL generated value into table

 		2/16/2014 3:11:11 AM
PHPRunner General questions
F
 F5447 author

Dear ALL expert of PHPRunner,
This is my first post of PHPRunner question, in which I am very new to PHPRunner ...
My question:
I have a table, tblNumber, in which it has many fields (more than as per given here as example) need to store SQL generated value through PHPRunner [List Page]. In brief, the tblNumber has fields of:
tblNumber.ID

tblNumber.Number

tblNumber.FirstD

tblNumber.SecondD

tblNumber.ThirdD

tblNumber.ForthD
My [List Page] SQL statement is



SELECT

ID,

LPAD(Number,4,'0') AS zNumber,

SUBSTRING(LPAD(Number,4,'0'), 1, 1) AS zFIRSTD,

SUBSTRING(LPAD(Number,4,'0'), 2, 1) AS zSECONDD,

SUBSTRING(LPAD(Number,4,'0'), 3, 1) AS zTHIRDD,

SUBSTRING(LPAD(Number,4,'0'), 4, 1) AS zFOURTHD

From

tblNumber


If my Number value in tblNumber has 1234, 5678, 9012, the followings are the [List Page] display through PHPRunner



ID zNumber zFIRSTD zSECONDD zTHIRDD zFOURTHD

1 1234 1 2 3 4

2 5678 5 6 7 8

3 9012 9 0 1 2


My question is how can I store SQL generated value into fields of tblNumber.FirstD, tblNumber.SecondD, tblNumber.ThirdD and tblNumber.ForthD in PHPRunner [List Page], during execution of [List Page], etc?
Purpose: I have many fields (not only the above 4 fields as example) to store with SQL generated value (which I already successfully make through SELECT statement of MYSQL).

Now the issue how to store them back to MYSQL table. I would like these fields stored, because I will have another [List Page] to extract these fields for data analysis.
Thanks in advance.

F5447 (Chin)
F
 		F5447 author 2/16/2014

Dear All,
It seems like I got the answer, but just would like to have all PHPRunner expert to confirm ...
First Step: I generated a Simple [List Page] ... with the following SELECT Statement ...

SELECT

ID,

LPAD(Number,4,'0') AS Number

From

tblNumber


After that, I make another more advance [List Page] SELECT Statement as follows:

SELECT

ID,

LPAD(Number,4,'0') AS Number,

SUBSTRING(LPAD(Number,4,'0'), 1, 1) AS FIRSTD,

SUBSTRING(LPAD(Number,4,'0'), 2, 1) AS SECONDD,

SUBSTRING(LPAD(Number,4,'0'), 3, 1) AS THIRDD,

SUBSTRING(LPAD(Number,4,'0'), 4, 1) AS FOURTHD

From

tblNumber


Instead of zFIRSTD ...use exact the table field name of FIRSTD
After that, each time I run advance [List Page], I need to click on <Edit> and then re-save the data ... and I check the database ... correct data were stored in the database ...
Question to PHPRunner Expert ...
Is this way OK? reliable? This is because I have a huge number of data need to be inserted into their fields ...

Any other simple/fast way?
Thanks.

F5447 (Chin)
F
 		F5447 author 2/16/2014

Dear All,
I tested, and it should be working ... BUT,
When I want to edit existing data, say the tblNumber.Number from a number to another new number, I need to change the data, and then re-edit the data (this need to be done twice). This is because says: I want to edit existing of 1234 to 7788
List Page >

SELECT

ID,

LPAD(Number,4,'0') AS Number,

SUBSTRING(LPAD(Number,4,'0'), 1, 1) AS FIRSTD,

SUBSTRING(LPAD(Number,4,'0'), 2, 1) AS SECONDD,

SUBSTRING(LPAD(Number,4,'0'), 3, 1) AS THIRDD,

SUBSTRING(LPAD(Number,4,'0'), 4, 1) AS FOURTHD

From

tblNumber



When this SELECT Statement first run ... the data is as follows:



ID Number FIRSTD SECONDD THIRDD FOURTHD

1 1234 1 2 3 4



But when I change the Number from 1234 to 7788 (I do not change fields of FIRSTD, SECONDD, THIRDD and FOURTHD) ... System store tblNumber.Number to 7788 (but did not update fields of FIRSTD, SECONDD, THIRDD and FOURTHD.
Therefore, I need to re-edit the through [List Page], in order I get the following data to be listed.



ID Number FIRSTD SECONDD THIRDD FOURTHD

1 7788 7 7 8 8



After that I need to <save> the [List Page].
Question:
Any better way, in order I do not need to re-edit (step 2)?
Thanks in advance.

F5447 (Chin)