Change Record Order in db

[kbc1]

Hi all

There might be a common solution out there to this so hopefully someone can advise.

In my db table, I have about 150 records, each have a displayorder value of 1 - 150 (which I manually set in the db when I inserted the records).

Now, I have a built a script which lists all of these records and allows me to edit the content relating to a chosen record, but I need to look at how I go about changing the 'displayorder' of each record by (i'm assuming) clicking an icon or up/down link for each record, thus changing it's display order order.

Any ideal ways of achieving this?

I have in mind that if I use GET, I could pass the ID of the record I am changing and run an UPDATE query to change its displayorder value to 1 more or 1 less (depending on the link clicked) but that would mean I would have to run 2 other queries to UPDATE the other 2 records which are beside it in the 'displayorder' value and i'm concerned that is overkill and I have missed a much simpler method.

I hope that makes sense.
Thanks for reading.

K

[sneakyimp]

I'm not 100% certain, but I don't think there's any way around running 2 queries to swap the order of two records. You'll need one query to change the moving record and another query to move the one next to it. NOTE that makes 2 queries, not 3.