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

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:

13 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
  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

Leave a Comment

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


*