Stuck on MySQL problem

AliasZero

Hey there,

Here's the story:

I have a table in mySQL. It has 3 columns:

ID TheData Order

ID: Primary Index Unique auto_increment
TheData: nothing special about it
TheData: nothing special about it

What i am trying to do is code a script that will add and change rows in this table.

It should be able to change the Order column so that for example

Row1: 1 MyUseLessJunkData 1
Row2: 2 MyUseLessJunkData 2
Row3: 3 MyUseLessJunkData 3
Row1: 4 MyUseLessJunkData 4

would be before the edit and:

Row1: 1 MyUseLessJunkData 1
Row2: 2 MyUseLessJunkData 4
Row3: 3 MyUseLessJunkData 2
Row4: 4 MyUseLessJunkData 3

would be after the edit. It has to shift all the other rows' order column up/down so it still has 1 2 3 4 etc...

I need it so that when a new row is added, it automatically gives it the highest 'Order'. For example, if a new row was added to my example table, it would be:

Row5: 5 MyUseLessJunkData 5

Auto_increment does the job for the ID column, but it wont let me set 2 columns as auto_increment.

I also need it so that when a row is deleted, all the others shuffle and reorder themselves:

Before delete:

Row1: 1 MyUseLessJunkData 1
Row2: 2 MyUseLessJunkData 4
Row3: 3 MyUseLessJunkData 2
Row4: 4 MyUseLessJunkData 3

After delete(old row2 deleted):

Row1: 1 MyUseLessJunkData 1
Row2: 2 MyUseLessJunkData 3
Row3: 3 MyUseLessJunkData 2

I really can't think how i would do this! I have absolutely no clue :eek:

If anyone could help it would be VERY appreciated!

tomhath

Keep in mind that order is relative, so if the order is (1,2,3,4), (1,2,3,8), (1,4,5,6), etc they will sort the same.

So for starters, I'd use the ID column as the order when inserting, you know it will be the highest number in the table.

When you delete a row, do nothing, the order won't change.

Editing a row to change the order is the only time you'll need to change anything else. How many rows in the table are you dealing with?

AliasZero

around 20-30 rows.

the reason i wanted them sorted in 1 2 3 4 order is because i i will need to change the order and move them up/down. How would i know what to change the order to? Some times i might have to -5 but other times only -1 etc..

I guess i could handle my script like

NameOfThing Order: <textbox>
NameOfThing Order: <textbox>
NameOfThing Order: <textbox>

so the user updates the orders manually instead of clicking up/down. i guess that would simplify the process a bit.