how to get product attributes from wordpress database

Question

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?

Slopwork

i tried but i couldn't get attribute values from it and i have used as $array = unserialize('a:1:{s:13:"pa_size-value";a:6:{s:4:"name";s:13:"pa_size-value";s:5:‌​"value";s:0:"";s:8:"position";s:1:"0";s:10:"is_visible";i:1;s:12:"is_variation";i‌​:1;s:11:"is_taxonomy";i:1;}}'); $var = $array['pa_size-value']; I can extract array but after that?????? – Slopwork

Answer 1

Product attributes are stored in two locations - in wp_terms, wp_term_taxonomy and wp_term_relationships (that's the first place - each attribute is preceded by pa_ for its taxonomy name - e.g. if you have a color attribute, it's under pa_color) then also as a PHP serialized array in wp_postmeta under '_product_attributes' meta_key.

You can find the method to construct the seriliazed attributes array here:

https://github.com/woothemes/woocommerce/blob/master/includes/class-wc-ajax.php

Look for function save_attributes() and add_attribute to see how the serialized array is constructed.

Update: Later versions of wooCommerce also have a serialized array in wp_options under the _transient_wc_attribute_taxonomies key and a new table called wp_woocommerce_attribute_taxonomies.

Aladdin

I would say that "wp_terms" holds attribute VALUES, not the attribute itself! – Heroic

Answer 2

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

Datary

Answer 3

Based on Fütemire's brilliant answer, this is how to get attributes by post ID

SELECT DISTINCT
    p.ID,
    t.name AS 'Term Name',
    tt.taxonomy AS 'Term Type',
    tt.description AS 'Term Description',
    (
        SELECT
            wat.attribute_label
        FROM
            wp_woocommerce_attribute_taxonomies wat
        WHERE
            wat.attribute_name LIKE REPLACE(tt.taxonomy, 'pa_', '')
    ) AS 'Attribute Name'
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.ID = 15870
    AND
    p.post_type = 'product'
    AND
    tt.taxonomy LIKE 'pa_%'

Capablanca

Thanks for the complement, glad my post helped. – Bennion

Answer 4

if you want to get every attribute as a new column you can add multiple join with filter like;

select 
      inv_sku.meta_value as sku, 
      wp.post_title as title, 
      inv_category.name as category,
      inv_subcategory.name as subcategory,
      inv_brand.name as brand,
      inv_price.meta_value as sale_price 
from wp_posts as wp 
inner join wp_postmeta as inv_sku on inv_sku.post_id =wp.ID and inv_sku.meta_key ='_sku'
inner join wp_postmeta as inv_price on inv_price.post_id =wp.ID and inv_price.meta_key ='_sale_price'
left join (
select tr.object_id, t.name  from wp_term_relationships AS tr 
left join wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
left join  wp_terms AS t ON t.term_id = tt.term_id
where tt.taxonomy ='pa_brand'
) as inv_brand on wp.ID =inv_brand.object_id 
left join (
select tr.object_id, t.name  from wp_term_relationships AS tr 
left join wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
left join  wp_terms AS t ON t.term_id = tt.parent 
where tt.taxonomy ='product_cat'
) as inv_category on wp.ID =inv_category.object_id
left join (
select tr.object_id, t.name  from wp_term_relationships AS tr 
left join wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
left join  wp_terms AS t ON t.term_id = tt.term_id
where tt.taxonomy ='product_cat'
) as inv_subcategory on wp.ID =inv_subcategory.object_id
where wp.post_type ='product'

Canicula

Answer 5

$args = array( 'post_type' => 'product','' );
$products = get_posts( $args );

foreach ($products as $product) {
    $data = get_post_meta($product->ID);            
    $pr['regular_price'] = $data['_regular_price']['0'];
    $pr['sale_price'] = $data['_sale_price']['0'];
}

Succession

When answering a question, please provide explanation associated with your code. Some people might not understand your code or don't see how it answers the question. See how to write a good answer – Dinkins

get_post_meta() is wordpress function, we can direct get the data using this function, and _regular_price is database field which is located wp_postmeta table in wordpress. – Succession