How to dynamically set an increasing value per row in MySQL


Problem :

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!)



Solution :

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)


Mysql Tutorials

Mysql Howto..

How to start mySQL server detached in OS X at system startup?

How to compare a date with UNIX timestamp value in php?

MySQL how to use SET type to select records where a SET contains one or more items from another SET

How to change TAB the default value column separator in Memcached mysql cluster 7.2.2 ?

How to use values retured from mysql stored function with in() function?

how to store keywords/tags

How to pass inserting old values to mysql? It inserts the old lines too

How to collect MySQL base path using JPQL?

How to save 'hidden-fields' value and change the button text 'Join' to 'Joined' Using AJAX, PHP & MySQL

How to make quoteless, comma delimited list of IDs work for IN clause within MySQL

How to convert a class object to generic object type?

How to edit invalid UTF-8 strings in mysql database

How to fetch the unmapped records in mysql

How to Manage my Comment System in Using PHP and MySQL

How to scheduled MYSQL query on a defined fixed time?

How to split a column in Mysql?

How to replace all double quotes to single quotes using mysql replace?

How to select only the first line from an raw in mysql

How to subtract one to the month of a date format in mysql? [duplicate]

MySQL. How to order by order in other table

How to group number of users by age bands in mysql

How to insert Data from textbox to MYSQL as HTML in PHP

Erlang Mysql: How to prevent SQL Injections

how to handle / in mysql queries

How to design DB for question-answer (MySql)

How to write an and operation in a SQL statement?

How can I tell why a rollback is happening in SQLAlchemy?

How to scale mysql tables for growth

How can I take variables from JavaScript and put them into a MySQL Database?

How to rewrite a NOT IN subquery as join