display order column

[chiprivers]

I have included a display order column in a database table that includes numbers to show which order the records should be displayed in.  i.e. the first one to be displayed will have display_order =1, the second record will have display_order = 2 etc etc

What I need now though is a script to allow a user to amend the order in which the records are displayed.  First, a page where the records will be displayed with a text input box next to them with the value defaulting to the current display_order value.  The user will then be able to alter a value and click submit to update values.

I can see that this will be easy if the user amends all values to ensure there is only one for each position, but I need the script to auto amend any effected values if only one record is changed. i.e. if the record with value 8 is amended to 4, the records 4 to 7 will need to be incremented by one

Does any body know if their is such a script anywhere to work from?

[printf]

Your going to have to explain a little more, because I do understand what you want to do, but if you are going in reverse, say the user wants to change record 4 where 8 is, what will you do with 8, does it get put where 4 was or does it just get moved down to 7.

printf

[Psycho]

Creating such a script when moving 1 record is fairly simple. If you move record 4 to record 8, then the previous 5-8 all move up one index. The complication comes when the user can change multiple records at once. What if they assign the same number to two records? (personally if the user changes records 5 and 8 both to 2, I would give give precedence to the lower number, so record 5 would become 2 and record 8 would become 3). You would need to determine the rules on how to handle mutiple record changes before going much further.

Netflix does this exact thing in the Queue page

[Psycho]

OK, I think I have an easy solution. For each record include a hidden field for the current_display_order value and include a text field for the new_display_order value (which will be prepopulated with the current_display order.

Let's take a sample of 8 records and suppose this is what is submitted:
[code]prev | new
    1    1  RecordA
    2    5  RecordB
    3    3  RecordC
    4    4  RecordD
    5    3  RecordE
    6    3  RecordF
    7    8  RecordG
    8    8  RecordH[/code]

When you receive the data put it into an array and add a new value to indicate if the value was change up or down (0-nochange, 1-moved up, -1 moved down), then sort it by new value ASC, changed DESC, then by old value ASC and you get this:
[code]prev | new | changed
    1    1  0  RecordA
    5    3  1  RecordE
    6    3  1  RecordF
    3    3  0  RecordC
    4    4  0  RecordD
    2    5  -1  RecordB
    8    8  0  RecordH
    7    8  -1  RecordG[/code]

You could then just do a foreach through the array and update the values in the database starting from 0 to x.