Magento: SQL to Find Missing Images

Magento Icon

I just imported a ton of products for new website that I’m working on, and it turns out that the client misnamed a bunch of images, causing Magento to ignore the image import for those ones. I don’t want to scour through the website and the CSV (and the client wouldn’t want to pay us to do that either), so I figured if I could just come up with a list of SKUs for products missing an image, I could hand it off to them so they could correct everything.

This isn’t foolproof, but it does the job. This query will give you a list of SKUs where there is no record in the database for that product’s ‘image’ attribute (the main large product image).

SELECT `sku` FROM `catalog_product_entity` AS a LEFT JOIN `catalog_product_entity_varchar` AS b ON a.entity_id = b.entity_id AND b.attribute_id = 74 WHERE b.entity_id IS NULL

This entry was posted in Magento, MySQL. Bookmark the permalink.

6 Responses to Magento: SQL to Find Missing Images

  1. Pingback: Magento: Get products without images using collections | Technology & Programming

  2. s0urce says:

    SELECT *
    FROM `catalog_product_entity` AS a
    LEFT JOIN `catalog_product_entity_media_gallery` AS b ON a.entity_id = b.entity_id
    WHERE b.value IS NULL

  3. noman says:

    hay i put your query in my DB ,but its not showing configurable products….any way?

  4. Pete says:

    Here is some code to grab missing images without needing raw SQL:

    $products = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('*')
        ->addAttributeToFilter('image', 'no_selection');
  5. James says:

    I will hold a little love in my heart for you forever for posting this.

    Good job and thank you :-)

  6. _bullseye_ says:

    Brilliant!

    I’ve also run this with attribute_id 75(small image), 76(thumbnail), 77(Media Gallery), and 83(Image Gallery).

    Thank you so much for the push in the right direction.