CFDB Plugin

The Situation:

You use short codes cfdb-table or cfdb-datatable to display a table on your page. One of the table column contains URLs or email addresses. You would like those to be clickable.

The Solution:

Add the following jQuery javascript to your page. To do so, in WordPress, edit your post or page, and switch the editor to “Text” view. Then past in the script AFTER the short code appears.

URLs: Assuming you have a column named “Website” use this code. Change “Website” below to the name of your column in line 3.

1
2
3
4
5
6
7
8

<script type="text/javascript">
(function ($) {
  $('td[title="Website"] div').each(
    function () {
      $(this).html('<a href="http://' + $(this).html() + '">' + $(this).html() + '</a>');
    })
})(jQuery);
</script>

The above assumes you have a URL value like “www.google.com” and it add “http://” in front of it to create a valid link. If your links already have “http://” in it, simply delete that from the code above in line 5.

Emails: Assuming you have a column named “E-mail” use this code. Change “E-mail” below to the name of your column in line 3.

1
2
3
4
5
6
7
8

<script type="text/javascript">
(function ($) {
  $('td[title="E-mail"] div').each(
    function () {
      $(this).html('<a href="mailto:' + $(this).html() + '">' + $(this).html() + '</a>');
    })
})(jQuery);
</script>

New in version 2.8

Shortcodes in version 2.8+ allow you to add additional text before and after the short code. Include the above script with the short code like this (in between markers {{AFTER}} and {{/AFTER):

[cfdb-table form="myform"]
{{AFTER}}
<script type="text/javascript">
(function ($) {
  $('td[title="Website"] div').each(
    function () {
      $(this).html('<a href="http://' + $(this).html() + '">' + $(this).html() + '</a>');
})
})(jQuery);
</script>
{{/AFTER}}
[/cfdb-table]

Forms that have many thousands of entries can be slow to return query results or may cause you PHP page to run out of memory. A quick addition to your short code can make the query run by the short code faster and require less memory. This addition is unbuffered=”true”.

Example: change [cfdb-table form="form1"] with
[cfdb-table form="form1" unbuffered="true"]

However: this cannot be used if you are nesting a short code inside another short code that is also doing a query. It doesn’t work if two queries are running at the same time. An example might be nesting a cfdb short code inside a [cfdb-html] short code.

Technical: This option makes use of the PHP mysql_unbuffered_query function instead of mysql_query.

This is an example of a user-defined short code that I helped someone create and I thought it would be a good one to share.

My new friend Gillian created a web site to track Polio survivors currently living in Australia (http://www.polioaustralia.org.au). Visitors to the site can register and provide information about themselves. Gillian is capturing these submissions into her database using this plugin. She then wanted to add some graphs showing statistics on the data.

Specifically, she wanted to compute some percentages. Example: of those people who contracted Polio in Australia, how many were contracted in New South Wales?

The first thought would be to use the cfdb-value short code with function=”percent”. But cfdb-value does not give her what she wants. The issue: The calculation needs to exclude those people who contracted Polio in a different country (Those people made form submission because they are now living in Australia).

So we turned to creating our own short code. In this short code we want to first select only the subset of form submissions that indicate a case of Polio contracted in Australia, then determine the percentage of those that were from a particular state (e.g. New South Wales).

But we can do better; we created a general “percentage-of” short code where we can select those entries that constitute the total for the denominator, then count those that match the sub-criterion for the numerator (state=New South Wales in this case).

The approach to getting the total (denominator) is to use plugin’s “filter” capability to select the relevant entries (In this case, Polio cases contracted in Australia). Then in the code of our user-created short code, we loop through those results and count those that meet our sub-criterion (state=New South Wales), compute and output the percentage.

The short code placed on the page looks like this:

[percent-of form="Polio Register" filter="Overseas=N" field="PolioStateTerr" matching="New South Wales"]

Here, we used the built-in  form and filter short code attributes to select the entries of interest for the denominator (those from the register that are not overseas i.e. Polio cases contracted in Australia). Then we add two new short code attributes that our user-created short code PHP will need to handle. These are field and matching for the numerator.

The PHP for the short code is the following. You may wish to use this code since it can be applied generally.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

require_once(ABSPATH .
'wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php');
$exp = new CFDBFormIterator();
$exp->export($atts['form'], $atts);
$total = 0;
$count = 0;
while ($row = $exp->nextRow()) {
    $total = $total + 1;
    if ($row[$atts['field']] == $atts['matching']) {
        $count = $count + 1;
    }
}
$percentNum = 100.0 * $count / $total;
$percentDisplay = round($percentNum) . '%';
echo $percentDisplay;

The key element is in lines 9-10 which resolves to checking if a form entry’s PolioStateTerr field’s value is equal to the value “New South Wales”. If so, we increment $count. At the end we compute the percentage using $count/$total.

As of version 2.2, you can filter by relative time to capture things like “since last week”

[cfdb-table form="Contact form" filter="submit_time>last week"]

Read more