calling 2 different columns from another part of the DB

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

Viewing 12 replies - 1 through 12 (of 12 total)
  • Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

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

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    Thank you so much for your help Scott ??

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    Hi Scott, any joy?

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    bump

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    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.

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    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.

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    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.

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    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?

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    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?

    Thread Starter Christopher Mitchell

    (@chrismitchell)

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

    Thread Starter Christopher Mitchell

    (@chrismitchell)

    bump

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    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.

Viewing 12 replies - 1 through 12 (of 12 total)
  • The topic ‘calling 2 different columns from another part of the DB’ is closed to new replies.