CFDB Plugin

Want to create your own short code but aren’t much of a programmer? For $25 I’ll write it for you.

Complete the form below. I’ll contact you to discuss what you want. You will then need to make an up-front donation of $25. If I cannot get your short code to work for any reason, your money will be refunded.

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.

What to create a form to that can be used to search your form entries? Follow this example:

1
2
3
4
5
6
7

<form action="" method="POST">
First Name: <input type="text" name="fname" /> 
Last Name: <input type="text" name="lname" /> 
<input type="submit" /></form>
 
[cfdb-datatable form="Contact Form" 
filter="fname~~/.*$_POST(fname).*/i&&lname~~/.*$_POST(lname).*/i"]

In the form, put whatever fields you wish to search on. You can name these form fields by any name.

Use a short code with the filter attribute. Here we use filter variable substitution. $_POST(fname) and $_POST(lname) will be substituted with the values of the form post.

For the filter in the short code, string together regular expressions of the form database_form_field_name~~/.*$_POST(posted_field_name).*/i. The “.*” at the front and end of the regex allow the user to just type in part of a word and find a match. The “i” makes it case-insensitive.

Chain all the regex’s together with logical AND (&&). If a user leaves an entry blank in the search form like fname in the example above then fname~~/.*$_POST(fname).*/i will become fname~~/.*.*/i after substituting an empty string. This regex will match all entries. The effect is as if this contraint did not exist. So this means that a user can fill in only those elements on the search form that he wants to contrain. Those that are not filled out are effectively unconstrained.

Missing Uploaded Files Fixed

Version 2.2.7 of the plugin is being released today to address the problem where uploaded files from Contact Form 7 forms where not being saved to the database. The submission data structure created by Contact Form 7 change sometime around version 3.1 which regard to how uploaded files where listed. This causes the CFDB plugin to miss upload file entries. This has been fixed in CFDB version 2.2.7.

Require Zend Version 1.1.11

The Google Spreadsheet upload function has been seen to fail recently with versions of Zend earlier than 1.1.11. Earlier versions used to work, but evidently changes on the Google side cause them to no longer work. Upgrade to Zend 1.1.11 to ensure the Google Spreadsheet upload works.

For Contact Form 7 Users: a recent update of the Contact Form 7 plugin seems to have added several new meta fields to form submissions. These automatically get saved by the CFDB plugin. If you look on the Database page in your administration panel,  you will see extra fields associated with new submissions:

_wpcf7,_wpcf7_version,_wpcf7_unit_tag,_wpnonce,_wpcf7_is_ajax_call

You may view these fields as unwanted clutter.

To avoid saving these fields, go to the Database Options page,  and paste the list of fields in the following:

Currently Contact Form 7 and Fast Secure Contact Form are integrated with this plug. That means that submissions from forms made by those plugins will be captured in the database by this plugin automatically.

If you have created a different form plugin then you may want to integrate it with the CFDB plugin so it will save your plugin’s form submission data into the database. This post explains how to do it. Essentially, when your code receives a form submission, you will need to organize the data into a an object structured in the way that CFDB plugin expects, then call a hook to send the data to it.

Calling the Hook

In the code where your plugin handles form submissions, you will need to organize the data into an object (see below). Let’s call that object $cfdb_data. You would then call the “cfdb_submit“ hook:

1

do_action_ref_array( 'cfdb_submit', array( &$cfdb_data ) );

Organizing the Data

You will need to create the $cfdb_data object to send to the CFDB like this:

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

$cfdb_data= (object) array(
    // name of the form
   'title' => $form_name,
 
    // $form_data is an associative array of
    // $form_field_name => $form_field_value
    // for each field in the form.
    // For files, the $form_field_value is the file name (no path)
    'posted_data' => $form_data,
 
    // $uploaded_files is an associative array of
    // $form_field_name (same as in $form_data) =>
    // a path on the server where the uploaded file is stored.
    // The CFDB will copy the file into the database.
    // Your plugin can then delete the file if you want.
    'uploaded_files' => $uploaded_files
);

Working Example Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

$formTitleField = 'form_title'; // change this as needed
if (is_array($_POST) && !empty($_POST)) {
    $title = isset($_POST[$formTitleField]) ? 
                   $_POST[$formTitleField] : 'Untitled';
    $posted_data = array();
    $uploaded_files = array();
 
    // Get posted values
    foreach ($_POST as $key => $val) {
        if ($key != $formTitleField) {
            $posted_data[$key] = $val;
        }
    }
 
    // Get uploaded files
    if (is_array($_FILES) && !empty($_FILES)) {
        foreach ($_FILES as $key => $file) {
            if (is_uploaded_file($file['tmp_name'])) {
                $posted_data[$key] = $file['name'];
                $uploaded_files[$key] = $file['tmp_name'];
            }
        }
    }
 
    // Prepare data structure for call to hook
    $data = (object) array(
        'title' => $title,
        'posted_data' => $posted_data,
        'uploaded_files' => $uploaded_files);
 
    // Call hook to submit data
    do_action_ref_array('cfdb_submit', array(&$data));
}

Notes

The function that handles the calls to the “cfdb_submit” hook is CF7DBPlugin::saveFormData($cf7)

There are two security settings on the Database Options page that can be confusing. Hopefully this post will clear that up. They are:

1. Can See Submission data
2. Can See Submission data when using shortcodes

Each of these can be set to a role level (Anyone, Subscriber, Contributor, Author, Editor, Administrator).

A user has a privilege if his role is equal or higher than that specified. If  ’Can See Submission data’ is set to ‘Author’ then all users with  Author, Editor, or Administrator have this privilege.

If a user’s role allows him to have #1, then he has complete access to the data. When logged into the WP Admin area, he will see the “Database” menu options in the “Contact” menu provided by Contract Form 7. He can delete data in the admin area. He can see short code output meaning he has #2 privileges. #1 is a superset of #2.

A subtle point is that if a user has #1, that the value of #2 is completely irrelevant to him. #1 is a superset of #2. It therefore follows that it is not meaning to set the role needed for #2 higher than that of #1.

#2 sholud be set to a role that is less than or equal to the level of #1. Typically #2 is set to ‘Anyone’ while #1 is set to something high like ‘Editor’ or ‘Administrator’.

The idea is that you may want visitors to your site (especially those that are not registered users) to be able to see the output of your short codes on public pages and posts. But even if they are registered users, you don’t want them to be able to edit your data.

If a registered user has #2 but not #1, he will not see menu items in the WP admin area for this plugin. However if he has permission to create posts and he is knowledgeable about how to manually create short codes and of your form names, he could craft a post and put in a short code to see data. So it is not entirely secure from reading. However he cannot delete any data, even if he tries to duplicate the operation that the admin page uses to delete data.

(As of version 2.2.4)

The [cfdb-html] short code now will allow you to nest short codes within it. This can be any tag you like, but there is a special case where you can nest a different tag from this plugin.

Example:

1
2
3
4

[cfdb-html form="My Form"]
Here is a table submitted at ${Submitted}
[cfdb-table form="My Form" filter="submit_time=${submit_time}"]
[/cfdb-html]

In this case we nest a [cfdb-table] inside the [cfdb-html]. The special thing that we do is give the submit_time from the outer [cfdb-html] to the inner [cfdb-table]. Remember that [cfdb-html] prints out its template for every entry found in the database and replaces ${field-name} type values with the field values for each entry. In this case, we effectively pass the submit_time (a form submission unique id) from each [cfdb-html] entry to a [cfdb-table] short code via filter=”submit_time=${submit_time}”. The [cfdb-table] short code will find just that one element each time.

This can be inefficient because [cfdb-html] does a query, then for each entry found,  [cfdb-table] does a query. A more efficient solution is to follow the technique in Create Your Own Short Code.

NOTE: because of a limitation of WordPress, you cannot nest a [cfdb-html] inside a [cfdb-html]

A few people have asked me if I could make the plugin make a WP Post out of a form submission. This would allow users to make a form post without logging into the WP admin area.

After some thinking, I decided it was best to provide this as a separate plugin. It is called “Form to Post”. You can install it like any other plugin. Read up on how to use it at Form to Post on WordPress.org.

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.