Database Query & Field Update After CBSubs Payment

Hi,

I am using Joomla 2.5.14, CB 1.9, CBSubs GPL 3.0 and all plugins are current as of 2014.10.14.

Project Parameters:

This is an endangered animal sponsorship website. This site offers only 1 subscription option which has a fee of $xx.xx for a lifetime membership. When a person joins he or she is assigned one of ~1100 unique animals to sponsor (the animal will be assigned, not chosen by the user). Each animal has a unique identifier (tag number, alphanumeric) and a short bio (text field). The assigned animals unique identifier and bio needs to show up in the user’s profile (under a custom tab) and the unique identifier/ tag number needs to be searchable. Each of the ~1100 animals can only be assigned once.

Here is what I believe needs to be done:

Created 2 new custom fields: cb_tagid and cb_bio – Not sure what “type” to make these?

Create 1 new tab “Sponsorship Info” to display the 2 custom fields

After a person successfully subscribes AND pays through PayPal I need CB to query the animal database table, grab the first unassigned record, then add that information to the 2 custom fields.

I have examined the use cases, tutorials and many forum threads (including the one below) but still have not been able to come up with a solution.

www.joomlapolis.com/forum/153-professional-member-support/193762-solved-auto-increment-field

Any thoughts or ideas would be greatly appreciated.

Thanks in advance for your help!

Hi,

Thanks for the very detailed description which really inspired me to respond (Kyle and others may of course chime in with additional comments.

RossM wrote: Hi,

I am using Joomla 2.5.14, CB 1.9, CBSubs GPL 3.0 and all plugins are current as of 2014.10.14.

Project Parameters:

This is an endangered animal sponsorship website. This site offers only 1 subscription option which has a fee of $xx.xx for a lifetime membership. When a person joins he or she is assigned one of ~1100 unique animals to sponsor (the animal will be assigned, not chosen by the user). Each animal has a unique identifier (tag number, alphanumeric) and a short bio (text field). The assigned animals unique identifier and bio needs to show up in the user’s profile (under a custom tab) and the unique identifier/ tag number needs to be searchable. Each of the ~1100 animals can only be assigned once.

Here is what I believe needs to be done:

Created 2 new custom fields: cb_tagid and cb_bio – Not sure what “type” to make these?

Create 1 new tab “Sponsorship Info” to display the 2 custom fields

After a person successfully subscribes AND pays through PayPal I need CB to query the animal database table, grab the first unassigned record, then add that information to the 2 custom fields.

I have examined the use cases, tutorials and many forum threads (including the one below) but still have not been able to come up with a solution.

www.joomlapolis.com/forum/153-professional-member-support/193762-solved-auto-increment-field

Any thoughts or ideas would be greatly appreciated.

Thanks in advance for your help!

I am assuming that you already have a list, table, excel, database (one or more of these) with the elements you want for the endangered species.

Lets assume it's a database table (if not you should be able to easily create this).
Such a table could contain the following columns:

tagid (unique key for table - could be a simple auto increment integer)
name (name of species - I think it's more appropriate to display a name in the user profile than a tagid)
shortbio (could be a char column)
assignedid (initially 0 to show unassigned and will be populated with the actual userid when assigned - assuming again one assignment per user - you must provision for what happens when you are out of free tags and a new user pops up!).

You would need the following proposed CB fields:

1. cb_tagid (this could be an integer or a text field, it should even be marked as readonly and it could even be placed on a tab that is only accessible by CB Moderation groups)
2. cb_namequery - this is a CB Query field that will return the name column of your species database table based on value present in your cb_tagid field for the user.
3. cb_shortbio - this is a cb query field that grabs the shortbio from the database.

You need the following logic now:

When the CBSubs subscription starts (you could use the CBSubs SQL integration or perhaps some CB Auto Action):

1. Queries the species database and finds the first unassigned row.
2. pushes the tagid value to the user's cb_tagid field
3. updates the species database overwriting the assignedid column with the userid

I would first concentrate on:

a. construction the species database table
b. creating the 2 CB Query fields to grab values based on the cb_tagid field value which you can manually populate initially to test the query fields.

- once you have above you can at least manually handle the process.

Then you can focus on the automation via CBSubs SQL and/or CB Auto Actions.

Hi Nick!

Thank you very much for your direction . I will give this a try and report back.

Ross

Ok. I believe that I have the solution thanks to your direction.

We are going to accomplish this by running a PHP script via CB Auto Actions.

Here are a few follow up questions I have…

1. Trigger: I want this to happen after CBSubs confirms payment. Which of the following should I use?

• “CB Paid Subscriptions - Payment Item Event”
• “CB Paid Subscriptions - After Payment Status Change”
• “CB Paid Subscriptions - After Payment Status Update Event”

2. User: Please confirm “User” is the logical choice here
3. Code Actions: Can we use CB substitutions in the PHP script since this is being run from CB Auto Actions?
4. Best way for PHP script obtain the specific User ID of the person who just paid so the script is just run for this user?

Thanks again for your help.

Ross

1. Trigger: I want this to happen after CBSubs confirms payment. Which of the following should I use?

It depends entirely on what you need to do. If you need your action to fire after a plan has become active then the below is the best usage.

Triggers: onCPayUserStateChange
Conditional 1: [var3] Equal To PLAN_ID_HERE
Conditional 2: [var2] Equal To A

If you want your action to fire after a payment has been successfully completed then the below is the best usage.

Triggers: onCPayAfterPaymentStatusUpdateEvent
Conditional 1: [var4] Equal To Completed

2. User: Please confirm “User” is the logical choice here

No, leave it on automatic. Both of the above triggers provide the user object as var1, which automatic will find.

3. Code Actions: Can we use CB substitutions in the PHP script since this is being run from CB Auto Actions?

Yes, it will parse the code string you've supplied for substitutions. Once it's done it'll execute the code. So be careful with how you write your code as it'll see [STRING] as a substitution.

4. Best way for PHP script obtain the specific User ID of the person who just paid so the script is just run for this user?

Substitute in [user_id] or [var1_id], both work for the above triggers as the user object is var1.

Please check the attached image. This is what i have set so far. I want to change name of just paid user.

I am not clear what do u mean by var1,var2 ,var4 etc.

Can u tell me where i have to change by check the attached image?