Exporting Magento Products: Where Are Attribute Values and Product Images Stored?
As an experienced developer working on Magento projects, I often receive queries about where product attributes and images are stored in the database. In this post, we’ll dive into how to extract these values for a successful XML export of your Magento store.
Understanding Magento’s Database Schema
Magento uses a complex schema composed of many tables to manage products and their attributes. The primary table is catalog_product_entity, which contains basic product information like SKU, name, status, etc.
SELECT cpe.entity_id, cpe.sku
FROM catalog_product_entity AS cpe
Storing Attribute Values
For custom or additional attributes (like color and size in your case), Magento stores them in the catalog_product_entity_int table. The mapping between product IDs and attribute values is handled through the entity_id column, which references back to the catalog_product_entity table.
Your current query:
SELECT cpe.entity_id, cpe.sku, csi.qty,
eav_color.value AS color_value,
eav_talla.value AS size_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
is on the right track but has a few issues.
Resolving Attribute Value Mapping
The value you’re seeing (66 for color and 5 for size) is actually the ID of the attribute option. To get the human-readable values (“RED” and “L”), we need to join with the eav_attribute_option_value table:
SELECT cpe.entity_id, cpe.sku, csi.qty,
eavo.value AS color_name,
eavo2.value AS size_name
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 eav_attribute_option_value AS eavo ON eavo.option_id = eav_color.value
JOIN catalog_product_entity_int AS eav_talla ON eav_talla.entity_id = cpe.entity_id
AND eav_talla.attribute_id = 127
JOIN eav_attribute_option_value AS eavo2 ON eavo2.option_id = eav_talla.value
WHERE csi.qty > 0
AND csi.is_in_stock
AND cpe.type_id = 'simple'
LIMIT 0, 30
Storing Product Images
Product images in Magento are stored in the catalog_product_entity_media_gallery table. Each row represents a single image associated with a product.
SELECT cpe.entity_id, cpe.sku,
cpg.file AS main_image,
GROUP_CONCAT(cpegg.value SEPARATOR ',') AS other_images
FROM catalog_product_entity AS cpe
LEFT JOIN (SELECT entity_id, file FROM catalog_product_entity_media_gallery WHERE is_main = 1) AS cpg
ON cpg.entity_id = cpe.entity_id
JOIN cataloginventory_stock_item AS csi ON csi.product_id = cpe.entity_id
WHERE csi.qty > 0
AND csi.is_in_stock
AND cpe.type_id = 'simple'
GROUP BY cpe.entity_id, cpe.sku
Conclusion
Extracting Magento product data requires a thorough understanding of the database schema. By joining the relevant tables and using appropriate columns, you can successfully retrieve attribute values and images for your XML export.
Remember to adjust this code according to your specific Magento version and configuration. Always test queries in a development environment before applying them to production.
For more advanced techniques like bulk data exports or performance optimizations in Magento, I recommend checking out articles on Laravel Company blog. They offer practical guides from experienced developers working with e-commerce platforms.
Happy coding!