How to Query the DB for your form data

October 18th, 2014

***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.

 

 

  1. Michael Brown
    March 2nd, 2011 at 06:41 | #1

    Can you make a video of this … I am so confused 🙂

  2. msimpson
    March 2nd, 2011 at 10:54 | #2

    @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

  3. Son Do
    March 18th, 2011 at 12:52 | #3

    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.

  4. msimpson
    March 19th, 2011 at 11:05 | #4

    @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.

  5. msimpson
    March 19th, 2011 at 15:53 | #5

    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.

  6. Son Do
    March 21st, 2011 at 07:10 | #6

    Thank you for your explanation! Let me try the new version.

  7. Ace
    March 1st, 2012 at 00:39 | #7

    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!

  8. May 7th, 2012 at 04:35 | #8

    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?

  9. Michael Simpson
    May 7th, 2012 at 10:20 | #9

    @Marco
    If you are following this page to create your own SQL query, then additional constraints like
    AND field_value=’particular name’

  10. May 13th, 2012 at 22:59 | #10

    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

  11. Michael Simpson
    May 14th, 2012 at 14:02 | #11

    @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.

  12. Paul Dillon
    April 13th, 2013 at 15:53 | #12

    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.

    • Michael Simpson
      April 14th, 2013 at 08:50 | #13

      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:

      1
      2
      3
      4
      5
      
      SELECT * FROM
      (
        [INSERT the generated pivot query here]
      )
      WHERE `your-sex` = 'Female'
  13. Paul Dillon
    April 14th, 2013 at 15:35 | #14

    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.

  14. sahadatju
    April 20th, 2014 at 12:32 | #15

    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.

  15. sahadatju
    April 21st, 2014 at 10:57 | #16

    <?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

  16. Michael Simpson
    April 26th, 2014 at 11:56 | #17

    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.

Comments are closed.  Go To Support Forum