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.

Solutions

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

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_lookuptable 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
  • 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

Similar questions

Reached end of document without finding - using mod_pagespeed
I'm getting this error repeatedly in my error.log: And I believe it's caused my site todo this when I go to the admin: Any thoughts?
Need Help Finding a Wordpress E-Commerce Plugin That Utilises Custom Post Types
I am currently in the middle of developing quite an extensive and complicated record label website that uses post types and a whole bunch of relationship mapping to map post types to one another. Part of the major functionality of the site is a store. Because I have an "Artists" post type called "artists" I would like to use a store plugin that off...
finding whether request is for post, and post id
I need a hook such that when visitor lands on any of the post (not page), I need to record a hit. How can I find whether the request is for post, and is there any hook to find if the loaded content is post, and if yes, what is post id
finding menu hierachy > wp_get_nav_menu_items
Hi I am building a menu using the code listed here. http://codex.wordpress.org/Function_Reference/wp_get_nav_menu_items Is there a way to suss out the menu hierarchy. As i can not debug this, it is difficult for me to see what wp_get_nav_menu_items actually returns. or Is there a listing that describes the objects returned via wordpress functions? ...
Finding the paragraphs in content
I am trying to create two functions, one that catches the first paragraph of some content, and one that catches the rest, but I have hit a bit of a conundrum. I have this in my single.php: which produces: each paragraph all nicely wrapped in a <p> tag. I assumed that I could simple break the explode() the content based on the string </p>...
Finding posts containing matching array elements in a meta field usign WP_Query
I have a metafield, that contains a series of options and writes the results to an array in a single meta field: I find posts that contain any of the array elements, and display them using WP_Query But this doesn't seem to work. Any idea on how to achieve this? I have edited my method, following this question: But no darts.

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.