WooCommerce: Finding Products in the Database

Once the WooCommerce plugin has been installed you can access details of all the WooCommerce products in your store via the admin area of your WordPress site, but as a developer you may wish to access product data directly in the database, either for reporting purposes or to access a specific piece of data.

It is also useful to understand how WooCommerce stores data when you are trying to work out how a particular part of the WooCommerce functionality works.

Become a WooCommerce Coding Jedi
Click here for more details about the "Learning WooCommerce Development By Example" book

In this article we’ll take a look at the database tables that WooCommerce uses and then start looking at some SQL queries which will hopefully help us understand how all of the WooCommerce data fits together.

An Overview of the WooCommerce Database Tables

Products data is mainly located in the following tables:

wp_posts this is the table that WordPress uses to store post data, WooCommerce uses this table to store top level information about products. There is a post_type column in the table that WordPress uses to identify the type of a post, this is how WordPress differentiates between the base post types and any custom post types that are added. WooCommerce product data will have a post type of either “product” or “product_variation”.

wp_postmeta any meta data that is stored against a post will be in this table. As products are a type of post when we save product meta data it will also be stored in this table. WordPress stores data in this table using key/value pairs, which makes it very easy to add a new meta value to a post or product. The table is linked to the posts table via the post_id column.

wp_wc_product_meta_lookup This is a table that was added inWooCommerce 3.7, it provides a way to access meta information for products without going via the meta tables. The schema for the table looks like this

The WooCommerce codebase keeps this table up to date by syncing it’s data with the data in the meta tables, this allows us to query the columns in this table directly rather than having to look up keys and values in the meta tables.

There are more details of how this table works here https://developer.woocommerce.com/2019/04/01/performance-improvements-in-3-6/.

wp_wc_order_product_lookuptable This table was also introduced in WooCommerce 3.7, it provides a quicker way to look up products contained in orders

The tables above contain the product and product meta data, product types, categories, subcategories, tags, attributes and all other custom taxonomies are located in the following tables –

wp_terms – this table holds the the terms that are related to a taxonomy, so for example, the values “Red”, “Green” and “Blue” which are linked to the the WooCommerce colour attribute would would be held in this table

wp_termmeta – this table holds any meta information that is linked to terms, it uses a similar schema to the wp_postmeta table and is linked to the wp_terms table via the term_id attribute

wp_term_taxonomy – this table links terms to taxonomies, it also links taxonomies to parent taxonomies.

wp_term_relationships – this is the table that links categories, attributes and tags to the products.

wp_woocommerce_attribute_taxonomies – this is a WooCommerce specific table that stores top level details of WooCommerce product attributes

wp_wc_category_lookup – this another WooCommerce specific table that was added in WooCommerce 3.7, it provides a faster lookup for category hierarchies, you can find further details here here https://blog.nexcess.net/woocommerce-4-0/

We will now look at a number of sample queries, it will hopefully be able to see how all the above tables fit together once you see some working examples.

Where possible I have tried to include a sample of data returned by each query which will hopefully make it easier to understand/visualize how everything fits together.

How to Query the WooCommerce Database Tables

In order to query any of the WooCommerce database tables you’ll need to a connection to the database server that your WooCommerce store is using, you can do this in two different ways

Via phpMyAdmin – phpMyAdmin is a web based program that allows you to run queries against a SQL database, most WordPress hosts give you access to phpMyAdmin via your host panel. I’m not going to look at phpMyAdmin in any more detail in this article, but you can read some more details here.

Via a Client Program – There are a number of client programs that will allow you to view the schema of, and run queries against your WooCommerce MySQL database, some of the client programs I’ve used in the past include

SQL Yog
My Sql Workbench
Heidi SQL

As an example, we’ll connect to a DB using Heid SQL.

The first thing we need to do is get the connection details for our database, in this article I’m going to access the database linked to a local by Flywheel site, Local shows the database details for a particular site in the “Database” tab

Once we have these details we can connect to the database via Heidi SQL, we can do this by opening the Session Manager and inputting the details

Filling in the details should be reasonably straight forward, we’ve used “127.0.0.1” (the IP address of localhost) as the Hostname, and then input the provided values in the User, Password and Port fields.

Once the details have been populated clicking the “Open” button should open a new window with details of the database schema

You can then query the tables by entering SQL into the query window

Your results will then be shown in a grid below the query window. Noe that that the number of rows and columns returned by the query is included in the text on the tab.

So now we know how to run a query, let’s look at some sample queries and the data they return.

How to Get Product Information

This query will get all of the products in the WooCommerce database along with their types.

SELECT wp.id, wp.post_title, t.name
FROM wp_posts wp 
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
WHERE tt.taxonomy = 'product_type'

How to get Product Information for a Variable Product

This query will list all the variable products and their variations

SELECT wp.id AS `Product Id`, wpv.id AS `Variant Id`, wp.post_title, wpv.post_title, wpv.post_excerpt 
FROM wp_posts wp 
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
INNER JOIN wp_posts wpv ON wp.id = wpv.post_parent 
WHERE tt.taxonomy = 'product_type' 
AND t.name = 'variable' 
AND wpv.post_type != 'attachment'

As you can see from the query above, the top level product record has a title that describes the top level product, then the variant products each have a title that describe the attributes that apply to that particular variant.

When a customer purchases a product it is the title of the variant product that shows in the admin interface and allows the admin user to identify the variant of the product that needs to be sent to the customer.

In addition to the titles the variant information for a product is also stored in the wp_meta table against the variant id, if we use one of the variant ids from the example above we can see the variant information for that variant.

Note here that ids might be different in your database so running the SQL below might give you a different result, you would need to run the first query and then use an id returned by that query in the query below.

SELECT wp.id, wp.post_title, wpm.meta_key, wpm.meta_value
FROM wp_posts wp
INNER JOIN wp_postmeta wpm ON wpm.post_id = wp.id
AND wp.id = 29
AND meta_key LIKE 'attribute%'

In the SQL we specify that the value ‘attribute’ must appear at the start of the meta_key value, this means only the attribute data is returned and we don’t have wade through all the other meta information stored against the product.

Finally WooCommerce also stores data about the attributes linked to a product in a serialised value in the wp_meta table against the top level product, we can withdraw that value using the following SQL

SELECT wp.id, wp.post_title, wpm.meta_key, wpm.meta_value
FROM wp_posts wp
INNER JOIN wp_postmeta wpm ON wpm.post_id = wp.id
AND wp.id = 13
AND meta_key = '_product_attributes'

As you can see the value returned is a long coded value, I won’t go into how to decipher the value manually here, but we can decipher it in code using PHP’s userialize function.

$attributes_value = unserialize('a:2:{s:8:"pa_color";a:6:{s:4:"name";s:8:"pa_color";s:5:"value";s:0:"";s:8:"position";i:0;s:10:"is_visible";i:1;s:12:"is_variation";i:1;s:11:"is_taxonomy";i:1;}s:4:"logo";a:6:{s:4:"name";s:4:"Logo";s:5:"value";s:8:"Yes | No";s:8:"position";i:1;s:10:"is_visible";i:1;s:12:"is_variation";i:1;s:11:"is_taxonomy";i:0;}}');
d($attributes_value);

In the code above we use the unserialize function to convert the string value to an array we then use the d function from the Kint debugging extension to output the array to the screen, here’s what we would see if we added the code above as a snippet to a WooCommerce store

I’ve truncated the output slightly, but hopefully this gives you an idea of the data stored in the value.

It’s also worth noting the different way that WooCommerce stores the data for a global attribute “pa_color” and a local attribute “Logo”.

Just to give you a better idea of how it all fits together, here are the attributes for the “Hoodie” product listed in the WooCommerce admin interface.

How to get Product Information for a Grouped Product

Grouped products are similar in terms of data to single products, but grouped products also a have meta value with the key “_children” that lists the individual products that are linked to the group product, the query below get’s the top level information for a grouped product

SELECT wp.post_title AS `Title`, wp.post_excerpt AS `Excerpt`, t.name AS `Product Type` , wpm.meta_value AS `Children` 
FROM wp_posts wp
INNER JOIN wp_postmeta wpm ON wpm.post_id = wp.id
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
WHERE tt.taxonomy = 'product_type'
AND wp.id = 34
AND meta_key = '_children'

As you can see from the screenshot above the “children” data is stored in the form of serialized string similar to the one we saw when we looked at the top level attribute information for a variable product. We can use a similar method to the one we used in the variable product section to view an array containing all the product ids linked to the grouped product

$linked_products = unserialize('a:3:{i:0;i:14;i:1;i:15;i:2;i:16;}');
d($linked_products);

As before, the code above uses the unserialize function to convert the string value to an array and we then use the d function from the Kint debugging extension to output the array to the screen, here’s what you would see if you added the code above as a snippet to a WooCommerce store

We can now run a SQL statement to get the title of the products with the ids above

SELECT wp.post_title AS `Title`
FROM wp_posts wp
WHERE id IN (14,15,16)

and then confirm these are the same products shown on the grouped product screen

How to get Product Information for an External Product

External products have a very similar data structure to grouped products, so they are basically a single product with a piece of meta data with the key “_product_url”. The query below will return the top level data for all of the external products in the database

SELECT wp.post_title AS `Title`, wp.post_excerpt AS `Excerpt`, t.name AS `Product Type` , wpm.meta_value AS `Children` 
FROM wp_posts wp 
INNER JOIN wp_postmeta wpm ON wpm.post_id = wp.id 
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
WHERE tt.taxonomy = 'product_type' 
AND meta_key = '_product_url'

How to get a Product’s Meta Data

Meta data is linked to the post table via the id on the posts table and the post_id column on the wp_postmeta table, so we can get the meta data for a specific product using a query similiar to the one blow.

SELECT wp.id, wp.post_title, wpm.meta_key, wpm.meta_value
FROM wp_posts wp
INNER JOIN wp_postmeta wpm ON wpm.post_id = wp.id
AND wp.id = 14

Note that you will need to change the id value depending on the product you are searching for.

How to get a product category information

WooCommerce categories are stored as taxonomies with a taxonomy type of ‘product_cat’, so we can retrieve the categories links to a product using the query below

SELECT wp.id, wp.post_title, tt.parent, t.name
FROM wp_posts wp 
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
WHERE tt.taxonomy = 'product_cat'
AND wp.id = 14

As before, this query is retrieving the categories for the product with id 14 in my local database. You would need to change the id value if you wanted to retrieve the categories for a different product.

The query above will only get the categories that re directly linked to a product, it will not return the full category hierarchy for each category that a product is linked to.

As WordPress/WooCommerce stores the parent taxonomy in the wp_term_taxonomy table it is not easy to get all of a products parents using a single query. In the query below we use the parent id (16) returned by the previous query to get the parent category of the “Hoodies” and “Man” categories.

SELECT tt.term_taxonomy_id, tt.parent, t.name 
FROM wp_term_taxonomy tt
INNER JOIN wp_terms t ON t.term_id = tt.term_id
WHERE term_taxonomy_id = 16

As you can see from the results the “Clotting” category has a parent id of zero, which means it has no further ancestors, if the parent id had not been zero then we could run a similar query using the parent id and then keep repeating the process until we reach a category with a parent id of zerp.

How to get a Products Attribute Information

WooCommerce public attributes are stored as taxonomies with a taxonomy type that begins ‘pa_’ and then is followed by the name of the taxonomy, so we can retrieve the taxonomies for a product using the query below

SELECT wp.id, wp.post_title, tt.taxonomy, t.name 
FROM wp_posts wp 
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id 
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id 
INNER JOIN wp_terms t ON t.term_id = tt.term_id 
WHERE tt.taxonomy like 'pa_%' 
AND wp.id = 12

As with previous examples, this query is retrieving the top level attributes for the product with id 12 in my local database. You would need to change the id value if you wanted to retrieve the attributes for a different product.

As you can see, the query only returns the global attributes that are linked to a product. If you want to see where local attributes stored then you should check out the “How to get Product Information for a Variable Product” section of this article.