Solved One select query for several meta_values possible?

perlfan

Registered User.
Local time
Today, 03:55
Joined
May 26, 2009
Messages
192
Hi there, I'm trying to fetch data from a Wordpress database into MS Access. In picture below you'll see how Woocommerce (the shop plugin of Wordpress) stores user data. I'm now trying to build a query to fetch the different user data at once (e.g. _billing_first_name, _billing_last_name, _billing_email, _billing_phone, _billing_city). They always have a common order ID. Of course, I could use single selects such as:
Select meta_value from wp_postmeta where orderID = " & ID_woo & " and meta_key IN ('_billing_first_name')"
As I need quite a lot of data, I'm wondering if I can pull the data at once. Thanks for help.
Frank

load-woocommerce-customer-data-from-user-meta-1.png
 
maybe you can use the power of PowerQuery in Excel to pull the data then import it from Access.
 
Perhaps use a crosstab query

row heading =userid
Column heading = meta key
Value=first(meta value)
 
We use a query like this to extract data from Woocommerce by SQL:
SQL:
SELECT
    p.ID as order_id,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END ) as billing_email,  
    max( CASE WHEN pm.meta_key = '_billing_company' THEN pm.meta_value END ) as billing_company,
    max( CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END ) as billing_first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END ) as billing_last_name,
    max( CASE WHEN pm.meta_key = '_billing_address_1' THEN pm.meta_value END ) as billing_address_1,
    max( CASE WHEN pm.meta_key = '_billing_address_2' THEN pm.meta_value END ) as billing_address_2,
    max( CASE WHEN pm.meta_key = '_billing_city' THEN pm.meta_value END ) as billing_city,
    max( CASE WHEN pm.meta_key = '_billing_state' THEN pm.meta_value END ) as billing_state,
    max( CASE WHEN pm.meta_key = '_billing_postcode' THEN pm.meta_value END ) as billing_postcode,
    max( CASE WHEN pm.meta_key = '_billing_country' THEN pm.meta_value END ) as billing_country
FROM wp_posts p
    INNER JOIN wp_postmeta pm
        ON p.ID = pm.post_id
WHERE    post_type = 'shop_order'
GROUP BY p.ID;
 
Super, this is what I needed - I didn't know that if ... then is possible in the Select part. Thanks a lot! Frank
 

Users who are viewing this thread

Back
Top Bottom