I am able to get the value of a custom attribute from the following query, however, the number doesn't help much. I would like to get the actual name of the value.

SELECT entity.sku, entity.type_id as 'type', entity_cod_custom_attribute.value as 'cod_custom_attribute'

FROM catalog_product_entity entity

LEFT JOIN catalog_product_entity_int entity_cod_custom_attribute ON entity_cod_custom_attribute.entity_id = entity.entity_id AND entity_cod_custom_attribute.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'cod_custom_attribute' AND et.entity_type_code = 'catalog_product')

This gives me: SKU-ABC, simple, 355

where 355 is actually "Adult-Large"

I would like it to show: SKU-ABC, simple, Adult-Large

It looks like I need to do a bit more connecting from the eav_attribute_option table and the eav_attribute_option_value tables.

Any suggestions?

1 Answer 1


Try following query.

$query = "SELECT ce.sku,           
            CASE ea.backend_type
               WHEN 'varchar' THEN ce_varchar.value
               WHEN 'int' THEN ce_int.value
               WHEN 'text' THEN ce_text.value
               WHEN 'decimal' THEN ce_decimal.value
               WHEN 'datetime' THEN ce_datetime.value
               ELSE ea.backend_type
            END AS value
        FROM (select sku,entity_type_id,entity_id from catalog_product_entity ) AS ce
        LEFT JOIN eav_attribute AS ea
            ON ce.entity_type_id = ea.entity_type_id
        LEFT JOIN catalog_product_entity_varchar AS ce_varchar
            ON ce.entity_id = ce_varchar.entity_id
            AND ea.attribute_id = ce_varchar.attribute_id
            AND ea.backend_type = 'varchar'
        LEFT JOIN catalog_product_entity_int AS ce_int
            ON ce.entity_id = ce_int.entity_id
            AND ea.attribute_id = ce_int.attribute_id
            AND ea.backend_type = 'int'
        LEFT JOIN catalog_product_entity_text AS ce_text
            ON ce.entity_id = ce_text.entity_id
            AND ea.attribute_id = ce_text.attribute_id
            AND ea.backend_type = 'text'
        LEFT JOIN catalog_product_entity_decimal AS ce_decimal
            ON ce.entity_id = ce_decimal.entity_id
            AND ea.attribute_id = ce_decimal.attribute_id
            AND ea.backend_type = 'decimal'
        LEFT JOIN catalog_product_entity_datetime AS ce_datetime
            ON ce.entity_id = ce_datetime.entity_id
            AND ea.attribute_id = ce_datetime.attribute_id
            AND ea.backend_type = 'datetime'";

Your Answer

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

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