How to Query the DB for your form data
***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.
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 |
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.
Can you make a video of this … I am so confused 🙂
@Michael Brown
I’m seeing that the web page theme is changed the single quotes in some browsers to something MSQL doesn’t like.
So here is a video including me manually correcting the quotes.
http://cfdbplugin.com/files/pivotquery.html
Could you please explain me more detailed! How can I use this code to get the value of e.g NAME then use it as a filter for a table on a WP page?
I’m sorry, I’m a newbie on SQL and php.
Thank you very much.
@Son Do
This example is for getting stuff out of the database manually. If you want to extract a value and put it on a page/post, then ignore this. Instead, use a shortcode with “filter” option.
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.
Thank you for your explanation! Let me try the new version.
Just to add I have found an awesome jquery plugin which wraps the data which is spit out on the page and allows users to filter using ajax search / pagination etc.. It seems to be working great. Here is hwo it looks once you wrap the jQuery script around the table this cfdb plugin spits out: http://datatables.net/release-datatables/examples/basic_init/themes.html
Note: When pasting the jQuery script in the (wordpress header section), you will need to change the “#example” id to the following class “.cf7-db-table”
enjoy!
First of all thanks for the plugin,
How can i filter the data after the query? If i want to display only the rows with a particular name, what are the commands to achieve this?
@Marco
If you are following this page to create your own SQL query, then additional constraints like
AND field_value=’particular name’
I am having trouble creating my own sql query. Additional constraints like
AND field_value=’particular name’ do not seem to work. Can you provide a more complete MySQL statement?
My field name is “tourid” and the value i’m looking for is $thePostID
Thanks!
Bryson
@Michael Simpson
@Bryson
No WordPress post id is captured in a form submission, so no query on this table can give you that.
To help with a query, go to the Database Options page and set “Show the query used to display results” to true. Then go to the Database page and select a form. That will print the MySQL query used to get the data at the bottom of the page. Use that as a template for your query.
Hi Michael,
I’m a noob and struggling to understand how to filter the query with the AND parameter.
I can run the SQL command and get the entire output of the database as you describe in this article. But let’s say I have a line :
max(if(`field_name`=’your-sex’, `field_value`, null )) AS ‘your-sex’,
How do I filter the output for ‘Female” so that I get all fields output. I assume I edit this one line but need the syntax.
Many thanks and your plugin is great.
I think the simpest thing is to make the generated pivot query a sub-select wrapped in an outer select that imposes your constraint. What I mean by that is:
Thanks Michael,
I ended up creating a view with the pivot query and then queried the view. I understand that process a little better – the pivot query is a little above my skill level.
I have find out a answer for my previous question but for that i need to use a variable instead of 0(Zero fixed value) for this example limit=”0,i” My short code is [cfdb-value form="Contact form 1" show="Name" limit="0,i"] . Can i use a variable which produce value 0,1,2,3….. instead of fixed value.
<?php
$num = 0;
while($num<=4){
echo 'Name:’.do_shortcode( ‘[cfdb-value form="Contact form 1" show="Name" limit="$num,1" unbuffered="true"]‘ ).’Age:’.do_shortcode( ‘[cfdb-value form="Contact form 1" show="Age" limit="$num,1" unbuffered="true"]‘ ).”;
$num = $num+1;
}
?>
Useing this code I get
Name:
Age:
Name:
Age:
Name:
Age:
Name:
Age:
Name:
Age:
why Values are not presented here
I’m not sure what is wrong, but I since you are writing PHP, I would suggest alternative ways to do this that would give you more control and flexibility over calling “do_shortcode”.
Better: to directly to the class that computes values for [cfdb-value]
require_once(ABSPATH . ‘wp-content/plugins/contact-form-7-to-database-extension/ExportToValue.php’);
$exp = new ExportToValue();
echo $exp->export(‘Contact form 1’, array( ‘show’ => “Name”, ‘limit’ => “$num,1”));
Best:
Or to be more efficient, you can loop through the data yourself and generate the counts and output you want directly. See: http://cfdbplugin.com/?page_id=367
Advantage here is that the previous solutions loop through all the data each time you run through your “while” loop. Instead, loop through it just once.