0

Anyone know How to get all attribute value and label of a child product of a configurable product using by direct sql?

I only know the way to find out what attributes of a configurable is using:

select * FROM `catalog_product_super_attribute` where product_id = 421

the result is

product_super_attribute_id  product_id  attribute_id    position
53                          421         92              0
54                          421         180             0

the product 421 has the following child products

//294, 295, 296, 518, 519, 888, 889, 890, 891, 892, 893, 894, 895, 896

because i don't know how the product is linking to a attribute/label/value, so how to get linked attributes label and value of the child products?

---Update---- just found some a useful sql, alter it can get my wanted result

SET @entityid = 294;

SELECT ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_decimal eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_datetime eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
2
  • do you want pure sql query or php code just like below Prasanta Hatui's answer ?
    – Arunendra
    Mar 3, 2017 at 7:20
  • his php code must provide the attribute_code
    – hkguile
    Mar 3, 2017 at 7:25

2 Answers 2

1

You can get by magento way attribute label/value by the using of following code:

    $configProductId=1;
    $configProductIdObj = Mage::getModel('catalog/product')->load($configProductId);
    $childProductsColl = Mage::getModel('catalog/product_type_configurable')->getUsedProductCollection($configProductIdObj)->addAttributeToSelect('*');
    if(count($childProductsColl)>0){
       foreach($childProductsColl as $child) {
           $associatedProductIds[]=$child->getId();
           $smproduct = Mage::getModel('catalog/product')->load($child->getId());
            //$smproduct->getId();
            //$smproduct->getName();
            //$smproduct->getDescription();
            //$smproduct->getFoodSize();//dropdown attribute value
            //$smproduct->getAttributeText('food_size');//dropdown attribute label
        }
    }
4
  • asking for sql query Mar 3, 2017 at 7:13
  • but he wrote that he do not know how to get attribute label/value. Mar 3, 2017 at 7:15
  • please read the last line. Mar 3, 2017 at 7:16
  • answer is working perfectly, thanks for that, is there a way to get that attribute set name ex- size, color Apr 23, 2018 at 17:29
1

Please give a try below mentioned code:

<?php
error_reporting(E_ALL | E_STRICT);
$mageFilename = '../app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
Mage::app('admin');
    $productId = 105;
    $product = Mage::getModel('catalog/product')->load($productId);
    $attributes = $product->getAttributes();
      foreach ($attributes as $attribute) {
            if ($attribute->getIsVisibleOnFront()) {
                $value = $attribute->getFrontend()->getValue($product);

                if (!$product->hasData($attribute->getAttributeCode())) {
                    $value = Mage::helper('catalog')->__('N/A');
                } elseif ((string)$value == '') {
                    $value = Mage::helper('catalog')->__('No');
                } elseif ($attribute->getFrontendInput() == 'price' && is_string($value)) {
                    $value = Mage::app()->getStore()->convertPrice($value, true);
                }

                if (is_string($value) && strlen($value)) {
                    $data[$attribute->getAttributeCode()] = array(
                        'label' => $attribute->getStoreLabel(),
                        'value' => $value,
                        'code'  => $attribute->getAttributeCode()
                    );
                }
            }
        }
        echo '<pre>'; 
        print_r($data); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.