This topic is locked
[SOLVED]

 ListQuery and ListFetchArray

1/29/2016 1:02:59 PM
PHPRunner General questions
C
chrism author

Looking for some guidance in how to use ListQuery and ListFetchArray.
I have a table of records indicating jobs and the personnel working on them. I'd like to order the records such that the records which pertain to the user are put at the top of the page. I believe that I'd accomplish this with the Custom query and Fetch records pages of the list page, but I'm unsure how to use them.

Sergey Kornilov admin 2/2/2016

ListQuery and ListFetchArray fucntions serve totally different purpose. They are mostly useful when you need to display some free form data that cannot be result of SQL query i.e. list of records received from some other datasource or web service.
What you looking for is replaceField function of SQLQuery object:

http://xlinesoft.com/phprunner/docs/replacefield.htm
Here is what needs to be done.
Here is your original SQL Query as it appears on 'SQL Query' screen.

SELECT

id,

item

FROM tablename


Modify SQL Query
Add a new calculated field to SQL query named 'newfield'

SELECT

id,

item,

0 as newfield

FROM tablename

order by newfield desc


AfterTableInit event
In this event we replacing 'newfield' with expression that will be 1 for records that belong to current user and 0 otherwise.

$query->replaceField("newfield","userid='" . $_SESSION["UserID"] ."'");


Here we assume that name of the field that stores userid is 'userid' and that this field is a text field.
SQL Query during execution

SELECT

id,

item,

userid='admin' as newfield

FROM tablename

order by newfield desc


userid='admin' is a calculated field that will be 1 for those records where userid is 'admin' and will be zero for all other records. Since we sort our data by this newfield descending all admin records will appear on the top of the list.

C
chrism author 2/3/2016



ListQuery and ListFetchArray fucntions serve totally different purpose. They are mostly useful when you need to display some free form data that cannot be result of SQL query i.e. list of records received from some other datasource or web service.
What you looking for is replaceField function of SQLQuery object:

http://xlinesoft.com/phprunner/docs/replacefield.htm
Here is what needs to be done.
Here is your original SQL Query as it appears on 'SQL Query' screen.

SELECT

id,

item

FROM tablename


Modify SQL Query
Add a new calculated field to SQL query named 'newfield'

SELECT

id,

item,

0 as newfield

FROM tablename

order by newfield desc


AfterTableInit event
In this event we replacing 'newfield' with expression that will be 1 for records that belong to current user and 0 otherwise.

$query->replaceField("newfield","userid='" . $_SESSION["UserID"] ."'");


Here we assume that name of the field that stores userid is 'userid' and that this field is a text field.
SQL Query during execution

SELECT

id,

item,

userid='admin' as newfield

FROM tablename

order by newfield desc


userid='admin' is a calculated field that will be 1 for those records where userid is 'admin' and will be zero for all other records. Since we sort our data by this newfield descending all admin records will appear on the top of the list.


Thanks again Sergey. You're the Best.

C
chrism author 2/3/2016

So, a followup. Suppose there's multiple userid fields, userid1 and userid2 lets say. I'd like to replace both of those fields in a query. Does this method support that? I'd like to extend this to 60-odd fields.

Sergey Kornilov admin 2/3/2016

Yes, you can do that for multiple fields. Not sure why would you have 60 different userid fields in the same table though.

C
chrism author 2/4/2016



Yes, you can do that for multiple fields. Not sure why would you have 60 different userid fields in the same table though.


So I must be doing something wrong, as only the first field (mech_eng_id) is being replaced. How do I extend this to replace many fields?



$query->replaceField("newfield","mech_eng_id='" . $_SESSION["ID"] ."'");

$query->replaceField("newfield1","pm_id='" . $_SESSION["ID"] ."'");

$query->replaceField("newfield2","mech_eng2_id='" . $_SESSION["ID"] ."'");