Magento – SQL export of products and associated image gallery files

databasegallery-imagemagento-1.9sql

I need to list all product entity_id, SKU and associated images from the value field of catalog_product_entity_media_gallery.

I need the output to be CSV based, so all images of each product will be listed in their relevant columns as shown below:

entity_id,sku,image1,image2,image3,image4
23321,prod1,/d/d/dsc_dasda.jpg,/d/d/dsc_d56.jpg,/d/d/dsc_d56.jpg,/d/d/dsc_542.jpg
32132,product2,/d/d/dsc_dsss.jpg,/d/d/dsc_d34s.jpg,/d/d/dsc_d432s.jpg,,

Not all products have the same number of associated images and I am not sure what the maximum is within the catalogue so the 4 images in the headers above is just an example.

Can anyone please help? I can't figure out the joins needed in the SQL but I imagine it is pretty simple if you know how.

I have tried the Magento export options and none can give the result I need.

Magento version 1.9.0.1

Best Answer

I figured it out:

SELECT  catalog_product_entity.sku,
        GROUP_CONCAT(CONCAT_WS(':', catalog_product_entity_media_gallery.value) SEPARATOR ',') AS Gallery,
        catalog_product_entity.entity_id
FROM    catalog_product_entity_media_gallery
        catalog_product_entity_media_gallery
INNER JOIN
        catalog_product_entity catalog_product_entity
ON     (catalog_product_entity_media_gallery.entity_id =
        catalog_product_entity.entity_id)
GROUP BY
        catalog_product_entity.sku
Related Topic