Find Products with No Images

Question

Is it possible to run a query to find a list of products with no images assigned to them? Ideally I'd like SKUs printed out on the screen.

score 17 · Accepted Answer · answered Jun 16 '14 at 03:12

You can find the collection for below code.

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

you can got all product list which has no Images assign.

score 10 · Answer 2 · edited Oct 01 '15 at 03:09

10

If you want only the products that don't have image, small_image or thumbnail assigned then the answers from @KeyulShah or @TBIInfotech will give you just that.

If you want the products that have no images at all, you can run this query on the database and get them.

SELECT
    e.sku, COUNT(m.value) as cnt
FROM
    catalog_product_entity e 
    LEFT JOIN catalog_product_entity_media_gallery m
        ON e.entity_id = m.entity_id
GROUP BY
    e.entity_id
HAVING
    cnt = 0

If you remove the having statement you will get a 2 column result with the product skus and the number of images assigned to them.

You can just export that as a csv.

edited Oct 01 '15 at 03:09

STW

2,047
3
16
41

answered Jun 16 '14 at 06:41

Marius

197,939
53
422
830

By any chance Will it work in Magento2 ! – Amit Singh Oct 01 '18 at 11:52
probably, but I cannot guarantee it – Marius Oct 01 '18 at 11:59

score 5 · Answer 3 · answered Jun 16 '14 at 05:06

Just a small modification to what @keyul shah described, just put the code on magento root:

<?php 

require 'app/Mage.php';
Mage::app();
$_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' => '%/%/%'
        ),
    ));

foreach($_products as $_product){

    echo $_product->getSku();

}

Your solution worked great, I've given you an upvote but will award the answer to the original post. — Francis Kim, Jun 16 '14 at 06:51

score 4 · Answer 4 · answered Oct 04 '18 at 05:30

4

If anyone looking for Magento 2. This will work. Its same as @Marius just added one table.

SELECT 
     e.sku, COUNT(m.value) as cnt
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_media_gallery_value_to_entity r
    ON e.entity_id = r.entity_id
LEFT JOIN catalog_product_entity_media_gallery m
    ON r.value_id = m.value_id

GROUP BY
    e.entity_id
HAVING
    cnt = 0

answered Oct 04 '18 at 05:30

Amit Singh

1,765
3
19
37

I ended up writing something based on this https://magento.stackexchange.com/a/344922/70343 – Dominic Pixie Aug 20 '21 at 15:18

score 2 · Answer 5 · answered Mar 03 '17 at 05:44

This works for me....

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

This works better, because if the product has not image yet, probably the attribute relation doesn't exists, and probably wont work correctly. — Beto Castillo, Apr 02 '18 at 23:28

Find Products with No Images

5 Answers5

Linked

Related