[SOLVED] Auto Actions insert in external DB - Solved

bizguy
OFFLINE
Posts: 199
Thanks: 32
Karma: 5

9 years 9 months ago - 9 years 9 months ago #260714 by bizguy

[SOLVED] Auto Actions insert in external DB - Solved was created by bizguy

I am looking to automatically add my CB members to my iDevAffiliate system. The iDevAffiliate database is not within the Joomla database, but is on the same host.

I am using Auto Actions to create the record and inserting some CB data to create the account.
I have searched and assembled what I believe to be the necessary coding, but would like suggestions as to how to correct it. I am not a programmer, so any assistance would be appreciated. (I have the correct info for XXXXXXXX).

Code:

$option = array(); //prevent problems
$option['driver']   = 'mysql';            // Database driver name
$option['host']     = 'localhost';    // Database host name
$option['user']     = 'XXXXXXXXXXX';       // User for database authentication
$option['password'] = 'XXXXXXXXXXX';   // Password for database authentication
$option['database'] = 'XXXXX_idevs';      // Database name
$option['prefix']   = '';             // Database prefix (may be empty)
$db = JDatabaseDriver::getInstance( $option );
$db = JFactory::getDbo(); // Get a db connection.
$query = $db->getQuery(true);  // Create a new query object.
$columns = array('id', 'username', 'approved', 'f_name', 'l_name', 'email', 'type', 'level', 'signup¬_date');  // Insert columns.
$values = array([userid], [username], 1, [firstname], [lastname], [email], 1, 1, date()); // Insert values.
// Prepare the insert query.
$query
    ->insert($db->idevaff_affiliates('XXXXX_idevs'))
    ->columns($db-> idevaff_affiliates ($columns))
    ->values(implode(',', $values));
// Set the query using our newly populated query object and execute it.
$db->setQuery($query);
$db->execute(); 

Any assistance in correcting this coding would be appreciated.

Last edit: 9 years 9 months ago by nant.

Please Log in or Create an account to join the conversation.

krileon
ONLINE
Posts: 48616
Thanks: 8302
Karma: 1446

9 years 9 months ago #260748 by krileon

Replied by krileon on topic Auto Actions insert in external DB

You don't need to use PHP for that. Use the Query action and set it to External mode. It will handle the entire database object construction for you for external queries. You'd then just use substitutions to substitute profile data into your query as needed. As for the actual query I can't suggest anything as I do not know iDevAffiliates database structure; you'd need to consult iDevAffiliate regarding that. If you choose to use PHP then you should consult the iDevAffiliate API documentation and use API instead of direct queries.

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.

The following user(s) said Thank You: bizguy

Please Log in or Create an account to join the conversation.

bizguy
OFFLINE
Posts: 199
Thanks: 32
Karma: 5

9 years 9 months ago - 9 years 9 months ago #260785 by bizguy

Replied by bizguy on topic Auto Actions insert in external DB

Thank you once again for the fast reply and helpful answer.
Should anyone else look to do this as well, here are some finer details:
Create a New Auto Action:

1 - Triggers: Before First Login
2 - Type: Query
3 - Action: Query

Code:

INSERT INTO `idevaff_affiliates` (`username`, `approved`, `payable`, `f_name`, `l_name`,
`email`, `address_1`, `city`, `state`, `zip`, `country`, `phone`, `type`, `level`,
`signup_date`, `ip`) VALUES ("[username]", 1, "[name]", "[firstname]", "[lastname]", "[email]", 
"[address]", "[city]", "[state]", "[zipcode]", "[country]", "[phone]", 1, 1, NOW()+0,
"[registeripaddr]");
INSERT INTO `idevaff_tiers` (`parent`, `child`) VALUES ("[cb_sponsor]", "[cb_member]");

4 - Action: Mode -> External
5 - Action: Host -> Localhost (in my case, the db is on the same host)
6 - Action: Username/Password/Database -> use whatever to setup on install of iDevAffiliate
7 - Action: Charset / Table Prefix -> left blank as unused

Note: On my install I synchronized the iDev users ID with my CB user ID and I have stored each user ID in the CB field cb_member and recorded the cb_sponor number at registration. This allows me to automatically create a connection between the two on registration in CB and setup a Tier connection in iDevAffiliate (which is the second INSERT above. If you don't need it, you can delete it.

Last edit: 9 years 9 months ago by bizguy. Reason: typo corrections

The following user(s) said Thank You: nant, shawneo

Please Log in or Create an account to join the conversation.

Moderators: beat, nant, krileon