[1.7][sql]How to set all first images as coverphoto

[yairobe]

After import  all products with one image don't show me image in product list at backend. How can I set all  first image to be cover image?

[Guest]

Please, as you imported the images, what is the exact version of Prestashop.
Thank you

[Guest]

Do you need an SQL script that sets the first image of the product?

[Guest]

Has image thumbnail regeneration been set up?

[yairobe]

hI! 1.7.6.9
I  try regeneration, no luck with that. Script will be  nice    I try this  but no  luck:
 

UPDATE `ps_image` SET `cover` = NULL
UPDATE `ps_image` SET `cover` = 1 WHERE `position` = 1;

 

[Guest]

Sorry.

I'm not at the computer today, I'll write tomorrow.

Edited December 3, 2020 by Guest (see edit history)

[yairobe]

Maybe somebody have php/js script  I waiting for reply

[Guest]

Hi,

You had SQL well, you just didn't put a semicolon at the end of the first line.

UPDATE `ps_image` SET `cover` = NULL;
UPDATE `ps_image` SET `cover` = 1 WHERE `position` = 1;

 

[yairobe]

Thank for help, 5 min ago i do that   And magic works when i delete cache

[bibob]

This may not work in case `position` 1 is not set for images

in my case i have 4 images for a product which only have position  4,5,6 and 7

the best solution to quickly fix all missing cover value is to run

UPDATE IGNORE ps_image f
	set	f.cover  =	1  
where f.id_image = 
	(SELECT t1.id_image FROM ps_image t1
	 JOIN (
	  SELECT MIN(id_image) AS min_value, id_product
	  FROM ps_image 
	  GROUP BY id_product
	) AS t2 ON t1.id_product = t2.id_product AND t1.id_image = t2.min_value
	  where t1.id_product = f.id_product
	limit 1);

UPDATE IGNORE ps_image_shop f
	set	f.cover  =	1  
where f.id_image = 
	(SELECT t1.id_image FROM ps_image_shop t1
	 JOIN (
	  SELECT MIN(id_image) AS min_value, id_product
	  FROM ps_image_shop 
	  GROUP BY id_product
	) AS t2 ON t1.id_product = t2.id_product AND t1.id_image = t2.min_value
	  where t1.id_product = f.id_product
	limit 1);

 

in case you will set the cover image according to the image with lowest position number,  just change  MIN(id_image) with MIN(position) ,  but the query i provided will just set the cover image for the first uploaded image which ahve the lowest image id