[SOLVED] SQL Actions Not Working

9 years 1 month ago - 8 years 3 months ago #134802 by metricton
So, I am trying to setup some SQL actions, and yes I know how MySQL works. Yes, I have also tested the Queries in phpmyadmin and they work just fine.

However, when subscribing / unsubscribing via the backend through an administrator, it would seem the SQL Actions are never executed. The only thing that is executed is CB Fields.

One, I use CB Fields to set two fields. One field is set to Active, and the other field is set to the name of the Subscription plan.

The problem is, CB Fields does not let you specify what you want the field to be when the user is inactive / unsubscribes. Instead it just removes it and leaves it blank. I need it so it adds "Inactive" to where active used to be, and change the other to "Regular" instead of the subscription plan name.

That is why I am trying to use the SQL Actions to accomplish it, and to accomplish adding a Date / Time to a field when the user renews subscription / creates subscription.

SQL Statements:

I have tried it this way:

Activation: UPDATE `#__comprofiler` SET `cb_donationdate` = NOW() WHERE `user_id` = '[user_id]'

Unsubscribe / Deactive: UPDATE `#__comprofiler` SET `cb_donationstatus` = 'Regular', `cb_memberstatus` = 'Inactive' WHERE `user_id` = '[user_id]'

And I have tried it this way:

Activation: UPDATE `#__comprofiler` SET `cb_donationdate` = NOW() WHERE `user_id` = [user_id]

Unsubscribe / Deactive: UPDATE `#__comprofiler` SET `cb_donationstatus` = 'Regular', `cb_memberstatus` = 'Inactive' WHERE `user_id` = [user_id]

I have also tried it this way:

Activation: UPDATE `jos_comprofiler` SET `cb_donationdate` = NOW() WHERE `user_id` = [user_id]

Unsubscribe / Deactive: UPDATE `jos_comprofiler` SET `cb_donationstatus` = 'Regular', `cb_memberstatus` = 'Inactive' WHERE `user_id` = [user_id]

None of them are executed when you unsubscribe via the backend / delete / activate a subscription

Thanks,
Metric
9 years 1 month ago #135001 by krileon
None of them save properly when adding or removing a users subscription via User Management. The same will be seen with CBSubs Fields. The queries ARE firing, but they're fired before the user is saved so the user is updated with the values from the query THEN is updated from the values of the fields as a result of saving the user. This is a known issue and there is no known work around as the trigger can't be moved. I recommend granting users subscriptions via CBSubs import. Please note it will behave as normally on frontend.

Post edited by: krileon, at: 2010/06/07 21:52


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM EST to 4:00 PM EST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
9 years 1 month ago #135065 by metricton
So, what you are saying is that the sql actions are firing first, and then the actual user fields from the user management is being saved? I don't think that is the issue, because CB Field integration works and updates the fields as needed just fine.

However, not even the date via the SQL Action is added to the field. So, something else is going on, because I am not overwriting that field at all with CBFields.

Even so, if you can get CB Fields to edit the database properly upon saving from user management. Then I do not see why SQL Actions cannot also trigger after the initial Save from the user manager, or even trigger after CB Fields.

One would think, y'all would have taken this into account when developing CB Subs and Community Builder.

Now, if I do use the CB Subs import, will the SQL Actions fire properly when the subscription is being imported for that user? <- this is my main concern, but I also need it to work from user management. Why? Because, of manually switching users to subscriptions when receiving an offline payment.

Thanks,
Metric
9 years 1 month ago #135087 by krileon

So, what you are saying is that the sql actions are firing first, and then the actual user fields from the user management is being saved?

Correct.

One would think, y'all would have taken this into account when developing CB Subs and Community Builder.

We are aware of the issue and are working to resolve it for a later release. Bugs are a common reality and we'll address them one by one as we come about them.

Now, if I do use the CB Subs import, will the SQL Actions fire properly when the subscription is being imported for that user?

Yes.

We've an active topic on this issue as follows as well as a bug ticket number we use internally.

www.joomlapolis.com/component/option,com_joomlaboard/Itemid,38%3E/func,view/catid,89/id,112663/#112663

Post edited by: krileon, at: 2010/06/08 21:49


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM EST to 4:00 PM EST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
8 years 4 months ago - 8 years 4 months ago #156583 by Robert_ITMan
We too find SQL Actions not working - and seeing as this thread has the right topic we thought we would add to it rather than start a new one.

Any reason why the following would not work with SQL actions integration?

UPDATE `jos_comprofiler` SET `cb_adminnotes` = CONCAT(' ACTIVATED Annual Membership <br /><br />',`cb_adminnotes`) WHERE `id` = [user_id] LIMIT 1;

We also tried this:

UPDATE `jos_comprofiler` SET `cb_adminnotesold` = `cb_adminnotes`, `cb_adminnotes` = CONCAT(' ACTIVATED Annual Membership<br /><br />',`cb_adminnotesold`) WHERE `id` = [user_id] LIMIT 1;

And this does work (but it replaces what we had there already and thus defeats the purpose):

UPDATE `jos_comprofiler` SET `cb_adminnotes` = CONCAT(' ACTIVATED Annual Membership<br /><br />') WHERE `id` = [user_id] LIMIT 1;

Database Version: 5.1.9
PHP Version: 5.2.8
Joomla! Version: Joomla! 1.5.22 Stable
8 years 4 months ago #156788 by krileon
Please test subscribing with debug mode enabled and maximum error reporting set within Joomla global configuration. This will allow fatal query errors to show so you can see if there's something wrong with your query. Also test within PHPMYADMIN directly if possible to see if it'll work (of course substitutions won't, use a real users ID).

Please also keep in mind if you're updating a CB field and you give a user a subscription through backend User Management the value set by the plan will NOT save as CBSubs executes BEFORE the users profile saves, which causes whatever the plan has done to reverse due to their profile data replacing it.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM EST to 4:00 PM EST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Moderators: beatnantkrileon
Time to create page: 0.394 seconds
Facebook Twitter Google LinkedIn