SQL Query Help

  • Fabbstar

    (@fabbstar)


    9 years, 5 months ago

    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

    (@msimpson)

    9 years, 5 months ago

    I recommend against writing SQL. I suggest to follow Accessing Form Data via PHP to retrieve the data you want, and expose that via the API that you create.

    Thread Starter Fabbstar

    (@fabbstar)

    9 years, 5 months ago

    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

    (@msimpson)

    9 years, 5 months ago

    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

    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 or it might be easier to create a shortcode string from the user input and call 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 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.

Tags