MySQLWooCommerceWordPress

WooCommerce basic SQL queries to fetch data for reports and API

2
7.6k
2017 / 11 / 07

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

Query #2: Get the category for a specific product

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

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

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

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

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

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.

Hi, I am a full-stack web developer with 5+ years of experience in working with different web technologies. Do you want to ask something? just send me a message through the contact form. Please visit my portfolio at hamzamehmood.com. Thanks