I am trying to get the raw data from the database for the woocommerce_single_product_summary
in woocommerce.
I need to use the data externally, but I cannot figure out how this is placed in the database, I can gather everything else I need with:
SELECT product.ID as product_id, product.post_title as product_name, replace(product.post_content, '"', "'") as product_content, product_sku.meta_value as product_sku, product_price.meta_value as product_price, product_weight.meta_value as product_weight FROM clk_692135435d_wp_posts as product LEFT JOIN clk_692135435d_wp_postmeta as product_sku ON product.ID = product_sku.post_ID LEFT JOIN clk_692135435d_wp_postmeta as product_price ON product.ID = product_price.post_ID LEFT JOIN clk_692135435d_wp_postmeta as product_weight ON product.ID = product_weight.post_ID LEFT JOIN clk_692135435d_wp_postmeta as product_desc ON product.ID = product_weight.post_ID WHERE (product.post_type = 'product' OR product.post_type = 'product_variation') AND product_sku.meta_key = '_sku' AND product_price.meta_key = '_price' AND product_weight.meta_key = '_weight' ORDER BY product_id ASC;
Advertisement
Answer
To get the correct desired meta keys from wp_postmeta
table, you will have to look at wp_postmeta
table in your database via phpMyAdmin for a given product Id (post_id).
Then you will better use this kind of lighter SQL query instead:
SELECT p.`ID` AS product_id, p.`post_title` AS product_name, p.`post_excerpt` AS product_short_description, p.`post_content` AS product_description, Max(CASE WHEN pm.meta_key = '_sku' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_sku, Max(CASE WHEN pm.meta_key = '_price' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_price, Max(CASE WHEN pm.meta_key = '_stock_status' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_stock_status, Max(CASE WHEN pm.meta_key = '_stock' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_stock_qty, Max(CASE WHEN pm.meta_key = '_weight' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_weight, Max(CASE WHEN pm.meta_key = '_length' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_length, Max(CASE WHEN pm.meta_key = '_width' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_width, Max(CASE WHEN pm.meta_key = '_height' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_height, Max(CASE WHEN pm.meta_key = '_thumbnail_id' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_image_id FROM `clk_692135435d_wp_posts` AS p INNER JOIN `clk_692135435d_wp_postmeta` AS pm ON p.`ID` = pm.`post_id` WHERE p.`post_type` = 'product' AND p.`post_status` = 'publish' GROUP BY p.`ID` ORDER BY p.`ID` ASC;
Tested and works.
So you need for each required meta data key / value pairs from wp_postmeta
table, you will have to add the following line in he SQL query (separated by a coma):
Max(CASE WHEN pm.meta_key = '_the_meta_key' AND p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS the_column_name
where the _the_meta_key
is the desired meta_key and the_column_name
the desired column name.