How to Query the DB for your form data
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.
|Jan 1, 2011||John Doe||1234567890||1/1/2015||30|
|Jan 2, 2011||Jane Doe||246813579||2/1/2014||100|
|Jan 3, 2011||Richard Roe||9876543210||5/5/2013||80|
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.