WordPress.org

Ready to get started?Download WordPress

Forums

Exports and Reports
calling 2 different columns from another part of the DB (13 posts)

  1. Christopher Mitchell
    Member
    Posted 2 years ago #

    Hi everyone.

    I am trying to get a custom report to run from my wp e-commerce tables to show the variations and the details of the customer (name, address, contact number etc), and if possible the course that the customer is booked on.

    So far I got this far and couldn't work it out for the SQL query as i'm not really that good with SQL:

    SELECT ID,value FROM wp_wpsc_submited_form_data
    SELECT ID,name FROM wp_wspc_cart_contents
    SELECT ID,name FROM wp_wpsc_variation_values

    Now the value from submitted_form_data has a few bits of info for each client.. I don't know if this is possible, but the least I want to be able to do is show the customers name, the course from cart contents and the variation value to show the date.

    Can anyone help me with the code to make this work?

  2. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    Checking into this, will let you know what I find out.

  3. Christopher Mitchell
    Member
    Posted 2 years ago #

    Thank you so much for your help Scott :)

  4. Christopher Mitchell
    Member
    Posted 2 years ago #

    Hi Scott, any joy?

  5. Christopher Mitchell
    Member
    Posted 2 years ago #

    bump

  6. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    How many queries are you running at once? Exports and Reports is designed for one single query, so you may need to do JOINs to bring in other data.

  7. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    You'll also need to know how to write a SQL query to use this plugin right now, I don't have a visual query builder built into it and won't until I have enough time to build it.

  8. Christopher Mitchell
    Member
    Posted 2 years ago #

    Ahh I see.. So I'll need to use a JOIN to bring in other data.. I'll give that a try :)

    Thanks again for all your help Scott.

  9. Christopher Mitchell
    Member
    Posted 2 years ago #

    Right... i've tried to use the JOIN function and it still doesn't do anything within the plugin (it says no data to show when I try to run the report). The mySQL i'm using is:

    SELECT value, name FROM wp_wpsc_submited_form_data
    JOIN wp_wpsc_variation_values

    I've run it in the phpmyadmin and it works to a degree, i need to somehow run a GROUP_CONCAT on the value column from the form_data table does the plugin support that?

  10. Christopher Mitchell
    Member
    Posted 2 years ago #

    Here's the code that works in mySQL in the phpmyadmin:

    SELECT * FROM wp_wpsc_submited_form_data
    INNER JOIN wp_wpsc_variation_values
    ON wp_wpsc_submited_form_data.form_id=wp_wpsc_variation_values.variation_id
    WHERE <code>form_id</code> IN ('21','2','3','4','8','17')

    It just doesn't seem to work in the plugin. Any idea why?

  11. Christopher Mitchell
    Member
    Posted 2 years ago #

    Sorry the code bits weren't meant to be there.. I removed them and it still doesn't work.

  12. Christopher Mitchell
    Member
    Posted 2 years ago #

    bump

  13. Scott Kingsley Clark
    Member
    Plugin Author

    Posted 2 years ago #

    Try this, you need to specify the table for form_id:

    SELECT * FROM wp_wpsc_submited_form_data
    INNER JOIN wp_wpsc_variation_values
    ON wp_wpsc_submited_form_data.form_id=wp_wpsc_variation_values.variation_id
    WHERE wp_wpsc_submited_form_data.form_id IN ('21','2','3','4','8','17')

    If that doesn't work, I'd have to take a direct look at the query to figure this out.

    As for GROUP_CONCAT, this plugin uses MySQL queries so it supports whatever queries you want to write, as long as you adjust the field settings to suit.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic