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.

I would recommend using [WordPress custom fields][1] to store eligible postcodes for each product. [add_post_meta()][2] and [update_post_meta][3] 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.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

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][1]
> - [Fetch the Products of Woocommerce from it’s Table in Database][2]





[1]:

[To see links please register here]

[2]:

[To see links please register here]

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

**Update 2020**

Products are located mainly in the following tables:

- `wp_posts` table with `post_type` like **`product`** (or **`product_variation`**),

- `wp_postmeta` table with `post_id` as relational index (the product ID).

- `wp_wc_product_meta_lookup` table with `product_id` as relational index (the post ID) | Allow fast queries on specific product data (since WooCommerce 3.7)

- `wp_wc_order_product_lookup`table with `product_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` - column `object_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` 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][1]
- Specific tables: [Woocommerce database description][2]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

get product category IDs by providing product_id (wp_posts ID column):

SELECT
term_taxonomy_id
FROM
wp_term_relationships
WHERE
object_id = PRODUCT_ID_HERE
AND
term_taxonomy_id IN
(
SELECT
term_taxonomy_id
FROM
wp_term_taxonomy
WHERE
taxonomy LIKE 'product_cat'
)

get category data (name, slug) and category hierarchical structure:

SELECT
wp_terms.term_id,
wp_terms.name,
wp_terms.slug,
wp_term_taxonomy.parent
FROM
wp_terms
LEFT JOIN
wp_term_taxonomy
ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE
wp_term_taxonomy.taxonomy LIKE 'product_cat'