WooCommerce: Finding the products in database

Question

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.

Answer 1

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 or variable_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 -

• Database Description
• Fetch the Products of Woocommerce from it’s Table in Database

Answer 2

Products are located mainly in 2 tables:

• wp_posts table with a post_type like product or product_variation,

• wp_postmeta table with the corresponding post_id by product (the product ID).

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

• wp_woocommerce_termmeta

• wp_woocommerce_attribute_taxonomies (for product attributes only)

---

Product types are handled by custom taxonomy product_type with the following default terms:

• simple
• grouped
• variable
• external

Since Woocommerce 3+ a new custom taxonomy named product_visibility handle:

• The product visibility with the terms exclude-from-search and exclude-from-catalog
• The feature products with the term featured
• The stock status with the term outofstock
• The rating system with terms from rated-1 to rated-5

Particular feature: Each product attribute is a custom taxonomy…

---

References:

• Normal tables: Wordpress database description
• Specific tables: Woocommerce database description

Answer 3

I would recommend using WordPress custom fields to store eligible postcodes for each product. add_post_meta() and update_post_meta are what you're looking for. It's not recommended to alter the default WordPress table structure. All postmetas are inserted in wp_postmeta table. You can find the corresponding products within wp_posts table.

Answer 4

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