In a PrestaShop online store I have found products that have some images, but none are marked as a cover image, so these products are listed in the frontoffice without an image.
I don't know the cause of this situation, but I want to fix it.
How can I set a cover image for all those products that have some registered image, but none are marked as the cover image?
Note.- This solution is valid for a PrestaShop with the multi-store option disabled. The solution for multi-store needs to be elaborated a little more.
The first thing we are going to do is identify all the products that are in that situation (they have some image but none is the cover one), and we are going to save the lowest ID of their images in a temporary table. We apply this query:
CREATE TABLE temp_image_id
AS SELECT MIN(id_image) AS id_image
FROM ps_image
WHERE id_product NOT IN (
SELECT id_product FROM ps_image WHERE cover = 1
)
GROUP BY id_product;
The next step is to mark cover for all those images:
UPDATE ps_image SET cover = 1
WHERE id_image IN (
SELECT id_image FROM temp_image_id
);
As we are almost 100% sure we are in a PrestaShop version 1.5 or higher, we update cover in the image_shop table:
UPDATE ps_image_shop `is`
JOIN ps_image `i` ON `i`.id_image = `is`.id_image
SET `is`.cover = `i`.cover;
Finally, we delete the temporary table that we used to know which image IDs we had to cover:
DROP TABLE temp_image_id;
Now we can check if there are no longer any products with an image but no cover image:
SELECT COUNT(DISTINCT id_product)
FROM ps_image
WHERE id_product NOT IN (
SELECT id_product FROM ps_image WHERE cover = 1
);
We should get zero.
Note.- We use a temporary table, because we cannot put everything together in a single query, well, yes we can, but that query will not work, it will give an error because it will try to update on a field (cover) that is part of the filter in the query. That can't be done.
Note.- Be aware of PrestaShop tables prefix, in queries from above we're using ps_, as is the default prefix.