SQL Querry: Change Cover img based on default combination BY POSITION!

[Kaper]

Hello guys,

 

I would really appreciate if you can help me with following query. I am trying to set img cover of the default attribute, which is working fine. But it set the first img as cover by ID, not by position from ps_image table. I would like to have the img cover based on position from that table.

Can you help? Thanks ♥

 

UPDATE ps_image SET cover = NULL;
UPDATE ps_image_shop SET cover = NULL;

UPDATE IGNORE ps_image
SET cover = 1
WHERE ps_image.id_image IN (
 SELECT ps_product_attribute_image.id_image
 FROM ps_product_attribute
 INNER JOIN ps_product_attribute_image
 ON ps_product_attribute.id_product_attribute = ps_product_attribute_image.id_product_attribute
 AND ps_product_attribute.default_on = 1
);

UPDATE IGNORE ps_image_shop
SET cover = 1
WHERE ps_image_shop.id_image IN (
 SELECT ps_product_attribute_image.id_image
 FROM ps_product_attribute
 INNER JOIN ps_product_attribute_image
 ON ps_product_attribute.id_product_attribute = ps_product_attribute_image.id_product_attribute
 AND ps_product_attribute.default_on = 1
);

UPDATE IGNORE `ps_image` SET `cover` = 1;
UPDATE IGNORE `ps_image_shop` SET `cover` = 1;

 

[Kaper]

UP: Still fighting with this

[musicmaster]

I would use a PHP loop for that. As I understand it you want to match the position field in ps_attribute with the position field in ps_image.

So in pseudo language you get something like:

$pos=1;

$qry1 = SELECT * FROM ps_attribute INNER JOIN ps_product_attribute_shop where id_product=xxx ORDER BY position

$qry2 = SELECT * FROM ps_image where id_product=xxx ORDER BY position

while ($row1 = fetch($qry1)

 { $row2 = fetch($qry2)

   assign image id to ps_product_attribute_image

}