Today’s tutorial is sure to open the flood gate of opportunities for how you use your CCB account data in an automated manner.
Most of us are quite familiar with CCB’s Process and Queues. From Connection Card follow ups to church Membership to messaging and communications, Process and Queues provide a structure and system for tracking attendees and members for a variety of church workflows and processes.
Although Process and Queues provide a church with an advancement in system tracking and communicating, one of the areas that it lacks is true automation. What I mean by true automation is that CCB lacks an automated method to message, communicate and process persons via Process and Queues.
This means that for every Process and Queue created, there always has to be someone to man the system. This can be good and bad depending on the size of one’s church they manage, and church staff to man the system.
So what does this automation business have to do with backup? Well, I’m glad you asked! 🙂 Although CCB backs up your data, I’m going to show you how to backup your data to an external source other than CCB.
As for further automation, I’ll have to cover more of this in up coming tutorials. For now, let’s get started on getting your data out of CCB.
Keep in mind that today’s tutorial can help you if you’re looking to migrate away from CCB to another provider, and help you if you seek to automate some simple tasks without hiring additional staff or putting more on the plates of your current staff.
Creating your database table
For starters, I’ll ask you to review a recent tutorial, How to create a MySQL database and table, should you not already be familiar with this process.
One of the first things to do in prepping to migrate data or backup a copy of your CCB data is to create a table. So, let’s create a table and name it ccb_profile_data, and include the following CCB fields in the newly created database table:
- individual_id
- first_name
- last_name
- family_position
- family_id
- family_indentifier
- birthday
- campus_id
To create your table, you can use one of the following options:
- phpMyAdmin’s built-in table creation wizard
- phpMyAdmin SQL query copy/paste window
- phpMyAdmin import SQL file
- MySQL command line syntax
For this tutorial, I’ll provide you with the SQL syntax for creating your table using options 2 and 4 since they are somewhat the same.
So, open your phpMyAdmin and go to the SQL tab (see screenshot below). If you need help accessing phpMyAdmin using Bluehost, feel free to review previous tutorials.
Now, you’re ready to copy and paste the following code into your SQL query window to create your backup table for CCB profile data. You’ll need to be sure to change `database` to your database for the code to successfully create the table.
Although you can use MYSAM for the engine (see last line of code), INNODB is most efficient, preferred by most and recommended.
CREATE TABLE `database`.`ccb_profile_data` ( `pkid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `individual_id` INT UNSIGNED NOT NULL DEFAULT '0', `first_name` VARCHAR( 50 ) NOT NULL , `last_name` VARCHAR( 50 ) NOT NULL , `family_position` VARCHAR( 2 ) NOT NULL , `family_id` INT UNSIGNED NOT NULL , `family_indentifier` VARCHAR( 25 ) NOT NULL , `email` VARCHAR( 255 ) NOT NULL , `birthday` DATE NOT NULL , `campus_id` INT UNSIGNED NOT NULL , `createDate` DATETIME NOT NULL , `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , INDEX ( `first_name` , `last_name` , `family_position` , `family_id` , `family_indentifier` , `campus_id` ) ) ENGINE = MYISAM
You can choose to add more CCB fields, but you’ll need to consult the individual_profiles service fields in the CCB API Documentation.
Extending functionality of CCB profiles codebase
Now that you have a table created, you’re now ready to extend the functionality of the previous tutorial, Retrieving total number of CCB Profiles. Review this tutorial for all the inside details of the codebase. I will use only the parsing of the XML response, and not use the entire codebase to highlight the necessary changes.
The beauty of the previous tutorial is that we can refactor or reuse that codebase for new extended functionality. Always aim to create a reusable codebase that can be easily extended for other purposes.
So, instead of parsing for the total number of CCB Profiles as we previously did, I’ll show you how to parse each individual CCB profile and insert it into your table.
Parsing CCB individual profile information
To parse each individual CCB profile as contained in the XML response, you’ll need to use XPATH query and the //individuals/individual expression. So, create the nodes variable and assign it to the rss reference to xpath, passing to xpath the //individuals/individual expression.
The next step you’ll want to take is setting the response_object and response_message variables to blank or nothing. Notice that I inlined both. This is a nifty trick to save on keystrokes.
Next, I’ll set an iterator or counter. I’m not going to use it in this tutorial, but I’ll briefly discuss why I’m using it. In short, if you wanted to know the number of profiles processed, then you simply could echo the i variable at the very end of your code. But more on this in a minute. For now, set i equal to 0 (zero).
Now you’re ready to parse individual profiles using the foreach statement. Once inside the foreach statement, set the response_object to an empty array. Now, this is where things can get tricky are hard to understand if you’ve not worked with arrays before.
Essentially, you can use the [] at the end of response_object variable to create an indexed array. Using the bulleted fields above and the example below, create an indexed array and set each to it’s respective parsed XML reference.
Notice that I did not create an array for the familiy_identifier. If you were importing and migrating data to CBB from another 3rd-party Church Management Software, you would use this field to group families or give families a unique id or identity. But since we’re only backing up data from CCB, then we don’t need it.
Next, you’ll need to check whether or not the CCB individual profile exists in your backup database table. If a profile exists in the backup table, then we only want to update information and not insert another profile. We’ll perform this check by creating and using the doesCCBProfileExist function, passing it the response_object array. I’ll explain this function later.
For now, if a CCB profile exists in the backup table, then we’ll update the profile using the updateCCBProfile function assigned to the response_message variable. If a CCB profile does not exist in the backup table, then we’ll insert the profile using the insertCCBProfile function assigned to the response_message variable. And you’ll complete this verification process using an if else statement.
Also, note the operator being used when assigned functions to the response_message variable.
And finally, before ending our foreach loop, you’ll add the i variable with the addition or increment operator symbol (++). And once the foreach loop has been completed, then you simply echo the response_message variable.
<?PHP
$nodes = $rss->xpath('//individuals/individual'); // xpath for individuals
$response_object = $response_message = '';
$i = 0;
foreach ($nodes as $node)
{
$response_object = array();
// now prepare the CCB individual profile date in an array (See CCB API documentation for more $apiService fields)
$response_object[] = $node['id'];
$response_object[] = $node->campus['id'];
$response_object[] = $node->first_name;
$response_object[] = $node->last_name;
$response_object[] = $node->family_position;
$response_object[] = $node->family['id'];
$response_object[] = $node->email;
$response_object[] = $node->birthday;
$checkProfileExists = doesCCBProfileExist($response_object);
if($checkProfileExists){
$response_message .= updateCCBProfile($response_object);
} else {
$response_message .= insertCCBProfile($response_object);
}
$i++;
}
echo $response_message;
?>The doesCCBProfileExist function
The doesCCBProfileExist function is essentially a quick look up to the backup table to see if a record for an individual exists. You’ll want to use the individual’s CCB id (i.e, the first value in the response_object array to perform this look up using a MySQL select statement.
Once you have the query, then you use the mysql_num_rows to determine if the person’s profile exists. If queryNum variable is greater than 0 (zero), then you have found a record with the individual’s CCB id profile and will need to return the msg variable as 1 (numeric one). By default the msg variable is set to 0 (zero) to indicated an individual’s id had not been found in the SQL query. And at the end of this function, you simply return the msg variable;
<?PHP
function doesCCBProfileExist($user){
$query = mysql_query("SELECT * from ccb_profile_data where individual_id='$user[0]';");
$queryNum = mysql_num_rows($query);
$msg = 0;
if($queryNum > 0) $msg = 1;
return $msg;
}
?>The updateCCBProfile function
The next function you’ll create is the updateCCBProfile function. You’ll pass the response_object array to it as the function’s user argument variable.
You normally wouldn’t update a profile, but I decided to include it to show you how to update an individual’s profile in the backup table in the event their profile data was updated. You’ll probably never know when someone updates their info, so it’s just a simple and safe function to keep your data updated.
This function will return a message that the person’s CCB profile was not or successfully updated depending on whether or not the query variable’s assignment to the mysql_query UPDATE statement was successfully run.
Notice that the createDate mysql table column is set equal to NOW() function.
<?PHP
function updateCCBProfile($user){
$query = mysql_query("UPDATE ccb_profile_data SET campus_id='$user[1]', first_name='$user[2]', last_name='$user[3]', family_id='$user[5]', email='$user[6]', birthday='$user[7]', createDate=NOW() WHERE individual_id='$user[0]' LIMIT 1;") or die(mysql_error());
$outcome = "not ";
if($query) $outcome = "successfully";
$msg = "$user[1] $user[2]'s CCB profile was $outcome updated.<br/>";
return $msg;
}
?>The insertCCBProfile function
The next function you’ll create is the insertCCBProfile function. You’ll pass the response_object array to it as the function’s user argument variable.
The insert behaves much like the updateCCBProfile function; however, this function uses the mysql_query INSERT statement instead of the UPDATE statement.
This function will return a message that the person’s CCB profile was not or successfully updated depending on whether or not the query variable’s assignment to the mysql_query INSERT statement was successfully run.
<?PHP
function insertCCBProfile($user){
$query = mysql_query("INSERT INTO ccb_profile_data (individual_id, campus_id, first_name, last_name, family_id, email, birthday, createDate) VALUES ('$user[0]', '$user[1]', '$user[2]', '$user[3]', '$user[5]', '$user[6]', '$user[7]', NOW());") or die(mysql_error());
$outcome = "not ";
if($query) $outcome = "successfully";
$msg = "$user[1] $user[2]'s CCB profile was $outcome inserted.<br/>";
return $msg;
}
?>It’s time to test your backup code!
Okay, now open your text editor assemble all of your code creating a backup-ccb-profiles.php file that looks like the codebase below. If you so desired, you could place the functions inside the general.php file created in previous tutorials. For the sake of this demo, I didn’t want you to get lost so i kept them in the file so you could see all of what your codebase should look like.
You’re now ready to test, and backup your CCB account info in your backup database table. Good luck and let me know if you have questions or run into technical issues.
If you’re wondering about the automation part, I’ll cover how to use automate this file on a daily basis in the next set of tutorials. Geez, I can’t give it all away in one tutorial! 🙂 Stay tuned…
<?PHP
/**
* backup-ccb-profiles.php
*/
// contains resusable globals and functions
include("includes/config.php");
$apiService = 'individual_profiles'; // CCB api service
$urlData = array_filter( array(
'srv' => "$apiService"
) );
$rss = ccbDetails('get',$urlData); // transform to XML
$nodes = $rss->xpath('//individuals/individual'); // xpath for individuals
$response_object = $response_message = '';
$i = 0;
foreach ($nodes as $node)
{
$response_object = array();
// now prepare the CCB individual profile date in an array (See CCB API documentation for more $apiService fields)
$response_object[] = $node['id'];
$response_object[] = $node->campus['id'];
$response_object[] = $node->first_name;
$response_object[] = $node->last_name;
$response_object[] = $node->family_position;
$response_object[] = $node->family['id'];
$response_object[] = $node->email;
$response_object[] = $node->birthday;
$checkProfileExists = doesCCBProfileExist($response_object);
if($checkProfileExists){
$response_message .= updateCCBProfile($response_object);
} else {
$response_message .= insertCCBProfile($response_object);
}
$i++;
}
echo $response_message;
function doesCCBProfileExist($user){
$query = mysql_query("SELECT * from ccb_profile_data where individual_id='$user[0]';");
$queryNum = mysql_num_rows($query);
$msg = 0;
if($queryNum > 0) $msg = 1;
return $msg;
}
function updateCCBProfile($user){
$query = mysql_query("UPDATE ccb_profile_data SET campus_id='$user[1]', first_name='$user[2]', last_name='$user[3]', family_id='$user[5]', email='$user[6]', birthday='$user[7]', createDate=NOW() WHERE individual_id='$user[0]' LIMIT 1;") or die(mysql_error());
$outcome = "not ";
if($query) $outcome = "successfully";
$msg = "$user[1] $user[2]'s CCB profile was $outcome updated.<br/>";
return $msg;
}
function insertCCBProfile($user){
$query = mysql_query("INSERT INTO ccb_profile_data (individual_id, campus_id, first_name, last_name, family_id, email, birthday, createDate) VALUES ('$user[0]', '$user[1]', '$user[2]', '$user[3]', '$user[5]', '$user[6]', '$user[7]', NOW());") or die(mysql_error());
$outcome = "not ";
if($query) $outcome = "successfully";
$msg = "$user[1] $user[2]'s CCB profile was $outcome inserted.<br/>";
return $msg;
}
?>