MySQL script to get all WooCommerce orders including metadata

Query

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 '2021-01-01' AND '2021-08-01' and
    post_status = 'wc-completed'
group by
    p.ID

@Flioper & @iputuadi Maybe try this as a starting point:

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  ( SELECT count( m.meta_value = m.meta_value)
    FROM wp_woocommerce_order_items i
    JOIN wp_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
    WHERE i.order_id = p.ID AND i.order_item_type = 'line_item') AS 'Total Items Ordered'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE
	post_type = 'shop_order' AND
	p.post_date BETWEEN '2023-01-01' AND '2023-10-30'
GROUP BY p.ID

@Flioper & @iputuadi want to maybe post what you have so far so one can assist?

Hi, sure i have working code. I can show it in a week maybe. If u will wait i will post it here :)

Thanks @robindevitt for the code. but I don't think this is showing what I need yet.

Your query, just showing totals item in one invoice.

What I need is the purchasing QTY per item like in this piture.

WP Admin --> woocommerce --> Orders --> Detail Order

other than QTY, I also have a lot of trouble finding values from Customer purchase_note.

Basically, I want to display all the data that appears in the order details pages. into the query results in phpmyadmin.

I really appreciate sir. for your help.

It's been 2 days, but I haven't found a solution yet.

@iputuadi it should give you the QTY for your example it'll return 30. If you have multiple line items that the qty would be added together. Are you after retrieving all the line items for example: "30 x Product 1, 10 x Product 2, 5 x Product 3" and so on?

@iputuadi it should give you the QTY for your example it'll return 30. If you have multiple line items that the qty would be added together. Are you after retrieving all the line items for example: "30 x Product 1, 10 x Product 2, 5 x Product 3" and so on?

your query earlier, just shows recap data, what i really need, is the details data orders.

I give an example in the picture.

Example A:
When your query was executed, what appears is the recap data. as an example Order ID: 115 the result is = 1. Because there is only 1 item / product variant purchased in a transaction with code 115.

Example B:
For Order ID: 119 the result will appear is 2.

Because there are only 2 items / product variants purchased in transactions with code 119.

But what i really need, is the details data orders, per invoice (by Order ID). As in the picture.

The questions is, how to get the "QTY" value 30 from order ID 115, and qty value 12, 5, from order ID 119.

Thank you @robindevitt

@iputuadi , you would need to list each line item on it's own row:
a query like this could do the trick:

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Order Total',
  oi.order_item_name AS 'Item Name',
  oi.order_id AS 'Item Order ID'
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.post_id
JOIN wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE
  post_type = 'shop_order'
  AND oi.order_item_type = 'line_item'
  AND p.post_date BETWEEN '2023-08-01' AND '2023-08-30'
GROUP BY p.ID, oi.order_item_id
ORDER BY p.ID, oi.order_item_id;

@iputuadi , you would need to list each line item on it's own row: a query like this could do the trick:

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Order Total',
  oi.order_item_name AS 'Item Name',
  oi.order_id AS 'Item Order ID'
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.post_id
JOIN wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE
  post_type = 'shop_order'
  AND oi.order_item_type = 'line_item'
  AND p.post_date BETWEEN '2023-08-01' AND '2023-08-30'
GROUP BY p.ID, oi.order_item_id
ORDER BY p.ID, oi.order_item_id;

Hi @robindevitt Big thanks for your help, I really appreciate it.

I just thought about the woocommerce rest API,
https://woocommerce.com/document/woocommerce-rest-api/

And yesterday I've found the solution there.

Thank You

@iputuadi you care to share the solution should someone come across a similar issue in the future?

@iputuadi you care to share the solution should someone come across a similar issue in the future?

Yes sure. Thank you.

@Flioper & @iputuadi want to maybe post what you have so far so one can assist?

I want to get the value of the quantity in each order. I try to use the "_qty" parameter, but it doesn't show anything. Do you know how to get the quantity value for each order on woocommerce via a query?

With qty was a little harder, here i have this code, for me its works, shows everything :)
(select group_concat(woim.meta_value separator '
' ) from wp_woocommerce_order_items woi join wp_woocommerce_order_itemmeta woim on woi.order_item_id = woim.order_item_id where woim.meta_key = '_qty' and woi.order_id = oi.order_id) as qty,