WordPress provides flexibility to execute raw SQL. Using WooCommerce we will execute basic SQL queries to fetch data for reports and API. Below are some WooCommerce basic SQL queries which help in fetching products, categories, orders, and order items from the database. All the queries mentioned in this article are using “wp_” as a default table prefix for the database tables. If the table prefix of your website is different, you will have to modify those prefixes accordingly.
In this article, we have used dummy product IDs and dummy order numbers for reference in the SQL queries. Those product IDs and orders numbers are highlighted in the examples which you have to change according to your case.
These are raw MySQL queries. We have executed them in PhpMyAdmin (an excellent database management tool that came pre-packaged with XAMPP and many other apache environment tools). If you’re writing a PHP script for your application’s need, you can also use these queries in your code.
#1: Return a list of all product categories
The taxonomy used for product categories is “product_cat” in WooCommerce. The SQL to fetch all categories is below:
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'
#2: Get the category for a specific product
If you want to fetch category details of a particular product, the following SQL will serve the purpose:
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
Here we are getting the category for product ID 167. Substitute your desired product ID, which you can find in the WooCommerce Products section from the admin panel.
#3: Return a list of product attributes for a specific product
In order to get all product attributes, you can use the following SQL query:
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 the above SQL, we are getting the attributes for product ID 167. Substitute your desired product ID, which you can find in the WooCommerce Products section from the admin panel.
#4: Return a list of line item details for a specific order
If you want to fetch the line items or product details of an order, you can use the following SQL query:
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
Here 7373 is the WooCommerce Order ID. Substitute your desired order ID, which you can find in the WooCommerce Orders section from the admin panel.
#5: Return shipping cost for a specific order
If you want to get the shipping code of a specific order, the following query will serve the purpose:
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 the above example, we’re getting shipping costs for order 9909. Substitute your desired order ID, which you can find in the WooCommerce Orders section from the admin panel.
#6: Return transaction ID for a specific order
In order to get the transaction ID of a specific order, you can the SQL below:
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 the above example, we’re getting a transaction ID for order 9909. Substitute your desired order ID, which you can find in the WooCommerce Orders section from the admin panel.
#7: Return total tax for a specific order
To get the total tax amount of a specific WooCommerce order, the following SQL will assist you in it:
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 the above example, we’re getting total_tax
for order 9909. Substitute your desired order ID, which you can find in the WooCommerce Orders section from the admin panel.