Custom function to filter a select_single options driven from a lookup table

[leone]

Dear Eugenio, hi!

I am looking for your advise because apparently when the application run the custom functions the global variables related to logged in users and connection are not yet declared or determined.

I'm building an application where I want that a group of users can select_single from a list which filtered based on their country.

Description:
1 - I added one column "country" to the dadabik_users table.
2 - there is one table, called "beneficiaries", containing a column named "country".
3 - there is another table, called "field_visits", containing a column named "beneficiary_id".

What is currently happening, is that a logged in user (which belongs to a specific group) can create a new record to the "field_visits" table; In the "field_visits" form configurator I set that the beneficiary_id can be chosen based on the beneficiary "full_name" which is driven from the "beneficiaries table".

So far so good!

Now, I want that only beneficiaries of the same country of the logged in user will be showed in the list.

Question #1: since I added one column to the dadabik_user table is by chance any easy way I can get also the "country" of the user as a global variable (so I can directly indicate it in the where clause of the form configurator)?

Alternatively, I am trying solve using a custom function but whatever I think is based on the fact I should know already what is the logged in user ID to retrieve its country and then return this information include it in the where clause of the configurator.

I hope I managed to express my problem and my question properly.
Thank you anyway for all your great work.

Leone

Your current DaDaBIK version​

You are using DaDaBIK version 11.7-Elba enterprise, installed on 23-09-2022 (installation code: 18232632dba4dd7cdc), the latest version of DaDaBIK is 11.7-Elba released on 18-08-2022

You are running the latest release of DaDaBIK

In case you want to upgrade to a more powerful edition (from Pro to Enterprise/Platinum, from Enterprise to Platinum) please contact us.

System info​

PHP Version: 7.3.29

mysql version: 5.5.5-10.3.24-MariaDB

Web server: Apache

Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.0 Safari/605.1.15

 

[eugenio]

Hello Leone,
no, the new fields in the users table are not automatically added as global variables, but of course you can read them.

Maybe the easiest solution is to add, in the custom startup function, some code that reads the country of the current logged in user and then store the country in a global variable (if you want to use it from form config filtered where clause).

When the custom startup function is executed, $current_user is not set yet, so you should check
$_SESSION['logged_user_infos_ar']['id_user']
instead. However, check it first with isset, because before the login and during the login check it is not.

Best,

 

[leone]

Thank you for your reply. It make sense, but I tried to follow your example and I get this server error:
Undefined variable: _SESSION in /var/www/vhosts/mydigital.farm/subdomains/report/httpdocs/include/custom_functions.php on line 108'"

Actually, the following code I the custom_functions.php

function cf_get_user_info(){
if (isset($_SESSION['logged_user_infos_ar']['id_user'])){
return $_SESSION['logged_user_infos_ar']['id_user'];
} else {
return 'undefined variable';
}

}
$cp_user_info = cf_get_user_info();

will always return undefined variable. The user is logged in I am sure.

 

[eugenio]

You should use your code in the dadabik custom startup function (check The custom startup function chapter in documentation).

Best,

 

[leone]

I am sorry, I made several trials, but I can't get it to work. I understand it should be something easy.

I made several trials, perhaps I should ask questions about the documentation. Below is an example from the documentation. Can you explain the reason for the last parenthesis? I didn't use that in my code.

$custom_startup_function = 'dadabik_startup';
function dadabik_startup (){
... your custom code here ...
}
} //<-- what is this parenthesis doing here?

On top of that, I don't understand another concept: if the function is not called (but executed every time a page is loaded) how do I get the variable returned?

Thank you,
Leone

 

[eugenio]

You are right there is one additional (wrong) parenthesis. Fixed.

You don't have to return anything, you can just store the value in a global variable, something like:

$GLOBALS['_cp_country'] = 'Italy';

You should then be able to use $GLOBALS['_cp_country'] in your filter.

Best,

 

[leone]

Thank you it works very well now!
Leone

 

[leone]

Now I am getting a new, strange error!

In the forms configurator at the where clause I set:

country = dadabik_var _cp_country_user dadabik_var

(the global value set by the startup_function is $GLOBALS['_cp_country_user']

and I get this error

[08] Error: during query execution. SELECT `id`, `full_name`, `region` FROM `beneficiaries` WHERE (1 = 1) AND (country = Uzbekistan)
The DBMS server said: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Uzbekistan' in 'where clause'

The name of the country Uzbekistan is correct, but why is it looking for a column with that name?

I observed that if in the where clause I write: country = 'Uzbekistan'
then, all work well.

I need your support again to understand, I guess?

 

[eugenio]

You missed the quotes

country = 'dadabik_var _cp_country_user dadabik_var'

SQL security hint: double check if country user is a value you control (you take it from the db and you store it on the db) and not a user-generated value