Changing SQL query of the Browse Form

Questions related to customising nuBuilder Forte with Javascript or PHP.

Changing SQL query of the Browse Form

Postby Janusz » Thu Jul 18, 2019 2:37 am

Hi,
I would like to change the SQL browse FORM based on variable from JS (for example limit data displayed based on user name or department name)

for ex.: initially in the SQL Browse Form Properties I have:
Code: Select all
SELECT storage_id, sto_ref_nr, sto_name, sto_memo, sto_resp FROM storage


and from JavaScript I would like to update this with:
Code: Select all
nuSetProperty('browse_sql', "SELECT storage_id,sto_ref_nr,sto_name,sto_memo,sto_resp from storage WHERE sto_resp='John'");


In the CONSOLE I can see that this property is changed - but it does not affect at all the browse data displayed.

Do you think should this work? or maybe any suggestion how can I do it?
Janusz
 
Posts: 257
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Changing SQL query of the Browse Form

Postby admin » Thu Jul 18, 2019 7:33 am

Janusz,

If you run this first

Code: Select all
nuSetProperty('responsible', 'Bob');


You can create a temp table in Before Browse (#TABLE_ID#) and use that.

Code: Select all
$s = "
CREATE TABLE #TABLE_ID#
SELECT storage_id, sto_ref_nr, sto_name, sto_memo, sto_resp
FROM storage
WHERE sto_resp = '#responsible#'
";

nuRunQuery($s);


And swap the Form's SQL to use the new table.

table_id.JPG
table_id.JPG (16.35 KiB) Viewed 744 times



Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: Changing SQL query of the Browse Form

Postby Janusz » Thu Jul 18, 2019 8:37 am

Steven, thanks a lot.
It's working :-).
There is one small issue - when you initlally opening the browse form it's empty.
To have data in the from you need to use refresh or just to press column title like for sorting - and after this initial let say conditioning it works OK.
So probably I will implement a kind of refresh just after form opening.
Janusz
 
Posts: 257
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Changing SQL query of the Browse Form

Postby admin » Fri Jul 19, 2019 7:32 am

Janusz,

If it's blank to start with I guess it is because you haven't had a chance to set the Hash Cookie.

Maybe if you include that possibility in your SQL.

Code: Select all
$s = "
CREATE TABLE #TABLE_ID#
SELECT storage_id, sto_ref_nr, sto_name, sto_memo, sto_resp
FROM storage
WHERE sto_resp = '#responsible#'
OR '#responsible"."#' = '#responsible#'
";


OR '#responsible"."#' = '#responsible#'

Notice the string is joined together so that '#responsible#' doesn't get replaced twice.

Steven
admin
Site Admin
 
Posts: 3162
Joined: Mon Jun 15, 2009 9:53 am

Re: Changing SQL query of the Browse Form

Postby Janusz » Fri Jul 19, 2019 9:39 am

Steven,
Thank you very much for your great support and suggestions - which was really very helpful for me.
So after the first quick trial when I had the issue with initiating the table - and after some more tests later - finally I moved the whole code to BB php and I am setting the data based on user name and everything works pefectly :-)

With the following code I can separate data per every user (so everyone has an access only to his data) and for admin I have an access to the full table :-)

Code: Select all
$object = nuUser();
$usr=$object->sus_name;
if ($usr=="") $usr="admin";

$s = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
WHERE rej_created_by = '$usr'
ORDER BY rej_created_on DESC
";

$s1 = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
ORDER BY rej_created_on DESC
";

if ($usr=="admin") {nuRunQuery($s1);} else {nuRunQuery($s);}
Janusz
 
Posts: 257
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Changing SQL query of the Browse Form

Postby Janusz » Fri Jul 19, 2019 10:10 am

and I checked the following on my original case from the initial post
Code: Select all
......
OR '#responsible"."#' = '#responsible#'

and with that the situation is following that initially I get the full browse form with all data displayed and after any operation on the form - data is limited as requested by query.
But anyway for the moment the filtering based on the user name - as I described in my previous post - completely solved my problem :-)
Janusz
 
Posts: 257
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Changing SQL query of the Browse Form

Postby kev1n » Fri Jul 19, 2019 1:00 pm

Janusz wrote:Steven,
Code: Select all
$object = nuUser();
$usr=$object->sus_name;
if ($usr=="") $usr="admin";

$s = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
WHERE rej_created_by = '$usr'
ORDER BY rej_created_on DESC
";

$s1 = "
CREATE TABLE #TABLE_ID#
SELECT * FROM rejestr
ORDER BY rej_created_on DESC
";

if ($usr=="admin") {nuRunQuery($s1);} else {nuRunQuery($s);}


A bit more compact:

Code: Select all
$usr = nuUser()->sus_name;

// No WHERE clause if admin
$where = ($usr == "") ? "" : " WHERE rej_created_by = '".$usr."' ";

$qry = "
   CREATE TABLE #TABLE_ID#
   SELECT * FROM rejestr"
   .$where."
   ORDER BY rej_created_on DESC
";

nuRunQuery($qry);
kev1n
 
Posts: 359
Joined: Mon Oct 15, 2018 2:13 am

Re: Changing SQL query of the Browse Form

Postby Janusz » Fri Jul 19, 2019 3:02 pm

Hi Kevin,
Thanks for sharing - this short code is working :-)
Appreciate very much your post - it's always very interesting to see some improvements which can be implemented to the code.
Janusz
 
Posts: 257
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Changing SQL query of the Browse Form

Postby nc07 » Thu Jul 25, 2019 8:44 am

Hi,

I have tried the code suggested by Kevin and Januz. I have multiple sites like LTK or NAN which is recorded through java and works fine. All site have site managers, so I need managers to view all staffs record for his site (individuals viewing their own record is working fine form me) and the General Manager (GM) to view all records from all sites. The user access level reads something like staff-ltk or staff-nan and for managers like mng-ltk or mng-nan. I need help here, please.
nc07
 
Posts: 21
Joined: Tue Jun 04, 2019 11:35 am

Re: Changing SQL query of the Browse Form

Postby Janusz » Thu Jul 25, 2019 2:52 pm

Hi,
Maybe this example can be helpfull - just tested and it's working well. You can use it from web browser - exactly like in attached example or create similar php code for the nuBuilder (but did not test from nuBuilder)
https://www.ionos.com/community/hosting/mysql/use-php-to-retrieve-information-from-a-mysqlmariadb-database/
when using this example directly please be carefull with security issue because such file can be easilly downloaded by anyone having the link with wget and there is password inside.
Janusz
 
Posts: 257
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Next

Return to Custom Code