WooCommerce: Finding the products in database
Update 2020
Products are located mainly in the following tables:
wp_posts
table withpost_type
likeproduct
(orproduct_variation
),wp_postmeta
table withpost_id
as relational index (the product ID).wp_wc_product_meta_lookup
table withproduct_id
as relational index (the post ID) | Allow fast queries on specific product data (since WooCommerce 3.7)wp_wc_order_product_lookup
table withproduct_id
as relational index (the post ID) | Allow fast queries to retrieve products on orders (since WooCommerce 3.7)
Product types, categories, subcategories, tags, attributes and all other custom taxonomies are located in the following tables:
wp_terms
wp_termmeta
wp_term_taxonomy
wp_term_relationships
- columnobject_id
as relational index (the product ID)wp_woocommerce_termmeta
wp_woocommerce_attribute_taxonomies
(for product attributes only)wp_wc_category_lookup
(for product categories hierarchy only since WooCommerce 3.7)
Product types are handled by custom taxonomy product_type
with the following default terms:
simple
grouped
variable
external
Some other product types for Subscriptions and Bookings plugins:
subscription
variable-subscription
booking
Since Woocommerce 3+ a new custom taxonomy named product_visibility
handle:
- The product visibility with the terms
exclude-from-search
andexclude-from-catalog
- The feature products with the term
featured
- The stock status with the term
outofstock
- The rating system with terms from
rated-1
torated-5
Particular feature: Each product attribute is a custom taxonomy…
References:
- Normal tables: Wordpress database description
- Specific tables: Woocommerce database description
The following tables are store WooCommerce products database :
wp_posts -
The core of the WordPress data is the posts. It is stored a
post_type
like product orvariable_product
.wp_postmeta-
Each post features information called the meta data and it is stored in the wp_postmeta. Some plugins may add their own information to this table like WooCommerce plugin store
product_id
of product in wp_postmeta table.
Product categories, subcategories stored in this table :
- wp_terms
- wp_termmeta
- wp_term_taxonomy
- wp_term_relationships
- wp_woocommerce_termmeta
following Query Return a list of product categories
SELECT wp_terms.* FROM wp_terms LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy = 'product_cat';
for more reference -
Bulk add new categories to Woo:
Insert category id, name, url key
INSERT INTO wp_terms VALUES (57, 'Apples', 'fruit-apples', '0'), (58, 'Bananas', 'fruit-bananas', '0');
Set the term values as catergories
INSERT INTO wp_term_taxonomy VALUES (57, 57, 'product_cat', '', 17, 0), (58, 58, 'product_cat', '', 17, 0)
17 - is parent category, if there is one
key here is to make sure the wp_term_taxonomy table term_taxonomy_id, term_id are equal to wp_term table's term_id
After doing the steps above go to wordpress admin and save any existing category. This will update the DB to include your bulk added categories