[SOLVED] SQL Query runs fine in phpMyAdmin, but not from a Query Auto Action

Hi,

I'm trying to get Auto Actions to run a mySQL query once a user has registered, however the query is either not running, or is running and failing. When I run the query inside of phpMyAdmin, it executes perfectly.

Essentially, the query is taking the username of a newly-registered user, and then updating the username field in a different table in a different database (on the same server) to be equal to this username.

For some more context,



Now we have arrived at (hopefully) a solution for this. I chained a Query auto action underneath the first action which changes the username. This query action has the same trigger (AfterUserRegistration), and uses the following SQL (which works perfectly when run in phpMyAdmin) (Db names edited for ease of reading)

This takes the username field of our new joomla/CB user (which has now been set to our custom username), and sets the username in the moodle database to be equal to it.

However, Having this query chained to be directly after the first auto action (using the same trigger), I cannot get it working.

Even if I use the onBeforeFirstLogin, onAfterFirstLogin and other general Login triggers, then log in, it does still not work and the moodle username stays the same - which leads me to believe that for some reason my query isn't executing inside of the auto action correctly. (Remember that it works perfectly fine when run inside phpMyAdmin).

So that leads me to where I'm needing help - how can I debug this & make sure the query properly executes? (Ideally directly after the first auto action which sets the username field to our custom username)

Alternatively, a potential different solution would relate to my 'Secondly' section in the spoiler tag above. If it was possible to set the username field to our custom username, before anything to do with the new user is saved in the database, joomdle might pick up on it and drag it through straight away - removing the need for this second auto action with the query. However as said before I tried with all the likely triggers and the username always was set to the default email, despite my best efforts.
If it's possible to set the username to be our custom username before the user who is registering is saved into the database, that would be the ideal solution as it would be more efficient than this new method we are trying (Where the user is saved, but we can't set the custom username - so we change the username after it's saved, then change it in the moodle db.
Compared to what would be ideal - save the user with our custom username in the first place; the database never thinks that the username field was anything other than our custom username)

I realise this was a big read but I just wanted to give as much info as possible to make it easier for you to understand my position/thinking and what I'm trying to achieve - any tips or advice is much appreciated.

If there's anything you don't understand or need more info on just let me know!

Cheers

Edit: All of the auto actions settings have been left as the default, except for the basics under the "Global" tab and the "Action" tab where I only changed what the actual action was (SQL query or set field equal to) - no other settings were changed, only the value of what the field is equal to

To change the username before the user is stored you need to modify the user object before storage and by acting on the user object reference. The below should do this fine.

Global
Triggers: onBeforeUserRegistration, onBeforeNewUser, onBeforeUserUpdate, onBeforeUpdateUser
Type: Code
User: Automatic
Access: Everybody
Action
Method: PHP
Code: Parameters
Reference Variables: Variable 1

The above covers registration and profile edit of frontend and backend since you're making this dependent on name fields which can be changed. In these triggers var1 is the user object. Be sure to set the reference variables parameter or you won't be able to modify the username before storage since CB Auto Actions will just be acting on a copy of the variable.

Next to synchronize the change to Moodle. You'd just need a small Query action after registration or profile edit is complete as follows.

Global
Triggers: onAfterUserRegistration, onAfterNewUser, onAfterUserUpdate, onAfterUpdateUser
Type: Query
User: Automatic
Access: Everybody
Action
Query:
I am not familiar with Moodles database structure so the above may require adjustments.

Hey that was perfect - Just the first solution worked as then moodle/joomdle automatically pulled through our edited username because it was initially saved like that in the database. Cheers