Welcome back. It seems like weeks and days just fly by until the next tutorial.
Last week, I covered how to access data that does not have a formalized api service. In that tutorial, we desired to access the “How They Heard” about us information for each CCB profile.
I discussed the options of using CCB’s front-end interface to create a custom report as well as creating a custom search and using the execute_search API service to access data.
We’ll build on last week’s tutorial this week by focusing on how to create your very own “How They Heard” about your church dashboard.
Simply put, this simple dashboard will provide you insight into the following (see image below):
- Total number of CCB profiles with “How They Heard”
- Grouped “How They Heard” sources with their respective totals and percentages
If you’re new to this website, I won’t give much description in tutorial about certain coding methods in this tutorial. Be sure to review and complete the following tutorials before beginning this tutorial:
- How to parse Excel and CSV files using php
- How attendees heard about your church using execute_search
Let’s get started with creating your simple dashboard.
Accessing data via Excel/CSV
In today’s tutorial, I’ll show you how to access the “How They Heard” data using a Excel/CSV file. You should have your file present and ready to use.
Open up your text editor of choice, creating and saving a file named parse-hth-csv.php. The “hth” is short for “how they heard”. 😉 Don’t forget to add the necessary header comments.
For starters, copy the code from the tutorial How to parse Excel and CSV files using php. This will be your base code to start from. We’ll make a couple of modifications to this existing codebase.
Start with updating the err_upTmpName variable and assign it the name of your Excel or CSV as a text string.
Inside the first if statement, create groupData variable and assign it an empty array.
Next, place your cursor inside the else statement of the next if else statement and use the code below. I’ve explained the if statement that you’ll paste inside this area via the comments.
<?PHP
// $data[0] = first name; $data[1] = last name; $data[2] = email; $data[3] = how they heard
//if(!empty(array_filter($data))) - use if statement if you want to remove all empty rows from array
/*********************************************************************************************************************/
if(!empty(array_filter($data))){
//echo $data[0].' - '.$data[1].' - '.$data[2].' - '.$data[3].' - '.$data[4].' - '.$data[5].' - '.$data[6].' - '.$data[7].' - '.$data[8].' - '.$data[9].' - '.$data[10].'<br/>';
$groupData[] = $data;
}
?>You’ll also notice that I’ve commented out the echo line. This echo line can be uncommented to view each column of data that exists within your file.
Nevertheless, the important part of this code is that you create a multi-dimensional array of the data using the groupData variable that is assigned the data variable, which is an array of the specific line of Excel and CSV data.
Time to group and sum the “How They Heard” sources
Begin by creating an empty array variable using arr as the variable name. Then, create a foreach statement, passing it the groupData variable and defining as key and item variables. Here’s where things get a bit tricky.
To group each of the “How They Heard” sources, you’ll need to know the key’s value for each source. In my CSV example, the “How They Heard” column is column number 4.
However, we’re dealing with a zero index array, meaning the array key starts at 0 instead of 1. This means that value of the column will always be minus one.
Knowing this bit of information, the index array key we’re in search of is the number 3 even though the column containing the value in the spreadsheet is column number 4.
You’ll have to figure out what column number that holds the “How They Heard” data, counting the column number minus 1.
<?PHP
foreach($groupData as $key => $item){
$arr[$item['3']][$key] = $item;
}
?>Now the array for group each “How They Heard” source has been created and the foreach loop can now be closed.
Using the asort method, the next order of business is sorting the newly group array in ascending order based on the array key, also known as the “How They Heard” source.
Once arr variable has been sorted, the next order of business is to determine the number total number of grouped “How They Heard” sources.
To do this, define a typeTotals variable and set the value to use the array_map method, passing it “count” as the first argument and the arr variable as the second argument.
Now, use the ksort method to sort the typeTotals variable in descending order.
Now, let’s figure out how many total “How They Heard” records exist. To do this, define a totalSources variable and set the value to use the array_sum method, passing it the typeTotals variable. We’ll use this total number to figure out the percentage of each “How They Heard” source.
Next, define a eachLineItem variable and set it value to an empty string. We’ll use eachLineItem variable within another foreach statement that will list the following:
- Each “How They Heard” source
- Each “How They Heard” source total number
- Each “How They Heard” source percentage
Pass the foreach statement the typeTotals variable and defining as key and value variables.
Once inside the foreach statement, set a percentage variable and set the value to divide the value variable by the totalSources variable. Multiply this value by 100, and then use the round method to round to the 2 decimals.
The next step is to prepare the eachLineItem variable to concatenate each “How They Heard” source, it’s respective total number and percentage using .= operator.
Now close the foreach statement and prepare to echo the page header (i.e., How They Heard…), and the totalSources and eachLineItem variables.
<?PHP
/**
* parse-hth-csv.php
*/
$err_upTmpName = 'how-they-heard.csv';
$row = 0;
if (($handle = fopen($err_upTmpName, "r")) !== FALSE) {
$groupData = array();
while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
if($row == 0){
$row++;
} else {
// $data[0] = first name; $data[1] = last name; $data[2] = email; $data[3] = how they heard
//if(!empty(array_filter($data))) - use if statement if you want to remove all empty rows from array
/*********************************************************************************************************************/
if(!empty(array_filter($data))){
//echo $data[0].' - '.$data[1].' - '.$data[2].' - '.$data[3].' - '.$data[4].' - '.$data[5].' - '.$data[6].' - '.$data[7].' - '.$data[8].' - '.$data[9].' - '.$data[10].'<br/>';
$groupData[] = $data;
}
}
}
} else {
echo 'File could not be opened.';
}
fclose($handle);
$arr = array();
foreach($groupData as $key => $item){
$arr[$item['3']][$key] = $item;
}
asort($arr);
$typeTotals = array_map("count", $arr);
ksort($typeTotals);
$totalSources = array_sum($typeTotals);
$eachLineItem = '';
foreach($typeTotals as $key => $value){
$percentage = round(($value/$totalSources)*100,2);
$eachLineItem .= $key.': '.$value.' ('.$percentage.'%)<br/>';
}
echo '<h2>How They Heard...</h2>';
echo '<h4>Total Sources: '.$totalSources.'</h4>';
echo $eachLineItem;
/*
echo '<pre>';
print_r($typeTotals);
echo '</pre>';
/**/
//print_r($groupData);
?>Time to save and test the dashboard
You’re ready to now save the file and test the code using a web browser. Open the file in a web browser and you should be able to see the results for how most attendees and members hear about your church.
That’s it for this week’s tutorial. See you next week!