Table of Contents

---

Working with WooCommerce Data Using MySQL

WooCommerce, the popular e-commerce plugin for WordPress, relies on a MySQL database to store and manage product information, customer details, and order data. Understanding how to work with WooCommerce data in MySQL can be valuable for customizing your online store, performing bulk operations, or troubleshooting issues. In this article, we’ll explore various MySQL instructions that can assist you in managing your WooCommerce database.

Get all products

Products in WooCommerce are essentially a custom post type with the post_type set to ‘product.’ To fetch all products, you can execute the following MySQL query:

SELECT * FROM wp_posts WHERE post_type = 'product';

This query will return all the products in your WooCommerce store, along with their details, stored in the wp_posts table.

Retrieve product details

To retrieve specific details about a product, you can use SQL queries like the following:

Get product information by ID

You can obtain detailed information about a product by knowing its post ID. For example:

SELECT * FROM wp_posts WHERE ID = 123;

In this query, replace 123 with the actual ID of the product you want to retrieve.

Get product metadata

WooCommerce stores additional product information in the post_meta table. To retrieve product metadata, you can use a query like this:

SELECT * FROM wp_postmeta WHERE post_id = 123;

Again, replace 123 with the product’s actual post ID.

Update product details

To update product information, you can use SQL queries to modify the post and post_meta tables.

Update product title

Suppose you want to change the title of a product with the ID 123:

UPDATE wp_posts SET post_title = 'New Product Title' WHERE ID = 123;

Update product price

To change the price of a product, you can update its post_meta:

UPDATE wp_postmeta SET meta_value = 'New Price' WHERE post_id = 123 AND meta_key = '_price';

Make sure to replace 123 with the product’s actual post ID and ‘New Price’ with the desired price value.

Delete products

In some cases, you may need to remove products from your store. Be cautious when using DELETE queries as they permanently delete data.

Delete a specific product

To delete a specific product, use the following query:

DELETE FROM wp_posts WHERE ID = 123;

Delete product metadata

If you want to remove a product’s metadata, you can execute:

DELETE FROM wp_postmeta WHERE post_id = 123;

Remember that these actions are irreversible, so use them with care.

Query product categories

WooCommerce organizes products into categories. You can query products within a specific category using SQL.

Get products by category

To retrieve products from a specific category, you can use a query like this:

SELECT p.* FROM wp_terms t
JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN wp_posts p ON tr.object_id = p.ID
WHERE t.name = 'Category Name' AND tt.taxonomy = 'product_cat';

Replace 'Category Name' with the name of the category you want to query.

Additional MySQL Instructions

Here are some additional MySQL instructions that can help you manage your WooCommerce data:

Find all customers

To find a list of all customers who have made purchases:

SELECT * FROM wp_users WHERE ID IN (SELECT DISTINCT customer_id FROM wp_woocommerce_order_items);

Retrieve customer details

To retrieve specific details about a customer, you can use their user ID:

SELECT * FROM wp_users WHERE ID = 456;

Update customer information

If you need to update customer information, such as their email address:

UPDATE wp_users SET user_email = 'new_email@example.com' WHERE ID = 456;

Delete customer records

To delete a customer record, you can use the DELETE query:

DELETE FROM wp_users WHERE ID = 456;

View recent orders

To view the most recent orders in your WooCommerce store:

SELECT * FROM wp_posts WHERE post_type = 'shop_order' ORDER BY post_date DESC LIMIT 10;

Retrieve order details

To retrieve details of a specific order, you can use the order’s post ID:

SELECT * FROM wp_posts WHERE ID = 789;

Mark an order as completed

If you want to mark an order as completed, you can use an SQL update query:

UPDATE wp_posts SET post_status = 'wc-completed' WHERE ID = 789;

Refund an order

To process a refund for an order, you can use an SQL update query:

UPDATE wp_woocommerce_order_items SET order_item_status = 'refunded' WHERE order_id = 789;

These additional MySQL instructions provide you with a comprehensive set of tools for managing your WooCommerce store’s data. Please be cautious when executing database operations to avoid any unintended data loss or errors.

Get orders with a specific coupon code

Sometimes it’s useful to know the orders that have been created using a specific coupon code

SELECT *
FROM wp_woocommerce_order_items woi 
JOIN wp_posts p on woi.order_id = p.id 
WHERE woi.order_item_name = 'coupon' 
AND woi.order_item_name = 'YOUR-COUPON';

Replace YOUR-COUPON by the coupon code you want to check