re-order array and insert results to mysql

[viveleroi0]

I have a table in MySQL in which I stores events, that have fields called "event_order".

This field indicates which event happens when, for example event 1 has an event_order of 1, event 2 has an event_order of 2, etc.

Assume I have five events total - when editing details for event 1, I decide to change it's order to 3 instead of 1. This means that everything else will have to move down, event 2 moves into third event_order, while event 5 becomes event_1 (as it was the last event).

How can I have code that resorts the orders based off of the new order of event 1, as I would then take those new orders and save them to my table.

[weekender]

UPDATE tablename SET event_order = event_order + 1 WHERE event_order >= 3

run this sql before changing the record you want to move to #3 - it will move all those three or above up one

[viveleroi0]

I've tried that - but the issue is that the last in the series will go up one, instead of going back to start of the line.

I have five items and do that function five times, instead of having orders 1-5 I'll have orders 5-10, which although they will be in the correct order, the numbers will be off.