Search code examples
phpmysqlsqlvirtuemart

Alter order for rows with same values

I transfered an old database to a new one for VirtueMart(Joomla Component), and in the database the there is a table that indicates where the media(images) should correspond (to which product they point), and the media is ordered by 1,2,3,4, etc. 1 being the main image for the product.

When I transfered the old database to the new one there was no field like the ordering.

enter image description here

The red block displays the order incorrectly(1,1,1,1), and the green block displays it correctly, I need to figure out a way to automatize the process because there are over 5000 entries in the database.

I tought of automating the process through PHP, but I can't figure out how would I compare if the rows have the same value and update the value of the order

If there is any possible way of doing this, would appreciate the help very much.

Regards.

Solution

  • You can use variables for this purpose:

    set @vpi := -1;
set @rn := 0;

update VirtueMart vm
    set ordering = (@rn := if(@vpi = virtualmart_product_id, @rn + 1,
                              if(@vpi := virtualmart_product_id, 1, 1)
                             )
                   )
    order by virtualmart_product_id, id;