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?

@Michael Myers: Why exactly did you edit this almost a full year after it was posted? — Zéychin, Jul 15 '11 at 17:46
@Zéychin: There's no statute of limitations on edits. Any time you see something which could be improved, please feel free to go ahead and do it. In this case, I was here because someone posted a non-answer which I came to delete. — Michael Myers, Jul 15 '11 at 17:53
Oh yes, that's fair. I just see: edited by..., and from the way it is placed, it seems that you apparently edited the question, not removed the (false) answer! That makes much more sense. I salute your work for this community. — Zéychin, Jul 15 '11 at 17:58

score 15 · Answer 1 · answered Aug 25 '10 at 17:06

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
}

@swl1020 I haven't tested this, but a standard not equal filter (`neq` ) should work. ``->addAttributeToFilter('image', array("neq"=>'no_selection'));` Full list of filtering options are available in this article: http://alanstorm.com/magento_collections — Alana Storm, Feb 27 '13 at 01:14
Collections are far better than debugging the self-obfusticating SQL statements necessary to work with EAV any day of the week. The work is already done for you, you only have to pay attention to the logic you use to work with the result. — Fiasco Labs, Mar 09 '13 at 20:16

score 6 · Answer 2 · answered May 20 '13 at 20:29

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' => '%/%/%'
        ),
    ));

score 4 · Answer 3 · answered Mar 30 '15 at 09:17

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.

this solution worked for my 1.9 installation – gonza May 06 '23 at 22:24 — gonza, May 06 '23 at 22:24

score 1 · Answer 4 · answered Nov 14 '11 at 19:14

1

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

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

answered Nov 14 '11 at 19:14

timbroder

858
1
13
25

score 0 · Answer 5 · answered Apr 17 '13 at 07:12

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!

score 0 · Answer 6 · answered May 02 '14 at 16:33

0

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

answered May 02 '14 at 16:33

Diaconescu Doru

21
1

score 0 · Answer 7 · answered Feb 09 '15 at 10:00

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
        ),
));

score 0 · Answer 8 · answered Aug 25 '10 at 11:43

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.

score 0 · Answer 9 · answered Mar 03 '17 at 05:41

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'
    );

score 0 · Answer 10 · answered Mar 08 '17 at 11:55

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!

How can I find all products without images in Magento?

10 Answers10

Linked