How can I find all products without images in Magento?

Question

I have some thousand products and want to find all products without an image. I tried to search for (no image) in the admin products grid, but no result. How can I make an SQL query that disables all these products?

Answer 1

Stop thinking in terms of SQL. Start thinking in terms of Magento's Models. Magento's models just happen to use SQL as a backend. Querying for things via raw SQL is possible, but is going to vary from version to version of the Magento, and may differ depending on the backend you're using.

Run the following from a test controller action, or somewhere else you can execute Magento code from. It queries the model for products with no image

//this builds a collection that's analagous to 
//select * from products where image = 'no_selection'
$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter('image', 'no_selection');

foreach($products as $product)
{
    echo  $product->getSku() . " has no image \n<br />\n";
    //var_dump($product->getData()); //uncomment to see all product attributes
                                     //remove ->addAttributeToFilter('image', 'no_selection');
                                     //from above to see all images and get an idea of
                                     //the things you may query for
}       

Answer 2

I know this is super old, but I found it helpful, so I thought I'd post an update.

As an addition to Alan's answer above, I found that there are other scenarios than just the 'no_selection' .. maybe due to plugins, or general bugs in the system? The final nlike will actually find everything, but I left the others just for fun.

Change the collection query as follows:

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(array(
        array (
            'attribute' => 'image',
            'like' => 'no_selection'
        ),
        array (
            'attribute' => 'image', // null fields
            'null' => true
        ),
        array (
            'attribute' => 'image', // empty, but not null
            'eq' => ''
        ),
        array (
            'attribute' => 'image', // check for information that doesn't conform to Magento's formatting
            'nlike' => '%/%/%'
        ),
    ));

Answer 3

I tried all of these answers in various combinations, and only got a small subset of my catalogue returned. The reason: I originally imported my products using a bespoke product image import script.

If I didn't specify images for some rows during import, the script did not create NULL or empty attribute values for those images. It simply did not create the attribute rows at all.

Since addAttributeToFilter uses an INNER join by default and there was no image attribute value to join to, the queries posted here didn't catch those SKUs.

The code below returns all products for which image, small_image or thumbnail are null, incorrectly formatted, or the row is missing entirely.

The third parameter to addAttributeToFilter allow you to specify the type of join to be used in conjunction with the OR clauses of the WHERE statement.

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(
        array(
            array(
                'attribute' => 'image',
                'null' => '1'
            ),
            array(
                'attribute' => 'small_image',
                'null' => '1'
            ),
            array(
                'attribute' => 'thumbnail',
                'null' => '1'
            ),
            array(
                'attribute' => 'image',
                'nlike' => '%/%/%'
            ),
            array(
                'attribute' => 'small_image',
                'nlike' => '%/%/%'
            ),
            array(
                'attribute' => 'thumbnail',
                'nlike' => '%/%/%'
            )
        ),
        null,
        'left'
    );

If, like me, you want to convert this to a SQL query to export as a CSV from your SQL client, simply print the query from the PHP script:

echo $products->getSelect();

I've seen some SQL posted on StackOverflow which hard-codes the attribute_id integers that refer to the image, small_image and thumbnail attributes, but these can differ from one install to another. In Magento, querying with the ORM is much better than with the SQL.

Answer 4

also, to get the sql that the query Alan describes runs behind the scenes:

echo (string) $products->getSelect();

Answer 5

I wrote up a blog article a little while back with a sql query to find missing images. It doesn't disable products, but it's a start at least: http://prattski.com/2010/06/29/magento-sql-to-find-missing-images/. Should be pretty easy to do from this point. You may have to change the attribute ID if yours doesn't happen to match mine.

Answer 6

There are both ways to do:

$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter('small_image',array('notnull'=>'','neq'=>'no_selection'));

Above code should work but in my case it was not working. So i tried following:

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

Good Luck!

Answer 7

for product without small image try this

select * from catalog_product_entity_varchar WHERE attribute_id = 86 AND value = 'no_selection'

find attribute_id in eav_attribute table

Answer 8

I only want to add that the answer of Sean Michaud is right except we don't need to use that

array (
        'attribute' => 'image', // null fields
        'null' => true
    ),

Using that page: http://bytes.com/topic/sql-server/answers/83267-wildcard-doesnt-match-using-like-varchar-field-wierd

"A NULL value is not the same as a string of zero length. NULL represents the absence of any value, and the SQL standard says that a NULL value is never equal to any other value including another NULL"

So %/%/% won't get the NULL values, but adding the code from above we will fix the error and get the image fields with NULL values. This is the result

$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter(array(
    array (
        'attribute' => 'image', // null fields
        'null' => true
    ),
    array (
        'attribute' => 'image', // check for information that doesn't conform to Magento's formatting
        'nlike' => '%/%/%'
    ),
));

If you want to work with all image attributes, the code could be like this

$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter(array(
        array (
            'attribute' => 'image', //Check for information that doesn't conform to Magento's formatting
            'nlike' => '%/%/%'
        ),
        array (
            'attribute' => 'small_image', //Check for information that doesn't conform to Magento's formatting
            'nlike' => '%/%/%'
        ),
        array (
            'attribute' => 'thumbnail', //Check for information that doesn't conform to Magento's formatting
            'nlike' => '%/%/%'
        ),
        array (
            'attribute' => 'image', //Check for null fields
            'null' => true
        ),
        array (
            'attribute' => 'small_image', //Check for null fields
            'null' => true
        ),
        array (
            'attribute' => 'thumbnail', //Check for null fields
            'null' => true
        ),
));

Answer 9

I tried all but this works for me when flat catalog is enable

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(
        array(
            array(
                'attribute' => 'image',
                'null' => '1'
            ),
            array(
                'attribute' => 'small_image',
                'null' => '1'
            ),
            array(
                'attribute' => 'thumbnail',
                'null' => '1'
            ),
            array(
                'attribute' => 'image',
                'nlike' => '%/%/%'
            ),
            array(
                'attribute' => 'small_image',
                'nlike' => '%/%/%'
            ),
            array(
                'attribute' => 'thumbnail',
                'nlike' => '%/%/%'
            )
        ),
        null,
        'left'
    );

Answer 10

You can use this SQL just to see which product doesn’t have images:

SELECT * FROM catalog_product_entity_media_gallery RIGHT OUTER JOIN catalog_product_entity ON catalog_product_entity.entity_id = catalog_product_entity_media_gallery.entity_id WHERE catalog_product_entity_media_gallery.value is NULL

Good Luck!