Magento: SQL to Find Missing Images

Posted on June 29, 2010 by Josh Pratt

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.

3 Responses to Magento: SQL to Find Missing Images

  1. _bullseye_ says:
    February 7, 2011 at 9:25 am

    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.

    Reply
  2. James says:
    September 22, 2011 at 3:12 am

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

    Good job and thank you :-)

    Reply
  3. Pete says:
    October 6, 2011 at 10:53 am

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">