Title: SQL Query Help
Last modified: October 25, 2016

---

# SQL Query Help

 *  [Fabbstar](https://wordpress.org/support/users/fabbstar/)
 * (@fabbstar)
 * [9 years, 5 months ago](https://wordpress.org/support/topic/sql-query-help-4/)
 * Hi Michael,
 * I hope you are well?
 * You seem to have missed my query regarding making the export features of the 
   plugin secure, so I decided to make an API myself!
 * I’ve created an SQL query of the form submissions and am hoping to make a JSON
   output as a result. I was wondering if you could help as I’m using your SQL example
   query as a basis.
 * I just started structuring a table of results using this SQL Query which is based
   on your website:
 * SELECT DATE_FORMAT(FROM_UNIXTIME(submit_time), ‘%b %e, %Y %l:%i %p’) AS Submitted,
   MAX(IF(field_name=’Username’, field_value, NULL )) AS ‘Username’, MAX(IF(field_name
   =’Value’, field_value, NULL )) AS ‘Value’, MAX(IF(field_name=’Ticket’, field_value,
   NULL )) AS ‘Ticket’, MAX(IF(field_name=’Renewal’, field_value, NULL )) AS ‘Renewal’
   FROM wp_cf7dbplugin_submits WHERE form_name = ‘Order Status Report’ GROUP BY 
   submit_time ORDER BY submit_time DESC
 * I now want to add a string to this so it filters by Username so I can effectively
   run this as a unique table of results for a specific user and build the JSON 
   output. I have a customer with the Username ‘CHEESE’, so I tried the following:
 * SELECT DATE_FORMAT(FROM_UNIXTIME(submit_time), ‘%b %e, %Y %l:%i %p’) AS Submitted,
   MAX(IF(field_name=’Username’, field_value, NULL )) AS ‘Username’, MAX(IF(field_name
   =’Value’, field_value, NULL )) AS ‘Value’, MAX(IF(field_name=’Ticket’, field_value,
   NULL )) AS ‘Ticket’, MAX(IF(field_name=’Renewal’, field_value, NULL )) AS ‘Renewal’
   FROM wp_cf7dbplugin_submits WHERE form_name = ‘Order Status Report’ AND field_value
   = ‘CHEESE’ GROUP BY submit_time ORDER BY submit_time DESC
 * It does correctly filter the results and shows only the orders for CHEESE, only
   now the Value, Ticket and Renewal columns all contain ‘NULL’, so no data is showing
   for the order.
 * Any thoughts on what I could be doing wrong?
 * Many thanks,
    S

Viewing 3 replies - 1 through 3 (of 3 total)

 *  Plugin Author [Michael Simpson](https://wordpress.org/support/users/msimpson/)
 * (@msimpson)
 * [9 years, 5 months ago](https://wordpress.org/support/topic/sql-query-help-4/#post-8355852)
 * I recommend against writing SQL. I suggest to follow [Accessing Form Data via PHP](http://cfdbplugin.com/?page_id=367)
   to retrieve the data you want, and expose that via the API that you create.
 *  Thread Starter [Fabbstar](https://wordpress.org/support/users/fabbstar/)
 * (@fabbstar)
 * [9 years, 5 months ago](https://wordpress.org/support/topic/sql-query-help-4/#post-8357300)
 * Hi Michael,
 * Thanks for the reply.
 * Will the result of your suggestion be a URL that could in theory be amended by
   the user to expose the rest of the data in the table?
 * That’s what I’m trying to avoid by using my SQL approach to generate a different
   URL structure that can’t be tampered with.
 * Thanks,
    S
 *  Plugin Author [Michael Simpson](https://wordpress.org/support/users/msimpson/)
 * (@msimpson)
 * [9 years, 5 months ago](https://wordpress.org/support/topic/sql-query-help-4/#post-8358323)
 * I don’t see any URL in your example, only SQL. But instead of doing complex SQL,
   let the plugin do it for you. Then if you want to expose that via a URL, then
   I suggest to create an WordPress AJAX URL that wraps it. See: [https://codex.wordpress.org/AJAX_in_Plugins](https://codex.wordpress.org/AJAX_in_Plugins)
 * Basically:
 * You need code to register the URL and a function to handle the request:
    add_action(‘
   wp_ajax_my_action’, ‘my_action_callback’ ); // Must be logged in or add_action(‘
   wp_ajax_nopriv_my_action’, ‘my_action_callback’ ); // Open to everyone
 * In your **my_action_callback** function you can code any additional security 
   checks before continuing. Then process any $_REQUEST parameters you choose to
   to pass to the [Accessing Form Data via PHP](http://cfdbplugin.com/?page_id=367)
   or it might be easier to create a shortcode string from the user input and call
   [do_shortcode](https://developer.wordpress.org/reference/functions/do_shortcode/)
   and let that just echo the output back to the calling browser.
 * Put all that code in your theme’s functions.php file or use my [Add Shortcodes Actions and Filters](https://wordpress.org/plugins/add-actions-and-filters/)
   plugin to give you a place to put it.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘SQL Query Help’ is closed to new replies.

 * ![](https://s.w.org/plugins/geopattern-icon/contact-form-7-to-database-extension_ffffff.
   svg)
 * [Contact Form DB](https://wordpress.org/plugins/contact-form-7-to-database-extension/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/contact-form-7-to-database-extension/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/contact-form-7-to-database-extension/)
 * [Active Topics](https://wordpress.org/support/plugin/contact-form-7-to-database-extension/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/contact-form-7-to-database-extension/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/contact-form-7-to-database-extension/reviews/)

## Tags

 * [sql](https://wordpress.org/support/topic-tag/sql/)

 * 3 replies
 * 2 participants
 * Last reply from: [Michael Simpson](https://wordpress.org/support/users/msimpson/)
 * Last activity: [9 years, 5 months ago](https://wordpress.org/support/topic/sql-query-help-4/#post-8358323)
 * Status: not resolved