woocommerce variable products database structure

Question

I'm building a wordpress eshop using woocommerce and I want to integrate it with an existing erp tool. I have complete the 90% of all the connections but I'm facing a problem with variable products and I can't find a solution by reading the documentation. I need some more info about how to create a variable product through the erp.

Below is what I'm currently doing:

I am creating an entry in wp_posts with post_type product and for the variation and I'm creating another entry on wp_posts with post_type product_variation and post_parent the ID of the product that holds the variation.

I believe that I'm missing something because doing only the above is not working as expected.

i'm missing something Yes you are missing many thing, first of all there are various EXTRA meta fields that needs to be added in main product postmeta table, and also you have to add taxonomy, term for main product. Moreover you also have to add postmeta table with attribute name and value for which it is variable. I'm flagging this question as too broad because it will take atleast 2 page to explain the whole logic — Raunak Gupta

Leandro Souza Leandro Souza · Accepted Answer · 2020-06-08T02:45:34

I will write what I am using.

update wp_postmeta set meta_value = 270 where post_id in (
    select id from wp_posts where post_parent = 46 and post_type = 'product_variation'
) and meta_key = '_regular_price';

... where 270 is my new price for all variable products.

... using select to test the solution:

select * from wp_postmeta where post_id in (
    select id from wp_posts where post_parent = 46 and post_type = 'product_variation'
) and (meta_key = '_regular_price' or meta_key = '_sale_price');

You can see all meta_key related to the wp_posts with:

select distinct meta_key from wp_postmeta where post_id = XX;

In my case, the 46 is the post_id. For you see what is your, open the product in wp-admin.

I hope that I was helpfull.

woocommerce variable products database structure

2 Answers