show only products without images

[vietnam]

I want to modify admintracking.php in a way that shows me the products that don`t have a image yet.

 

I know how can i see all the products that have images, using this SQL statement :

 

  Quote

 

SELECT *

FROM `ps_image`

WHERE `cover` =1

 

But the reverse is not possible

 

I was thinking of something like "select products that are only in ps_product, and are not in ps_image"

 

But i don`t know how can i do something like this.

 

If you have a idea, please help me

[vietnam]

I solved it. With a bit of reading from here http://dev.mysql.com/doc/refman/5.1/en/select.html i figure it out how to compare the 2 products.

 

Now i will have to figure it how to exclude the disabled products (Solved)

 

This is directly for ps_

 

If you have another database, you have to change ps_ with your database. (Solved that too )

 

  Quote

SELECT * FROM `ps_product` LEFT JOIN `ps_image` ON `ps_product`.`id_product`=`ps_image`.`id_product` WHERE `ps_image`.`id_product` IS NULL AND `ps_product`.`active` = 1

 

So the code is like this

 

  Quote

SELECT `'._DB_PREFIX_.'product`.`id_product` FROM `'._DB_PREFIX_.'product` LEFT JOIN `'._DB_PREFIX_.'image` ON `'._DB_PREFIX_.'product`.`id_product`=`'._DB_PREFIX_.'image`.`id_product` WHERE `'._DB_PREFIX_.'image`.`id_product` IS NULL AND `'._DB_PREFIX_.'product`.`active` = 1

 

This is working with 1.4.9

[marcelo365]

where u get that?

 

where i can find? admintracking.php

thanks for helping

[Maheshmohan1093]

SELECT `ps_product_shop`.`id_product` , `ps_product_shop`.`active`,`ps_product_lang`.`name`
FROM `ps_product_shop`
INNER JOIN `ps_product_lang` ON `ps_product_shop`.`id_product`=`ps_product_lang`.`id_product`
LEFT JOIN `ps_image` ON `ps_product_shop`.`id_product` = `ps_image`.`id_product`
WHERE `ps_image`.`id_product` IS NULL
AND `ps_product_shop`.`active` =1
AND ps_product_shop.id_category_default NOT
IN ( 2 )

 

The above query should work in PS1.6 version. Please replace ps with your DB-prefix. This will retrieve the id of the product, product name and its status which is not having images in the Web.

[telefonino]

Sorry for the stupid question - but where do I find the DB-prefix?

[Maheshmohan1093]

You can find your DB-prefix in your database where all tables will be having a prefix with something like ps_tablename.

[razvy]

This is an incredible tool for finding products with no images at all.

 

Can it be changed so it could show all the products that have broken images?

I have lots of products with broken images due to errors during image import... and it's impossible to find them one by one.

 

IE: the product has 5 images, one of them is ok and the rest are just showing questin marks.

[Black RL]

This is an old thread but I had a similar problem with product cover image, here's an adaptation to find products without cover:

 

SELECT *
FROM ps_image
WHERE cover IS NULL
AND position = 1

 

Note that you might have to change position = 1 to 2, 3 or other depending on your setup, you might also try without that line:

 

SELECT *
FROM ps_image
WHERE cover IS NULL

 

But this might not be the best solution if you have multiple images per product, because this will return all images that are not flagged as cover.

 

Note: note that you have to substitute ps_ with your table prefix

Edited March 26, 2018 by Black RL (see edit history)