mysql update

[tbobker]

I am trying to build a cms and the part im struggling on is the menu positioning.
I have printed out all the pages in links with an up arrow and down arrow to represent moving the link up or down in relation to the menu links.
In my database i have a field for each page called position. This is a number from 1 to however many pages.

When i click an up or down link i figured i need to make two queries at the same time, update the new link to -1 or +1 depending on where it is going and update the link it is replacing.

I cant figure out the sql. This is what i have:

$sql3="update content set position=$posNew where id=$id3";

this just updates the new link, but how can i then update the link it is replacing?

[etully]

The safest way to do this is to find the id's of the two rows in question. When they click an up arrow or a down arrow, two pieces of data should be passed in: (1) the row in question, (2) whether they clicked up or down.

So you know that they want to move row $row in direction $direction. In that example, $row might be equal to 13 and $direction might have a value of 1 or 2 to signify up or down.

So with that information, you can calculate some values and lookup others in the database. For example:

To find the current position of row $row:
select position from content where id=$row
$current_position = mysql_result($result,0,"position");

To find the new position where this row is going
if ($direction==1) { $new_position = $current_position - 1 ; }
if ($direction==2) { $new_position = $current_position + 1 ; }

To find the id of the row that is already in that position
select id from content where position=$new_position
$other_row = mysql_result($result,0,"id");

Now you know the id's of both rows and you can simply swap positions like this:
update content set position=$new_position where id=$row
update content set position=$current_position where id=$other_row

For this to work, you need two things:
1. The links on your arrows should contain row=### & direction=#
2. You must be careful when you draw your menu so that the first row doesn't have an up arrow and the last row doesn't have a down arrow

[tbobker]

Thanks. I like the direction design! Will try it and let you know how i get on!