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
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_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_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:
wp_postmeta
WooCommerce also keep all the assigned attribute name as a serialized way in postmeta table under meta_key => _product_attributes, like this:
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).
The relations table will look like this:
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.
thanks.
There is certainly a lot to know about this topic. I really like all the points you have made.
Can you share the SQL query too? it’ll help me completing one report!
Thanks for taking the time to write down this helpful post!
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
Hi Dani,
Do you want to export product from one website and import into another?
Good evening. Thanks.Excellent article.
Major thanks for the article post.Thanks Again. Awesome.
Thanks for this great article, I have shared it on Twitter.
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.
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.
Hii, This was a great article.
It would be great if you could explain where are the customer details stored in woocommerce database.
Thanks.
Hi Dhyey,
You can get that from
wp_postmetatable use this query to get the details,meta_keyvalue’s are self explanatorySELECT * 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 ofmeta_valueis 0 zero then order was placed by Guest)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.
Hi @Dilip,
You mean to say attributes hierarchy?
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!
Hi, great article, looked at every other table. Im new to woocommerce and wordpress. Is it possible to get all the information in one table? Like the product id, name, price and its attributes? I would like to create a search table based on the attributes and bring up the products in a table which the name could hyperlink to the actual detail product view page?
Hi @Nimeshin,
I won’t say it is not possible, but even you create a table like that it would be in normalization form.
Very neat blog post. Fantastic.
Thank you @Shellhamer!
Thanks a lot!!!
I had problems with renamed Attribut-Slugs which had no values anymore and your article saved me the total backup-process!
nice post
good post!
Really appreciate you sharing this blog post. Awesome.
awesome article, thanks a lot 🙂
Hei, I’m really looking this doc in the entire internet and just found yours. Thanks a lot.