Swapping Problem

Hey,

I have the following (simplified) Database:

+----+----------+
| ID | Location |
+----+----------+
| 1  |    1     |
| 2  |    2     |
| 3  |    3     |
+----+----------+

Code (markup):

Where every element has an ID and a Location. These elements are displayed by their location value, and while their given ID value is permanent, their location values vary.

So, if I want to move ID=1 from Location=1 to Location=2, I must swap it with ID=2: two different elements must not have the same location value, because I want the display order to be very precise and specific.

Anyway, here's what I did. I got the table name ($Table), the row number ($Row), and either 1 or -1, depending on what I want to do ($Do: 1 swaps the element with the following one, and -1 swaps it with the previous one).

// We get element's CURRENT, or OLD location:
$Query = mysql_fetch_array(mysql_query("SELECT * FROM {$Table} WHERE ID={$Row}"));
$Old = $Query["Location"];
// We get elements's DESTINED, or NEW location ($Do = 1, in this case):
$New = $Old + $Do;
// We set whatever is in our element's destined location to its old location:
mysql_query("UPDATE {$Table} SET Location={$Old} WHERE Location={$New}");
// And we set our element's location to this new location:
mysql_query("UPDATE {$Table} SET Location={$New} WHERE ID={$Row}");

Code (markup):

The way I see it, after I run this code, my Database should look like this:

+----+----------+
| ID | Location |
+----+----------+
| 1  |    2     |
| 2  |    1     |
| 3  |    3     |
+----+----------+

Code (markup):

However, it looks like this:

+----+----------+
| ID | Location |
+----+----------+
| 1  |    3     |
| 2  |    1     |
| 3  |    2     |
+----+----------+

Code (markup):

And I really can't find the flow in this very, very simple algorithm.

Does Anyone have any idea what's the problem and how can I fix it?

Thanks in advance!

By the way: I didn't include the restricting if commands that make sure there will be no Location=0 or Location =4; I just tried to move ID=1 from Location=1 to Location=2, nothing extreme.

 

I know a great Mysql guru, I will give him this problem see if he has answer.
Will let you know

 

Thanks, very much appreciated. I'll be waiting.

 

Begin
| ID | Location |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |

$mylocation=SELECT Location FROM $Table WHERE ID=$Row
UPDATE $Table SET Location=Location-1 WHERE Location=$mylocation+1 (This will decrement you next location by one)
| ID | Location |
| 1 | 1 |
| 2 | 1 | (2-1=>1)
| 3 | 3 |

UPDATE $Table SET Location=Location+1 WHERE ID=$Row (This will update your current location to be incremented by one, we find it by ID because at this time there are two locations with location value 1
| ID | Location |
| 1 | 2 |(1+1=>2
| 2 | 1 |
| 3 | 3 |

That should do the trick.
And for consistency you should execute these statements within a transaction (BEGIN+COMMIT) otherwise your data might be mangled when the server crashes after the first update statement.