CFDB Plugin

***UPDATE***

New in version 1.8, it will give you the pivot query. But you have to turn on the option in “Database Options” “Show the query used to display results”. Then go to the “Database” page and show your form. The query will be at the bottom of the page.

If you want to see/query your form data like it is all in its own table, grab that query, put a “CREATE VIEW your_form_name” in front of it, then execute that in MySQL to create view that other applications can query.

A couple people have wanted to access the database directly and query the form data. But when they go to do this, they are surprised to find that there is not a DB table for each form with columns to match. Instead there is one table that holds all the form data in a “soft” schema. (If you want more info on the schema and its rationale, read here).

That leaves them scratching their heads trying to figure out how to create a SQL query that would return data just like you would see it on the plugin’s admin page. To do this, you have to write a pivot query. The concept of pivoting data is it a bit hard to get your head around at first. Then trying to write a SQL query (which doesn’t have a natural syntax for pivoting) is harder still.

In this post, I will walk you through an example MySQL pivot query so you can create one for yourself. the good news is that you don’t have to fully understand pivoting, you just have to understand how to take this example SQL and follow the pattern to plug in stuff to make it work for your form. And you have to understand how to access your database, for example using PHPMyAdmin to access it via web pages. Your service provider likely provides this.

First, in our example, imaging we went to the admin page and the data looked something like the following. It is from a form called “Purchases” and reflects credit card purchase information.

The pivot SQL that you would have to write to return the data in the same format looks like this:

1
2
3
4
5
6
7
8
9
10
11

SELECT
DATE_FORMAT(FROM_UNIXTIME(submit_time), '%b %e, %Y  %l:%i %p') AS Submitted,
MAX(IF(field_name='Name', field_value, NULL )) AS 'Name',
MAX(IF(field_name='CardNum', field_value, NULL )) AS 'CardNum',
MAX(IF(field_name='Exp', field_value, NULL )) AS 'Exp',
MAX(IF(field_name='Amount', field_value, NULL )) AS 'Amount'
FROM wp_cf7dbplugin_submits
WHERE
form_name = 'Purchases'
GROUP BY submit_time
ORDER BY submit_time DESC

How to Adapt this to query your form

Pay attention to the highlighted items in the above example:

• Replace Purchases with the exact name of your form (same case), just like it appears in the selection of forms in the Admin page.
• You will need to create one of those lines that start with “max(if” for each column you wish to display.
  • Place them in the order that you want to see them
  • Substitute your form’s field names for the highlighted values. Be sure these are an exact match to the column headers you see in the admin page (case sensitive).
  • Add/remove as many of these lines as needed
  • Be sure the very last line does NOT have a comma at the end (an easy syntax error to make)
• Date-time format: if you want a different format, consult the MySQL Manual on date formats. You may also need to adjust the output for timezone.

A few times people have raised the question of why the plugin’s database table (wp_cf7dbplugin_submits) is structured the way it is.

The table is set up with one row per form entry. So each row contains the submit time, form name, field name and field value. The MySQL definition is this:

CREATE TABLE IF NOT EXISTS wp_cf7dbplugin_submits (
submit_time DECIMAL(16,4) NOT NULL,
form_name VARCHAR(127) CHARACTER SET utf8,
field_name VARCHAR(127) CHARACTER SET utf8,
field_value LONGTEXT CHARACTER SET utf8,
field_order INTEGER,
file LONGBLOB)

Typically, people would have expected to see a separate table for each form, where each table’s columns reflect its form fields. They don’t expect to see this single consolidated table for all form data.

Typical questions/objections are:

(1). The database table structure does not match the table structure seen on the web page. The database data has to be pivoted into that table. This is not a trivial data transformation. Sometimes people want to write their own queries to against the table to retrieve or load data, and it is not so obvious/easy to do this.

(2). Schema is not normalized. It takes up more space and queries will not be as performant as compared to a normalized form.

My rationale for the design is to favor simplicity, flexibility and maintainability over what I expect to be negligible performance optimizations.

If every form had its own table, we have these issues:

(1). Need to integrate more tightly with Contact Form 7 and Fast Secure Contact form plugins to read their form definitions and create a table. With the existing “soft” schema, we can completely ignore the form definitions. We just save what gets posted.

(2).  When a user changes form definitions in Contact Form 7 and Fast Secure Contact form plugins, we would need to make appropriate changes to existing tables. But what if there is already data in the DB table that no longer matches any field in the updated form? How should we handle “legacy data”? With the existing schema, both coincide without conflict. The Admin panel display table shows you all the form submissions, and if one form submission did not have certain field, then it simply shows a blank cell in that column. If you delete all those legacy submissions, then any columns specific to them simply vanish.

(3). Integrating more tightly with Contact Form 7 and Fast Secure Contact form plugins creates dependencies on the inner workings of these plugins, thereby making my plugin more fragile.  In other words, it becomes sensitive to changes in those plugins and unexpected changes in new versions of those plugins may violate assumptions I would have to make in code, thereby breaking this plugin. As it stands, with the existing schema, the plugin requires the absolute minimal knowledge of other plugins. And what it does depend on are WordPress hooks defined by those plugins, which constitute an API. Therefore, I can feel confident that those other plugin writers will maintain support for their APIs and therefore my plugin will not have forward-compatibility issues.

(4). Although the current schema is in theory a less performant schema and take up more bytes in the DB, I believe this difference is negligible, and a good trade-off choice considering the benefits.

In summary, if I made a normalized one-table-per-form design, then:

(1) I have to write a whole bunch of table management code which I can now completely avoid.

(2) I have to integrate tightly with Contact Form 7 and Fast Secure Contact form plugin data structures, making this plugin more fragile.