WooCommerce: Finding the products in database
I'm creating a website using WooCommerce and I want to restrict the available products to users depending on the postcode that they enter in the search form on my home page.
To be able to achieve that I'll have to specify the conditions of each product within the database in phpMyAdmin, but I can't seem to find it.
Does anybody know where the woocommerce database for products and/or categories are within phpmyAdmin?
Thank you in advance.
The following tables are store WooCommerce products database :
The core of the WordPress data is the posts. It is stored a
post_typelike product or
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_idof product in wp_postmeta table.
Product categories, subcategories stored in this table :
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 -
- Database Description
- Fetch the Products of Woocommerce from it’s Table in Database
Products are located mainly in the following tables:
post_idas relational index (the product ID).
product_idas relational index (the post ID) | Allow fast queries on specific product data (since WooCommerce 3.7)
product_idas 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:
object_idas relational index (the product ID)
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:
Some other product types for Subscriptions and Bookings plugins:
Since Woocommerce 3+ a new custom taxonomy named
- The product visibility with the terms
- The feature products with the term
- The stock status with the term
- The rating system with terms from
Particular feature: Each product attribute is a custom taxonomy…
- Normal tables: Wordpress database description
- Specific tables: Woocommerce database description
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