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

Question

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

Answer 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
        }
    }

Answer 2

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);