AppGini Community Forums

Apologies if this has been asked, but I cannot find it
I'll pose my question, with hypothetical demo, as I'm finding it difficult to articulate, so - I have an application, as follows
- On user registration, I collect the SURNAME of new sign-up (stored in membership_users table in custom2 field AND accessible via PHP using the $memberInfo['custom'][1] reference)
- There is a table called CONTRACTS, which has a field called SURNAME
- There is a table called PROGRESS, which has a field that looks up the CONTRACTS

I want to edit the lookup query (using the advanced mySQL edit functionality), so as to only select
- records from CONTRACTS
- where SURNAME matches the custom2/$memberInfo['custom'][1] (i.e. the surname of the signed in user)

The query is not an issue - how do I reference custom2 in the query (For clarity, I can do it in hooks using PHP, i.e. $memberInfo['custom'][1])

SELECT ... WHERE `CONTRACTS`.`Surname`='???' ORDER BY 2

TIA, James

Hi,

I don't think this is possible through the customization (advance SQL) as the memberInfo is not available at the time you need it.

Thanks Pbottcher, I was afraid that might be the answer
I wonder if it is possible to put in some sort of pattern and do preg_replace in a hook - any thoughts?

I have rejigged this app, in the hope of achieving the same result. In effect, I have switched the search string from the SURNAME to the MEMBERID (as it is always stored in $_SESSION['memberID], but I still have a problem that I hope may be resolvable

In the AppGing advanced mySQL editor for the field, I have entered:
SELECT ... WHERE `addresses`.`Username`="'.$_SESSION[memberID].'" ORDER BY 2
This creates a query in the AJAX_COMBO.php file
'SELECT ... WHERE `addresses`.`Username`="\'.$_SESSION[memberID].\'" ORDER BY 2'

The problem arises because the ' is escaped (\). When I manually edit the line in AJAX_COMBO.php, the script works
'SELECT ... WHERE `addresses`.`Username`="'.$_SESSION[memberID].'" ORDER BY 2'

Clearly, a manual edit in AJAX_COMBO is problematic (as it is not in a hook, etc). Does anyone know how I could write the query in the AppGini advanced mySQL editor so as I could get a working string to be created in the AJAX_COMBO.php file

Thanks in advance, James

The .$_SESSION[memberID]. part of the code is PHP code .. unfortunately, you can only use SQL code in the advanced query section. I'd consider implementing some kind of a placeholder to handle this case in future releases so you could for example write the query like this:

Thank you, I look forward to that

Hi Ahmed
Have you scheduled this development into your road map yet - expected date?
Very best,
James

Yes, it's planned now for AppGini 5.80, hopefully before the end of April.

Class, this will Add significantly to AppGini, which is already powerful. TIA

May I ask whether the placeholder %%memberID%% in the advanced lookup query is workable?

Is there any other type of placeholders?

a.gneady wrote: ↑

2019-03-22 13:58

Yes, it's planned now for AppGini 5.80, hopefully before the end of April.

Hey Ahmed, was this feature released? Can you advise further on any posts, tweets or blogs with this?

Unfortunately not :/
I know it's a very long time .. but the feature didn't make it into 5.80 at the time because while experimenting with it, it caused several issues that needed more planning to carefully implement. I've taken note of this now and will work in it in upcoming releases. Sorry for that.

That's ok, I appreciate the feedback.

Hi Ahmed, did you ever get around to this? It keeps coming around for me. James

Hi Ahmed, did you ever get around to this? It keeps coming around for me. My current need is as follows (1) user creates an address, but may mark it inactive, and (2) subsequently, user creates an asset and may select that address for the asset, or any other address he has created.
A costom mysql statement can be used to return active addresses, OR the default mysql with 'inherit permissions' to return the users own records. I cant seem to combine both. James

Ahmed?