WooCommerce basic SQL queries to fetch data for reports and API

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 that 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.

Introduction

In this article, we have used dummy product IDs and dummy order numbers for reference in the WooCommerce basic SQL queries. Those product IDs and order 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 needs, you can also use these queries in your code.

Need of WooCommerce basic SQL queries

If you are creating a plugin or API where you want to fetch data directly from the database, you can run WooCommerce basic SQL queries to achieve this. In the following sections, we have written some WooCommerce basic SQL queries which will help you to get more insights:

#1: Return a list of all product categories

Among the WooCommerce basic SQL queries, the most common is to fetch the 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

One of the important WooCommerce basic SQL queries is to fetch the category details of 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

There’s another WooCommerce basic SQL queries that can get all the attributes of a specific product. 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

One of the important WooCommerce basic SQL queries is to fetch the order details. 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

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.