Exploring WooCommerce Product Attribute Storage in WordPress Database

34 Comments

1. thanks.

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

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

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

5. 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?

6. Good evening. Thanks.Excellent article.

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

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

9. 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.

10. 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.

11. 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_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)

12. 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?

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

14. 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.

15. Very neat blog post. Fantastic.

    • Thank you @Shellhamer!

16. Thanks a lot!!!
    I had problems with renamed Attribut-Slugs which had no values anymore and your article saved me the total backup-process!

17. nice post

18. good post!

19. Really appreciate you sharing this blog post. Awesome.

    1

20. awesome article, thanks a lot 🙂

    1

21. Hei, I’m really looking this doc in the entire internet and just found yours. Thanks a lot.

    1

22. Hello,

    This is a great article and I have followed and removed almost all of my headaches.

    Let me give you a little background about my situation first. My customer have a WooCommerce site which need to be imported 350mb xml data (generated by an ERP system) on a weekly basis. The xml file contains 260 products and those are linked +500k variants. +500k variants is insane and I still have a doubt that all those are functional and useful. But my job is to import all of them on the website, not to ask a question 🙂

    I wrote a php script which executes query strings into MySQL database directly since importing this giant file via WP importer is impossible. All of the data properly saved into the above 5 tables. Additionally, attributes combination properly stored in `wp_posts` table as well.

    My only issue left is, product variables change only appears (takes effect) on the product frontend view when I hit the update button in the dashboard. I still confused and couldn’t find a solution for it 🙁

    Could you tell me how can I refresh/regenerate all those variable linking to the product? Or what I could missed during the process?

    Thanks a lot

    2

    • Hi again,

      I found a solution for this issue that clears transient data related to the posts when they need to take changes immediately.

      // Deleting post transients
$transients = array(
'_transient_timeout_wc_product_children_',
'_transient_wc_product_children_',
'_transient_timeout_wc_var_prices_',
'_transient_wc_var_prices_',
'_transient_timeout_wc_related_',
'_transient_wc_related_'
);

      foreach ($transients as $option_name) {
delete_option($option_name.strval($post_id));
}

      Thanks a lot

      2

23. I’m a bit confused about the wp_terms table. Can you explain it in more detail?

    1

    • Hi Emily, of course! The wp_terms table stores attribute values. For instance, if you are selling rings with two attributes – Size and Material, and you sell golden and silver rings ranging from size 16 to 20, then gold, silver, 16, 17, 18, 19 and 20 will be saved in the wp_terms table.

24. Thanks for this informative post. It helped me understand WooCommerce attributes better.

    1

    • You’re welcome, Sarah! I am glad you found it helpful. If you have any questions, feel free to ask.

25. I’ve been working with WooCommerce for a while too. One tip I’d like to share is to keep attribute names consistent for easy management.

    1

    • That’s a great tip, Mark! Consistency is key in WooCommerce. Thanks for sharing.