User defined ordering using MySQL and PHP

mysql, php

Solution

Yes a `sorting` column works fine. You don't need a for loop for this.

When doing an insert first increment the other sorting values.

UPDATE foo
  SET sorting = sorting + 1
  WHERE sorting >= :sorting;

INSERT INTO foo
  SET name = :name, sorting = :sorting;

See it work

On updating set the `sorting` column for the specific record to the new index and increment/decrement the `sorting` for the other records to make a valid sequence.

SELECT @old_sorting:=sorting FROM foo WHERE id = :id;
UPDATE foo
  SET sorting = IF(id = :id, :sorting, sorting + IF(@old_sorting > :sorting, 1, -1))
  WHERE sorting BETWEEN LEAST(@old_sorting, :sorting) AND GREATEST(@old_sorting, :sorting);

See it work

Values of `:id`, `:name` and `:sorting` should be inserted by your mysql lib

Problem

This is more of a methodological question. Say I have a table ``` id int auto_increment primary key, name text not null ``` I have a number of entries which I want to order in some arbitrary way. I could create an interface that allows me to change the order of the names as they would appear on some page. When I read out the entries of the table, they would be ordered according to how I chose. I see three possible approaches. First approach is to add a field ``` order int not null ``` and when I changed the the order, I would have to update every row, or at least every row with ordering higher than the the lowest order I am changing. This seems like the wrong approach, as it would require doing SQL statements in a for loop. The second approach would be to create another table, linked by id ``` id int not null order int not null ``` but I would run into the same problem here. If I added a name and wanted to put them first, I would have to change the order entry in every row. I could see a possible third approach, which is to store some associations between id and order in a single column, or even in a flat file. I could see using JSON formatting to do this. My question is this. What is the best way to do this using MySQL and PHP.

Original source