how to get product attributes from wordpress database

custom-attributes, product, wordpress

Solution

I took a little bit of a different approach, I created a stored procedure in my database that will return all terms associated with a woocommerce product. I decided to go this route because i can call the procedure from my wordpress site and the desktop app I am creating without having to write the function in two different languages.

Though I'd post it here for others to use.

CREATE DEFINER=`database_name_here`@`%` PROCEDURE `get_product_attributes`(IN ProductName TEXT)
BEGIN
SELECT DISTINCT
     p.post_title AS 'Product Name',
     t.name AS 'Term Name',
     tt.taxonomy AS 'Term Type',
     tt.description AS 'Term Description'
FROM
     wp_posts AS p
INNER JOIN
     wp_term_relationships AS tr ON p.id = tr.object_id
INNER JOIN
     wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN
     wp_terms AS t ON t.term_id = tt.term_id
WHERE
     p.post_title= ProductName
AND
     p.post_type = 'product';
END

Problem

Writing custom code to create product detail page with wordpress database. I have displayed product title, desc, price, stock, etc and got stuck up with product attributes. In the database, _product_attributes is stored in serialized manner in wp_postmeta table in database. And i couldn't unserailize attributes from it. But i found, each attribute value with it own price has been stored in wp_postmeta in some other post_id. for example, product with post_id=55 has attribute name 'Size value' having values 14 and 18 and price 300 and 350, is displayed as attributes value and price in post_id=110,111 is there any formula behind? Any idea to find this product attribute value and corresponding price value?

Original source