Getting WooCommerce order information from the database

Here at Wibble, we utilise WooCommerce to help our clients manage their online stores. WooCommerce is a popular plugin for WordPress and provides the ability to create an e-commerce site with features like the ability to register customers, create products and manage orders.

This post in particular will delve a little into how WooCommerce stores information in the database and how to find this information directly from the relevant tables.

Recently, for a client site, I needed to export WooCommerce information about customers, their orders and the products that they had bought so that they could be transferred from one site to another.

I attempted to use plugins for this as they offered the ability to export individual orders which seemed to be exactly what I needed.

However, I ran into a couple of issues:

• Timeout
  This particular site that I needed to export information from had a huge number of orders, spanning over a couple of years at least. When attempting to export, I was met with timeout errors. It was just simply too much to export.

• Taking too long
  In an attempt to circumvent the previous issue, I decided to reduce the range of data being exported by the plugin into separate individual months. So, for example, exporting all orders made during the month of January in 2020. However, as mentioned before, this site had orders spanning years. Each month was taking at least 5 hours to export. It would simply take too long and just wasn’t feasible.

So, there had to be another way to export an insanely large number of WooCommerce orders, right?

Well, it seemed that there was a way: just get it from the database.

This, however, wasn’t as clear-cut as I thought it would be. WooCommerce stores any data about customers, orders placed and products across multiple different tables. Firstly, I had to find which tables this was all stored in, and then I needed to construct queries to actually fetch the relevant data from these tables instead of just exporting everything.

WooCommerce orders are a Custom Post Type so they’re located in the wp_posts table.

This contains information like:

To fetch only orders from the wp_posts table, I selected posts with the post_type “shop_order”.

/* get orders from wp_posts table */
SELECT * FROM `wp_posts` WHERE post_type = "shop_order";

Of course, any information held in this table regarding orders is only partial and the rest is contained within the wp_postmeta table.

This contains information like:

In order to get this meta data (like the shipping address or payment method used) for each of these orders, I would need to match the post_id against the order’s ID in the wp_posts table.

/* 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");

Additionally, I also needed to get extra information about the actual products being ordered for each individual order, their quantity, costs associated etc.

This part was easy as it was just a simple export of two of WooCommerce’s own custom tables, wp_woocommerce_order_items and wp_woocommerce_order_itemmeta.

This is just an alternative way of extracting WooCommerce order information straight from the database whenever a large number of orders need to be exported.

Wibble are experts with all things WordPress – this is our preferred CMS that our studio uses. If you need help getting started with your own eCommerce and web design project or need a bit of guidance with Woocommerce please get in touch and we will be happy to help.