[RESOLVED] Moving Record Order

[alirezaok]

hi
i have a table with 3 fields: ID Name Order
ID is autonumber and primery key

as example: (records order by Order Field)

ID Name Order
4 book 1
3 tree 2
2 red 3
1 blue 4
5 black 5

i wan to change its order
as example i want to move record with ID=1 to after the record with ID=4

new result must be:
ID Name Order
4 book 1
1 blue 2
3 tree 3
2 red 4
5 black 5

you see most of records have new Order filed numbers with this one changing.

any one can help me how do i can change Order field for reaching to this result?

if you show me an example script i thanks of you

[Piranha]

You can do this quite easy with 2 queries (assuming that you know where it is ordered now and where you want it to be ordered in the future). Let's take the above example as the example:

$id_to_move = 1;
$order_to_move = 4;
$order_to_move_to = 2;
$sql = "UPDATE table SET order = order + 1 WHERE order BETWEEN $order_to_move_to AND $order_to_move";
// Run query
$sql = "UPDATE table SET order = $order_to_move_to WHERE id = $id_to_move";
// Run query

[alirezaok]

thank you very much....