How to Query the DB for your form data

get_row.sql

SELECT DATE_FORMAT( FROM_UNIXTIME( submit_time ) ,  '%b %e, %Y  %l:%i %p' ) AS Submitted, 
MAX ( IF( field_name =  'your-name', field_value, NULL ) ) AS  'Name', 
MAX ( IF( field_name =  'your-message', field_value, NULL ) ) AS  'CardNum'
FROM wp_cf7dbplugin_submits
WHERE form_name =  'suggestion form'
GROUP BY submit_time
ORDER BY submit_time DESC 
LIMIT 0 , 30

gistfile1.txt

Table Structure


Submitted	    Name	       CardNum	    Exp	        Amount

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



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.