CODEWITHSUNDEEP
phpmysql
buck54321
buck54321

Reputation: 847

User defined ordering using MySQL and PHP

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.

Upvotes: 4

Views: 905

Answers (2)

Arnold Daniels
Arnold Daniels

Reputation: 16573

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

Upvotes: 8

Martin Sheeks
Martin Sheeks

Reputation: 137

You could use a "weight" int, since Order is a reserved word. As for avoiding the constant updates, you'd have to code some logic to protect against errors, but if you leave the default weight increment something other than 1, you can put things between other items.

IE:

id weight item

1 1 something

2 10 somethingElse

add something to be between the two:

id weight item

1 1 something

3 5 someOtherThing

2 10 somethingElse

Granted, a second table or simpler structure would be less error prone.

Upvotes: 0

Related Questions