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.
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:
eav_attribute
tablecatalog_product_entity
tableThe second is catalog_product_entity_media_gallery_value
with columns:
catalog_product_entity_media_gallery
rowSo catalog_product_entity_media_gallery
defines images for products and catalog_product_entity_media_gallery_value
handles settings for different store views.