You need to create a method that will accept item_id, sort. Assign this sort to the current item. And item whose sort> =: sort do sort + 1. Move the position by one.

The easiest option is through a loop. But can it somehow be done only on sql. sort = sort + 1 WHERE sort> =: sort AND item_id <>: item_id is not appropriate, since in this case the sort values ​​will continue to grow indefinitely. And you need to grow in order from the provided sort value. Thank.

mysql

tbl products prod_id = 1 sort = 2 prod_id = 2 sort = 3 prod_id = 3 sort = 1 плохой вариант func sort($prod_id, $sort){ UPDATE products SET sort=$sort WHERE prod_id=$prod_id $prods = SELECT prod_id FROM products WHERE sort>=$sort foreach($prods as $prod){ $sort++ UPDATE products SET sort=$sort WHERE prod_id=$prod->prod_id } }
  • 1) What kind of SQL server do you have ?. 2) The question is not quite clear, add an example of input and output data. in MySQL, for example, you can operate a variable directly in a query, including on the basis of the data obtained in the last iteration of the query. - Vladimir Klykov
  • Tell me how to handle data from the previous iteration of mysql? - Vasya Pupkin
  • one
    An example of an update can be taken from here.stackoverflow.com/a/545766/194569 . And at the same time a few more ideas, because renumbering in a row is not very effective, and the task seems to be about the same as described in that question - Mike
  • @VasyaPupkin depends on the SQL version. In general, declare a variable in the query and update it after executing the required values, for example, if you want to get the previous ID and current ID in the query, you can do this: Select Id,@myId,@myId=id from sometable,@myId=0 ; Then at each iteration you will receive the current Id and previous ID - Vladimir Klykov
  • sort = sort + 1 WHERE sort> =: sort AND item_id <>: item_id is not appropriate, since in this case the sort values ​​will continue to grow indefinitely. Talk nonsense. In a single update request, each record is updated only once - even if it corresponds to several “iterations”. - Akina

0