I had this problem where I need to change the order of the products we have and update them from the most popular to least popular. So what I did, I declared a variable that will act as a counter and set the column order_num to the current value of the counter. Here’s what I did:
$db = new mysqli("server", "user", "pass", "db_name");
$query = "set @cnt := 0;";
$query .= "update tbl set order_num = (@cnt := @cnt + 1);";
$db->multi_query($query);
You can also order specific record by adding an IN clause like:
$query .= "update tbl set order_num = (@cnt := @cnt + 1) where id IN (23, 76, 9, 45) order by field(id, 23, 76, 9, 45);";