Advanced CForms Queries: Pivoting filtered submission data into rows.

I’ve mentioned before that Oliver Seidel’s CForms is my hands down favorite forms plugin, and I wrote a previous post about using CForms to write data to Google Docs. I’ve been doing more work with business system integration, and CForms again makes a great front end for that process. CForms comes with an API, but the list of available filters for ‘get_cforms_entries();’ doesn’t include the ability to pass a where clause. In this case, I needed to be able to query and display a pivoted table that included a where clause against a hidden field I’d included with the form to set a submission status.

/* Display a filtered view of CForms submissions */
function MyCustomCFormsQuery() {
		global $wpdb;

		// replacing standard CForms query with custom query to display in rows, and filter for status -> $array = get_cforms_entries();
		// I've passed a hidden field called 'status' in the cforms submissions, and I'm going to use that to filter what I see in this display

		// This query is going to pivot the columnar data, and cast the field values as column labels. Use your field names as stored in the database
		$queryargs = $wpdb->prepare( "Select * from (

We define our function, open the WordPress data object, and begin our query. The first select returns the row casted values of the nested queries below.

			select distinct D.sub_id,
				MAX(IF(field_name = 'First Name', field_val, null)) as 'FirstName',
				MAX(IF(field_name = 'Last Name', field_val, null)) as 'LastName',
				MAX(IF(field_name = 'Phone', field_val, null)) as 'Phone',
				MAX(IF(field_name = 'Email', field_val, null)) as 'Email',
				MAX(IF(field_name = 'Event Type', field_val, null)) as 'EventType',
				MAX(IF(field_name = 'Est. No. of Guests', field_val, null)) as 'GuestCount',
				MAX(IF(field_name = 'Select Date', field_val, null)) as 'EventDate',
				MAX(IF(field_name = 'Is this date flexible?', field_val, null)) as 'DateFlexible',
				MAX(IF(field_name = 'If yes, please provide details:', field_val, null)) as 'FlexDetails',
				MAX(IF(field_name = 'Estimate', field_val, null)) as 'Budget',
				MAX(IF(field_name = 'Special Instructions or Additional Details', field_val, null)) as 'Details',
				MAX(IF(field_name = 'How did you hear about us?', field_val, null)) as 'Source',
				MAX(IF(field_name = 'Status', field_val, null)) as 'Status'

				from (

The second select is going to take the values we found in the most inside select, and pivot them. Pay attention to how your field names are actually stored in the database, as the usage is “field_name = ‘Your stored field value’”. Next is our inside select, which returns the columnar data we just pivoted:

select sub_id, field_name, field_val 
						from wp_cformsdata 
						where field_name in 
							('Status','First Name','Last Name','Phone','Email','Event Type','Est. No. of Guests','Select Date','Is this date flexible?','If yes, please provide details:','Estimate','Special Instructions or Additional Details','How did you hear about us?')

This gives us the submission data we’re looking for along with an identifying key: the sub_id.

					) as D

					group by sub_id
					) as T
					where T.Status = '1'
					order by T.sub_id desc;");
		$array = $wpdb->get_results($queryargs);

Close the innermost query as object D, and group by the sub_id. Close the second innermost query as object T, and pass a where clause and order the output by the sub_id. Finally, execute our query.

So, how did we do? Let’s check our output:

/* Do we have data? */
		if (! $array) {echo '

Query returned no results.

'; } else {		
		// Debug print_r ($array); // If you're having trouble with no results, or bad results, uncomment this line		
		/* OK, we have data, so let's display it */		
		print '

‘;

ID First Name Last Name Phone Event Type Date Budget Actions

Build your column labels in your table to match your data, and then populate the table with the output of your query:

   foreach( $array as $e ){
   	$timestamp = strtotime($e->EventDate);
   	$subDate = date("m-d-Y", $timestamp);
	echo '' . $e->sub_id . '' . $e->FirstName . '' . $e->LastName . '' . $e->Phone . '' . $e->EventType . '' . $e->EventDate . '' . $e->Budget . 'Your action links';
}
    }
    print '';
  }

You can then call ‘MyCustomCFormsQuery()’ in a shortcode or WP hook.

Have a better method for accomplishing the same thing, or suggested improvements to this code? Leave a comment below!

Download the code file (zip, 1.25k).

P.S. Sorry if the displayed code formatting is a bit funky; it seems WP-Code Highlight has its own ideas about indentation. The code is indented correctly in the download.