Database Query & Field Update After CBSubs Payment

10 years 5 months ago #235141 by RossM
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!

Please Log in to join the conversation.

10 years 5 months ago #235147 by nant
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.

Please Log in to join the conversation.

10 years 5 months ago #235182 by RossM
Hi Nick!

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

Ross
The following user(s) said Thank You: nant

Please Log in to join the conversation.

10 years 4 months ago #237367 by RossM
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

Please Log in to join the conversation.

10 years 4 months ago #237383 by krileon

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.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
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 CST to 4:00 PM CST. 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.

Please Log in to join the conversation.

10 years 4 months ago #237421 by RossM
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?
Attachments:

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.245 seconds

Facebook Twitter LinkedIn