WooCommerce: Finding the products in database

by

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:

More Related Contents:

  1. How to search and replace all instances of a string within a database?
  2. Error Code: 2013. Lost connection to MySQL server during query
  3. Throw an error preventing a table update in a MySQL trigger
  4. Do different databases use different name quote?
  5. MySQL Trigger after update only if row has changed
  6. Woocommerce, get current product id
  7. Binary Data in MySQL [closed]
  8. Checking if customer has already bought something in WooCommerce
  9. MySQL Query to select data from last week?
  10. Using an Alias in SQL Calculations
  11. Can a table field contain a hyphen?
  12. Adding a leading zero to some values in column in MySQL
  13. Auto Generate Database Diagram MySQL [closed]
  14. MySQL Order before Group by
  15. View’s SELECT contains a subquery in the FROM clause
  16. MySQL : Multiple row as comma separated single row
  17. How to insert data to MySQL with auto-incremented column(field)?
  18. How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
  19. Case statement in MySQL
  20. Export a MySQL Database to SQLite Database
  21. How to find out the MySQL root password
  22. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  23. How to display open transactions in MySQL
  24. MySQL query, MAX() + GROUP BY
  25. Easiest way to copy a table from one database to another?
  26. Add a line break in Woocommerce Product Titles
  27. Is it unreasonable to assign a MySQL database to each user on my site?
  28. Create programmatically a product using CRUD methods in Woocommerce 3
  29. Create a new database with MySQL Workbench
  30. mySQL SELECT upcoming birthdays

Leave a Comment