Below are some basic MySQL queries for getting WooCommerce products, categories, orders, and order items from the database. All of the queries assume the default “wp_” prefix to the database tables, so you may need to modify those prefixes if your tables are named differently. I’ve also left some example product and order numbers in place to complete the syntax of the queries. I’ve highlighted those values in the examples. Change to suit your particular use.
These are raw MySQL queries. I use them in PhpMyAdmin, an excellent MySQL admin tool. If you’re writing a PHP script for your own application needs, you can adapt these queries into your code.
Query #1: Return a list of product categories
SELECT wp_terms.*
FROM wp_terms
LEFT JOIN wp_term_taxonomy
ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'product_cat'
Query #2: Get the category for a specific product
SELECT wp_term_relationships.*,wp_terms.*
FROM wp_term_relationships
LEFT JOIN wp_posts
ON wp_term_relationships.object_id = wp_posts.ID
LEFT JOIN wp_term_taxonomy
ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
LEFT JOIN wp_terms
ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
WHERE post_type = 'product'
AND taxonomy = 'product_cat'
AND object_id = 167
In above example, we’re getting the category for product 167; substitute your desired product ID, which you can find in the WooCommerce Products admin menu area.
Query #3: Return a list of product attributes for a specific product
SELECT wp_posts.ID,wp_posts.post_title,wp_postmeta.*
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id
WHERE post_type = 'product'
AND wp_posts.ID = 167
In above example, we’re getting the attributes for product 167; substitute your desired product ID, which you can find in the WooCommerce Products admin menu area.
Query #4: Return a list of line item details for a specific order
SELECT wp_woocommerce_order_itemmeta.*,wp_woocommerce_order_items.*
FROM wp_woocommerce_order_items
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
WHERE order_id = 7373
ORDER BY wp_woocommerce_order_itemmeta.meta_key
In above example, we’re getting the line item details for order 7373; substitute your desired order ID, which you can find in the WooCommerce Orders admin menu area.
Query #5: Return shipping cost for a specific order
SELECT im.meta_key, im.meta_value, i.order_item_type
FROM wp_woocommerce_order_items i
JOIN wp_woocommerce_order_itemmeta im
ON im.order_item_id = i.order_item_id
WHERE i.order_item_type = 'shipping'
AND im.meta_key = 'cost'
AND order_id = 9909
ORDER BY im.meta_key
In above example, we’re getting shipping cost for order 9909; substitute your desired order ID, which you can find in the WooCommerce Orders admin menu area.
Query #6: Return transaction ID for a specific order
SELECT pm.meta_value
FROM wp_posts p
LEFT JOIN wp_postmeta pm
ON p.ID = pm.post_id
WHERE post_type = 'shop_order'
AND pm.meta_key = '_transaction_id'
AND p.ID = 9909
In above example, we’re getting transactionID for order 9909; substitute your desired order ID, which you can find in the WooCommerce Orders admin menu area.
Query #7: Return total tax for a specific order
SELECT SUM(ifnull(pm.meta_value, 0)) AS tax_total
FROM wp_posts p
LEFT JOIN wp_postmeta pm
ON p.ID = pm.post_id
WHERE post_type = 'shop_order'
AND (pm.meta_key = '_order_shipping_tax' OR pm.meta_key = '_order_tax')
AND p.ID = 9909
In above example, we’re getting total_tax for order 9909; substitute your desired order ID, which you can find in the WooCommerce Orders admin menu area.
