How Product Attribute are Stored in Database

How Product Attribute are Stored in Database

product attribute banner

Whenever I install any new plugin I have a great curiosity as for how they are storing data in the database, so I take a look in core files and study them. I’m playing with WooCommerce from a long time and gone through its table for the numerous number of time but didn’t think that there are developer/users who are looking for such kind of tutorial. So today I decided to put my Product Attribute knowledge into a single piece.

A product attribute is a characteristic that defines a particular product and will affect a consumer’s purchase decision. WooCommerce uses the WordPress default tables to store the entire product attribute.

Let take an example, of Woo Product Happy Ninja

Woo Product

In the above product, there are 2 attributes namely Size and Color now let see how it is stored and from where they are fetched.

wp_term_taxonomy

The entire attribute category is stored in this table with a prefix.pa_ For example, if we add Color then it will be stored as:

wp_term_taxonomy data
wp_terms

The entire attribute’s value is stored in this table with their name and slug. So for example, if we have Black, Blue, Seal Brown color then it will be stored as:

wp_terms data
wp_term_relationships

It stores the mapping of the post/product ID with the term ID, So, for example, the above product Happy Ninja has attribute color with values Blue and White then it will be stored as:

product attribute data are stored

wp_postmeta

WooCommerce also keep all the assigned attribute name as a serialized way in postmeta table under meta_key => _product_attributes, like this:

wp-postmeta product data

So in the frontend or singles product field, attributes names are displayed from _product_attributes meta_key and the values are displayed from wp_terms.name, (obviously with relational data).

additional_info

The relations table will look like this:

wp table relationship

MySQL Query to Get all the Attributes Associated with Product(s)

Hope you all enjoyed this article and if you have any query then let me know through comments so that I can reply one by one.

Share this Post:

About Raunak Gupta

I'm an expert Laravel, WooCommerce, WordPress theme and plugin developer, over the time I have developed several sites and applications. I like to help the upcoming developer, So I share my experience through this blog.  

Related Posts:

27 thoughts on “How Product Attribute are Stored in Database

  1. lykyik@gmail.com'

    There is certainly a lot to know about this topic. I really like all the points you have made.

    Reply
  2. roryrentoul@gmail.com'

    Can you share the SQL query too? it’ll help me completing one report!

    Reply
    • cbrconvers@gmail.com'

      Yes, Webhat/Gupta, it would help much more if you look at updating this article with the inclusion of SQL ( or `dbDelta()` or `$wpquery->$webhat_args` )… you get the idea, please share.
      I subscribe waiting for your edit. Cheers.

      Reply
    • Hi Perkosa

      I have updated the article with the SQL query. Thanks for the suggestion.

      Reply
  3. steffen@gourami.de'

    Thanks for taking the time to write down this helpful post!

    Reply
  4. info@edmancenter.com'

    hello
    i have a website which have more than 15.000 product with more than 2.000 product’s attribute , i think its impossible to import product with csv file or xml file , or its better to say , its impossible to make xml or csv file, whats your idea? what’s a best way? please guide me
    thanks

    Reply
  5. Tonks@gmail.com'

    Good evening. Thanks.Excellent article.

    Reply
  6. Dreiss@fargus.eu'

    Major thanks for the article post.Thanks Again. Awesome.

    Reply
  7. madie.timm@bigstring.com'

    Thanks for this great article, I have shared it on Twitter.

    Reply
  8. Heath@gmail.com'

    I really can’t believe how great this site is. Keep up the good work. I’m going to tell all my friends about this place.

    Reply
  9. numbersroberson@gmail.com'

    Nicе post. I waѕ checking continuously tһis blog and I am
    inspired! Very useful info particularly the final
    рart 🙂 I deal with such info a ⅼot. I used to be looking for this particular info for a very long time.
    Thanks and good luck.

    Reply
  10. dhyeythaker00@gmail.com'

    Hii, This was a great article.
    It would be great if you could explain where are the customer details stored in woocommerce database.
    Thanks.

    Reply
    • Hi Dhyey,
      You can get that from wp_postmeta table use this query to get the details, meta_key value’s are self explanatory
      SELECT * FROM `wp_postmeta` WHERE `post_id` = 123 (replace 123 by your Order ID)

      If you want to know the User ID/Customer ID then use this query
      SELECT * FROM `wp_postmeta` WHERE `post_id` = 123 AND `meta_key` = '_customer_user' (If the value of meta_value is 0 zero then order was placed by Guest)

      Reply
  11. dilipsharma640@gmail.com'

    Where categories and subcategories attributes of a product is stored? I want to store that when an order is placed, I have to keep the info that from which category particular order is.
    Sorry for bad English.

    Reply
  12. email@gmail.com'

    wkz0UJ You ave made some good points there. I checked on the net to find out more about the issue and found most individuals will go along with your views on this web site.

    Reply
  13. kyawthet.2017.flc@gmail.com'

    Hello I would like to know that How could I do for the below ones
    color size count
    red small 3
    cyan medium 55
    blue big 2
    Please help me!

    Reply
    • hello@webhat.in'

      Hi Aungthura,

      Here is the MySQL query that will help you to achieve the desired result.

      SELECT
      p.`ID`,
      MAX(case when pm.`meta_key` = "_stock" then pm.`meta_value` end) as stock,
      MAX(case when pm.`meta_key` = "attribute_size" then pm.`meta_value` end) as size, -- replace attribute_size with your size attribute name
      MAX(case when pm.`meta_key` = "attribute_color" then pm.`meta_value` end) as color -- replace attribute_color with your color attribute name
      FROM `wp_posts` AS p
      LEFT JOIN `wp_postmeta` AS pm ON p.ID = pm.post_id
      WHERE p.`post_parent` = 34 -- replace it with product ID whose varient count you want to get.
      AND pm.`meta_key` IN ('_stock', 'attribute_size', 'attribute_color')
      GROUP BY p.`ID`

      Reply
  14. jasirec@gmail.com'

    I execute this query to get varible products, but its returning only product name and details, i need to get product attributes and price as well. i created to products with attribute values as varible products. Please help me regarding this
    “SELECT p.*,`post_content`,`post_excerpt`,t.*,tt.*,pm1.*,
    MAX(CASE WHEN pm1.meta_key = ‘attribute_pa_packages’ THEN pm1.meta_value END) as attributes
    FROM kk_posts p
    LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
    LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
    LEFT JOIN wp_woocommerce_attribute_taxonomies AS wat ON wat.
    JOIN wp_term_taxonomy AS tt ON tt.taxonomy = ‘product_cat’ AND tt.term_taxonomy_id = tr.term_taxonomy_id
    JOIN wp_terms AS t ON t.term_id = tt.term_id
    WHERE p.post_type in(‘product’, ‘product_variation’) AND p.post_status = ‘publish’ AND t.slug = ‘package’
    GROUP BY p.ID,p.post_title ORDER BY pm1.meta_value+0 DESC”

    Reply
  15. tangela.greenfield@gmail.com'

    whoah this blog is magnificent i love studying your posts.
    Keep up the great work! You realize, a lot of individuals are hunting around for this info, you can aid them greatly.

    Reply
  16. blythegreaves@inbox.com'

    I’m very pleased to discover this site.
    I need to to thank you for your time for this fantastic read!!
    I definitely savored every part of it and I have you book-marked to
    check out new things in your site.

    Reply
  17. hollie_auld@hotmail.com'

    Hello my loved one! I wish to say that this article is awesome,
    nice written and include approximately all important infos.
    I would like to see more posts like this .

    Reply
  18. koolabhishek837@gmail.com'

    Thanx… this post really helpful for me 🙂

    Reply
  19. aradadiya1636@gmail.com'

    Woocommerce Product Table View for also good woocommmerce product table list to add to cart product in woocommerce

    Reply
  20. dave@solarbotics.com'

    Helpful – thanks.
    One issue left to cover is term SORTING. I have a site with multiple attributes that have 60~100 terms that cannot be sorted by traditional means as many values have multiplier suffixes, I.e.: 1k is larger than 470, but 1k shows up first in the list. I can manually drag them into the right order in the “Configure Terms” link on the attributes page, but that’s untenable.

    I see it is possible to sort attributes by Products–>Attributes–>[Edit an Attrib value]–>Default Sort order –>”Custom ordering” or even by “Term ID”.

    I’m trying to determine what is the manual sorting mechanism so I can bulk change/upload my terms rather than spending hours dragging values up and down a tree across 6 pages.

    I’m thinking I can either rearrange the order in whatever table contains the order (wp_term_relationships has a “term_order” field. Hmmm…) or rebuild the table that contains the Terms in the right order. Just have to figure out the relationships.

    Any insights?

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *


*