Ask your WordPress questions! Pay money and get answers fast! (more info)

Help with writing SQL query WordPress

  • REFUNDED

Can anyone help with wpdb query to get all woocommerce orders (with order details) last 30days

I need to output into a table that will be looking like below

Order ID - B_first_name - B_last_name - Order_Items & Order Items_meta - Total

Currently, I have:
global $wpdb;
$oi = $wpdb->prefix.'woocommerce_order_items';
$oim = $wpdb->prefix.'woocommerce_order_itemmeta';
$pm = $wpdb->prefix.'postmeta';
$p = $wpdb->prefix.'posts';
$get_tab_1 = $wpdb->get_results

Answers (1)

2017-05-23

Swadesh Swain answers:

select
p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts p
join wp_postmeta pm on p.ID = pm.post_id
join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
post_type = 'shop_order' and
post_date BETWEEN '2015-01-01' AND '2015-07-08' and
post_status = 'wc-completed' and
oi.order_item_name = 'Product Name'
group by
p.ID


Regarding date 30 days record replace "CURDATE() - INTERVAL 30 DAY AND CURDATE()" with " '2016-01-01' AND '2016-07-08' "


email889 comments:

how and where can I add your codes?

I am using

global $wpdb;
$oi = $wpdb->prefix.'woocommerce_order_items';
$oim = $wpdb->prefix.'woocommerce_order_itemmeta';
$pm = $wpdb->prefix.'postmeta';
$p = $wpdb->prefix.'posts';
$get_tab_1 = $wpdb->get_results("

=== YOUR CODE ====

", ARRAY_A);

var_dump($get_tab_1);

it's empty