How to Access WooCommerce Database

Do you want to access WooCommerce database? In this brief tutorial, we will delve into how WooCommerce stores information in the database and how to find this information directly from the relevant tables.

Table of Contents

WooCommerce provides the ability to create an e-commerce site with features like the ability to create products, register new customers, and most importantly manage orders.

Getting WooCommerce Order information

You may want to export WooCommerce information about customers, their orders, and the products that they had bought from one site to another. There are many plugins on the market today, which allow you to export individual orders.

However, it is important to note that if you have too much information to export, timeout errors will occur. It would simply take hours to export information spanning over a couple of years and it is not feasible.

Before we proceed, it is worth mentioning that you should have some coding skills to implement this solution. You should also take a full backup of your site. It will allow you to revert to the previous version if an error occurs.

Let us see how you can access this information from the database.

How to Access WooCommerce Database

In this section, we will share how you can export an insanely large number of WooCommerce orders from the database. It is not a clear-cut solution, but we will explain all the details.

Information about customers, orders placed and products is stored across multiple different tables. To find out the exact table with this information, we need to construct queries to fetch the relevant data from these tables instead of just exporting everything.

WooCommerce orders are located in the wp_posts table because they are a Custom Post Type.

ID	post_status	post_name	post_type
102	wc-completed	order-may-13-2021-0601-am	shop_order
106	wc-completed	order-may-13-2021-0721-am	shop_order
108	wc-completed	order-may-13-2021-0801-am	shop_order

To fetch orders from the wp_posts table, we need to select posts with the post_type “shop_order”.

[php] /* get orders from wp_posts table */

SELECT * FROM `wp_posts` WHERE post_type = "shop_order";
[/php]

Any information held in this table regarding orders is only partial and the rest is contained within the wp_postmeta table. This table contains information like:

meta_id	post_id	meta_key	meta_value
3456	102	_order_currency	USD
3457	102	_billing_address_1	255Example Road
3458	102	_shipping_address_1	shop_order
3459	102	_payment_method	paypal
3460	102	_order_total	155.00

To get metadata like the shipping address or payment method used for each order, we need to match the post_id against the order’s ID in the wp_posts table.

[php] /* get order meta data from wp_postmeta table */

SELECT * FROM `wp_postmeta` WHERE post_id in (SELECT ID FROM `wp_posts` WHERE post_type = "shop_order");
[/php]

If you need to get extra information about the actual products being ordered for each individual order, their quantity, costs associated, etc, we will export the wp_woocommerce_order_items and wp_woocommerce_order_itemmeta tables.

[php] /* get everything from wp_woocommerce_order_items table */

SELECT * FROM `wp_woocommerce_order_items`;

/* get everything from wp_woocommerce_order_itemmeta table */

SELECT * FROM `wp_woocommerce_order_itemmeta`;
[/php]

Conclusion

In this brief tutorial, we have shared an alternative way of extracting WooCommerce order information straight from the database. This is a great solution if you have a large number of orders that need to be exported.

If you encounter any problem implementing this solution, we recommend that you consult a qualified WordPress developer.

Editorial Staff

How to Access WooCommerce Database

Getting WooCommerce Order information

How to Access WooCommerce Database

Conclusion

Similar Articles

Related