magentomagento-1.6

Where are magento attribute values and product images stored?


I need to create a SQL query to export my Magento site products in XML format.

I can't figure where are attribute values and product images stored.

By now my query is this:

SELECT cpe.entity_id, cpe.sku, csi.qty, eav_color.value, eav_talla.value
FROM catalog_product_entity AS cpe
JOIN cataloginventory_stock_item AS csi ON csi.product_id = cpe.entity_id
JOIN catalog_product_entity_int AS eav_color ON eav_color.entity_id = cpe.entity_id
AND eav_color.attribute_id =85
JOIN catalog_product_entity_int AS eav_talla ON eav_talla.entity_id = cpe.entity_id
AND eav_talla.attribute_id =127
WHERE csi.qty >0
AND csi.is_in_stock
AND cpe.type_id =  'simple'
LIMIT 0 , 30

But I am getting attributes ids (I think). My query returns this:

entity_id           sku                 qty value   value
6000            0121011000-RED-L    2.0000     66       5

I am getting 66 as value for column attribute and value 5 for "talla" attribute.. But those values must be "RED" and "L". I don't understand in wich table are those values stored.

And on the other hand I need to get the product images but I can't figure in where table are stored.


Solution

  • Although there is EAV attribute for image it is actually stored in two tables.

    The first one is catalog_product_entity_media_gallery with columns:

    The second is catalog_product_entity_media_gallery_value with columns:

    So catalog_product_entity_media_gallery defines images for products and catalog_product_entity_media_gallery_value handles settings for different store views.