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
  • do you want pure sql query or php code just like below Prasanta Hatui's answer ? – Arunendra Mar 3 '17 at 7:20
  • his php code must provide the attribute_code – hkguile Mar 3 '17 at 7:25
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
        }
    }
  • asking for sql query – Gopal Patel Mar 3 '17 at 7:13
  • but he wrote that he do not know how to get attribute label/value. – Prasanta Hatui Mar 3 '17 at 7:15
  • please read the last line. – Prasanta Hatui Mar 3 '17 at 7:16
  • answer is working perfectly, thanks for that, is there a way to get that attribute set name ex- size, color – Suneth Kalhara Apr 23 '18 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, privacy policy and cookie policy

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