User defined ordering using MySQL and PHP

Question

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.

Answer 1

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