First lets have quick look on WordPress database schema:
Database description: https://codex.wordpress.org/Database_Description
WordPress CMS stores most information about articles and pages with in wp_posts and wp_postmeta tables.
Now lets install WooCommerce plugin a addon extending our CMS to fully featured e-commerce solution.
Database description: https://github.com/woocommerce/woocommerce/wiki/Database-Description
By now you probably see that there is actually no product table… How it comes? Well the thing is that the product table does not exist (more on that later).
Take a pick at your database installation file is under includes/class-wc-install.php in your WooCommerce plugin folder.
How is WordPress database build and why?
If you have some time to spare i do recommend to watch Stoyan Cheresharov introduction to WordPress and WooCommerce database structure. This video is almost two hour long so if you have no time or can not YouTube at work read the extended article below.
Developers in general do not like to deal with WP database. This is because WP database ureses a very weird approach which is combining both relationship and mimic of nosql approach (example: key value columns in meta tables) in SQL database as well as storing some data as php serialized objects, which if not handled properly can get corrupted very easy. Serialized data is most likely to be edited on application level (where data get unserialized > modified > serialized > saved to database).
If you need to work with WordPress database it is curtail to understand how data is related, stored and should be used.
In WordPress almost everything is stored in the wp_posts table with different post_type value. So articles, pages, products, manu items, images, and so on … are all stored as “posts” in wp_posts table. Knowing that we should really think about generic object when fetching data from wp_posts table. Then using join we need to pull all specyfc post_type related meta_data stored in wp_postmeta and any other related tables if needed.
Lets have a look on how wp_posts and wp_postmeta tables where created:
show create table wp_posts; | wp_posts | CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT 0,
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`(191)),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+------------------------------------------------------------------------------show create table wp_postmeta;
+-------------+---------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| wp_postmeta | CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=1958 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------------+---------------------------------------------------------------------------Note that even that we know that there are relation between certains columns between those tables, we will not see any foreign key constraints. This is how the WP database was design in general. This is why we need to be aware of those constraints and push them in to code logic instead.
We could say that this is bad design of WP database but WP is all about flexibility which in this example comes at cost of data consistency.
As we already know in WP database almost everything is stored as “post” in wp_posts table including (pages, articles, products, menu items, images, uploaded files and so on…).
Now after installing WooCommerce plugin and adding some demo products let’s have a look at some example sql query which selects products from WordPress database:
SELECT
p.ID,
p.post_title,
`post_content`,
`post_excerpt`,
t.name AS product_category,
t.term_id AS product_id,
t.slug AS product_slug,
tt.term_taxonomy_id AS tt_term_taxonomia,
tr.term_taxonomy_id AS tr_term_taxonomia,
MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
FROM wp_posts p
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND p.post_content <> ''
GROUP BY p.ID,p.post_title;Note how the above SQL query referencing wp_posts table (aliased as p) which is a starting and main p.ID reference of product which joins with wp_postmeta table (aliased as pm1) with a reference to pm1.post_id column and wp_term_relationships table (aliased as tr) which reference to p.ID from wp_posts table with tr.object_id column.
Lets query WP database with WooCommerce installed for some products basic informations from wp_posts table with left join to wp_postmeta table:
SELECT p.ID, p.post_name, p.post_title, p.post_content, p.post_type, pm1.* FROM wp_posts p
LEFT JOIN wp_postmeta as pm1 on p.ID = pm1.post_id
WHERE p.post_type = "product"
ORDER BY p.ID ASC;should return something like:
Select all meta values for WooCommerce products:
SELECT p.ID, p.post_name, pm1.* FROM wp_postmeta pm1
RIGHT JOIN wp_posts AS p ON pm1.post_id = p.ID
WHERE p.post_type = "product";
Outputs:
From the above output you can notice that some meta_key records have serialized data as its value stored in meta_value column.
Product categories in WooCommerce database:
Information about product category are stored in three tables. wp_terms – stores names (not only categories)wp_term_relationships – join terms rows with actual product id wp_term_taxonomy – which stores information about taxonomy type
Tables structure:
+----------+----------------------------------------------------------------------------
----------------------------------------------------------------------------------------
| wp_terms | CREATE TABLE `wp_terms` (
`term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`term_group` bigint(10) NOT NULL DEFAULT 0,
PRIMARY KEY (`term_id`),
KEY `slug` (`slug`(191)),
KEY `name` (`name`(191))
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------
----------------------------------------------------------------------------------------
+-----------------------+--------------------------------------------
---------------------------------------------------------------------
| wp_term_relationships | CREATE TABLE `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`term_order` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------------------+--------------------------------------------
---------------------------------------------------------------------+------------------+-------------------------------------------------------------------
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
| wp_term_taxonomy | CREATE TABLE `wp_term_taxonomy` (
`term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`term_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`taxonomy` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`parent` bigint(20) unsigned NOT NULL DEFAULT 0,
`count` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------------+-------------------------------------------------------------------
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------Select all product categories
SELECT * FROM wp_terms t
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
WHERE tt.taxonomy = "product_cat"Output:
SQL to Select all products with categories
SELECT p.ID, p.post_name, tr.term_taxonomy_id, tt.taxonomy, t.term_id, t.name from wp_posts p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = "product" AND tt.taxonomy = "product_cat";Output:
SQL WooCommerce – Select only products and categories of variable or simple product type
SELECT p.ID, p.post_name, tr.term_taxonomy_id, tt.taxonomy, t.term_id, t.name from wp_posts p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = "product"
AND tt.taxonomy = "product_cat"
AND p.ID IN(
SELECT p.ID FROM wp_posts p
LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN wp_terms t ON tt.term_id = t.term_id
WHERE p.post_type = "product"
AND t.name = "variable" -- replace variable with simple to see simple product list
);Output
WooCommerce database – How are variable products represented in database?
Main product object as well as each product variation is stored as separate record in wp_post table. To see all variation products in your DB run:
SELECT * from wp_posts p where p.post_type="product_variation";Notice the post_parent vale for each variation data.
If you want to select all variable product including main product for certain ID use the sql query below:
SELECT * from wp_posts p where p.post_type="product_variation" AND post_parent = 375 OR p.ID = 375;Outputs:
+-----+-------------+---------------------+---------------------+------------------------------------------+-----------------------------+--------------------------+-------------+----------------+-------------+---------------+-----------------------------+---------+--------+---------------------+---------------------+-----------------------+-------------+--------------------------------------------------+------------+-------------------+----------------+---------------+
| ID | post_author | post_date | post_date_gmt | post_content | post_title | post_excerpt | post_status | comment_status | ping_status | post_password | post_name | to_ping | pinged | post_modified | post_modified_gmt | post_content_filtered | post_parent | guid | menu_order | post_type | post_mime_type | comment_count |
+-----+-------------+---------------------+---------------------+------------------------------------------+-----------------------------+--------------------------+-------------+----------------+-------------+---------------+-----------------------------+---------+--------+---------------------+---------------------+-----------------------+-------------+--------------------------------------------------+------------+-------------------+----------------+---------------+
| 375 | 1 | 2021-11-28 12:39:47 | 2021-11-28 11:39:47 | Wyczerpujący opis produktu w tym miejscu | Pomidor | | publish | open | closed | | pomidor-2 | | | 2021-11-29 00:19:28 | 2021-11-28 23:19:28 | | 0 | https://rolagra.dv/?post_type=product&p=375 | 0 | product | | 0 |
| 382 | 1 | 2021-11-28 12:39:48 | 2021-11-28 11:39:48 | | Pomidor - 1kg | ilość: 1kg | publish | closed | closed | | pomidor-1kg-2 | | | 2021-11-28 12:39:49 | 2021-11-28 11:39:49 | | 375 | https://rolagra.dv/?post_type=product&p=382 | 1 | product_variation | | 0 |
| 383 | 1 | 2021-11-28 12:39:48 | 2021-11-28 11:39:48 | | Pomidor - 1 skrzynka 15kg | ilość: 1 skrzynka 15kg | publish | closed | closed | | pomidor-1-skrzynka-15kg-2 | | | 2021-11-28 12:39:50 | 2021-11-28 11:39:50 | | 375 | https://rolagra.dv/?post_type=product&p=383 | 2 | product_variation | | 0 |
| 384 | 1 | 2021-11-28 12:39:48 | 2021-11-28 11:39:48 | | Pomidor - 10 skrzynek 150kg | ilość: 10 skrzynek 150kg | publish | closed | closed | | pomidor-10-skrzynek-150kg-2 | | | 2021-11-28 12:39:50 | 2021-11-28 11:39:50 | | 375 | https://rolagra.dv/?post_type=product&p=384 | 3 | product_variation | | 0 |
| 385 | 1 | 2021-11-28 12:39:48 | 2021-11-28 11:39:48 | | Pomidor - 1 Paleta 300kg | ilość: 1 Paleta 300kg | publish | closed | closed | | pomidor-1-paleta-300kg | | | 2021-11-28 12:39:50 | 2021-11-28 11:39:50 | | 375 | https://rolagra.dv/?post_type=product&p=385 | 4 | product_variation | | 0 |
+-----+-------------+---------------------+---------------------+------------------------------------------+-----------------------------+--------------------------+-------------+----------------+-------------+---------------+-----------------------------+---------+--------+---------------------+---------------------+-----------------------+-------------+--------------------------------------------------+------------+-------------------+----------------+---------------+How to deal with serialized data in php.
To serialize data we use serialize() function to unserialize we can use unserialize().
https://stackoverflow.com/questions/39776601/sql-query-to-get-all-products-categories-and-meta-data-woocommerce-wordpress
https://www.youtube.com/watch?v=KOjGDlhoLFg
https://www.php.net/manual/en/function.serialize.php
https://www.php.net/manual/en/function.unserialize.php
https://stackoverflow.com/questions/25766378/wordpress-show-unserialize-data-in-a-smart-way
https://www.webhat.in/article/woocommerce-tutorial/how-product-attribute-are-stored-in-database/