Get WooCommerce single products data from database with a SQL query

user_11426940 0
Viewed 350

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;
woocommerce product wordpress metadata sql
1 Answers

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.

user_37307540
Related