Using values to order (and re-order) listings

[FredFredrickson2]

Well, I am getting ready to write a database where I will need the entries to be sortable by the user - that is, like how many forums work, I am going to put a column into the table that has variou8s values in it (not indexes) which the database will use to sort the information in the order the user puts it in.  I understand  how to actually order everything, but my problem is (and maybe this is more of a PHP issue?), how do I go about setting it up so one click of a plus or minus link can make an item gain or lose enough value to rise or lower in the list?

 

Confused?  Maybe this diagram will help...  this is basically how I want the output of the database to look on the page (the plus and minus symbols would be links, which should move the results up and down in the list, kinda like a friends list):

 

#1 Name (+ / -)

#2 Name (+ / -)

#3 Name (+ / -) etc.

 

If you pressed the + next to #3 Name, you'd get this:

 

#1 Name (+ / -)

#3 Name (+ / -)

#2 Name (+ / -)

 

Any suggestions on what would be the simplest / most efficient way to code this?  Any help is much appreciated!

[btherl]

The first thing that comes to mind is to swap the order values when you press + or -.  That guarantees the behaviour you want.  Deletion is also simple.  But insertion may cause problems, as you may not have enough space between two order values.  It seems messy.  Perhaps you could use floating point numbers, and insert by creating a number between the next greater and next lower values.

 

Or you can just move all subsequent values along when you insert.  That's fine as long as you don't have too many items to move.

[fenway]

Or, assuming that you're always showing all of the names, just re-do the sort order based on the newly specified order; any in general, leave "room" between your values (e.g. by 10 or 100) so that you can inject new ones.