How to dynamically set an increasing value per row in MySQL

Question

I am creating a web application that acts as a priority list, allowing a user to re-arrange a set of items in a list and storing that arrangement in a MySQL database via a PHP backend.

At the moment, when the user has finished arranging their items and saves the configuration, I am calling a seperate UPDATE statement for every row, plugging in the priorityLevel (position in the list) for each.

For example: (not my production code, but this illustrates the point)

<?php
  $items = array(12, 17, 27, 26, 5); // an array of IDs, in proper order
  // due to nature of foreach loop in PHP, this makes $order be the key value,
  // in this case, 0, 1, 2, 3, 4 respectively
  foreach ($items as $order => $item)
  {
    $database->query("UPDATE `table` SET `priorityLevel` = {$order} " .
                     "WHERE `ID` = {$item}");
  }
?>

This works well enough, but it seems inefficient to run so many UPDATE queries, especially when the list gets long and we're re-ordering many items. I was hoping there was some way to dynamically create the order within the UPDATE command, something like this:

<?php
  $database->query("UPDATE `table` SET `priorityLevel` = ORDER_NUMBER " .
                   "WHERE `ID` IN (12, 17, 27, 26, 5)");
?>

... where ORDER_NUMBER was a dynamic number determined perhaps by the position of the ID value in the WHERE clause. It would need to respect the order of the items in the WHERE clause as well to make sure the priorityLevel was set according to the user's wishes.

Does anyone know if this is possible in a MySQL 5 / PHP 5 environment? The table is using the MyISAM engine so it does not support transactions.

(PS - I know some of the basics of MySQL well enough, but I am far from an expert, so if there is anything involved here beyond simple CREATE,INSERT,UPDATE,DELETE commands, please be descriptive)

(PPS - This subject is hard to Google, as the terms are too generic. If I search for 'mysql order', I get a bunch of tutorials on creating Shopping Cart applications for MySQL!)

Answer 1

You have to build a query in PHP like this and send that query to mysql:

UPDATE `table`
SET priorityLevel = 
CASE 
WHEN `ID` = 12 THEN 1
WHEN `ID` = 17 THEN 2 
WHEN `ID` = 27 THEN 3
WHEN `ID` = 26 THEN 4
WHEN `ID` = 5 THEN 5
END 
WHERE `ID` IN (12, 17, 27, 26, 5);

(Tested with MySql 5.0)

Answer 2

You could also make the loop faster if you use a prepared statement, which lets you change the specific values while the MySQL server keeps the query in memory. This requires using the MySQLi extension, instead of the regular MySQL one.

Something along these lines:

$mysql = new mysqli(HOST, USER, PASS, DB);

$stmt = $mysql->prepare("UPDATE items SET sortorder = ? WHERE id = ?");

$stmt->bind_params('ii', $sort, $id);

foreach ( $items as $sort => $id ) {
    $stmt->execute();
}