Developer Snippet Diary

WooCommerce Order Items and Meta Data extract using SQL Command

SELECT * FROM `wp_posts` where ID='123';
SELECT * FROM `wp_postmeta` where post_id='123';
SELECT * FROM `wp_woocommerce_order_items` where order_id=123; // it will return meta id use in below query
SELECT * FROM `wp_woocommerce_order_itemmeta` where order_item_id=456;

OR

 

SET @order_id = 123;

SELECT * FROM wp_posts WHERE ID = @order_id;
SELECT * FROM wp_postmeta WHERE post_id = @order_id;
SELECT * FROM wp_woocommerce_order_items WHERE order_id = @order_id;

-- gets itemmeta for ALL line items in one shot, no manual ID copying
SELECT oi.order_item_id, oi.order_item_name, oim.meta_key, oim.meta_value
FROM wp_woocommerce_order_items oi
JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id
WHERE oi.order_id = @order_id;

 

https://stackoverflow.com/questions/38743011/sql-select-query-for-retrieving-woocommerce-order-items-and-meta-data

Posted by: R GONDAL
Email: [email protected]