It's definitely possible, but you have to do a little bit of extra logic, it's not something that can be done in one query.

Think about the steps when you click an up/down arrow. For example, let's say that you start with the items in the order 1, 2, 3, 4 and then you click the up arrow on #3. Two things have to happen to change the order to 1, 3, 2, 4:

• 3's position changes to 2 (it gets decreased by 1)

• 2's position changes to 3 (it gets increased by 1)

So you have to change the position for the item that they clicked up on, as well as the one that was previously in its new position. Clicking a down arrow is almost the same, but with the increase/decrease reversed.

I had the same problem, and solved it with an if statement query. Here is step by step instructions: 1. get the position of the item before updating mysql.

$query = "SELECT * FROM table WHERE id=1;
$result = mysql_query($query);
$item = mysql_fetch_array($result);

$old_position = $item["position"];

we had a form in the page so we get the new position from $_POST data.

$new_position = $_POST["position"];


we determine if we moved the item up or down

if ($new_position > $old_position) { //moved it down
$query = "UPDATE table SET position = position-1 WHERE position >= {$old_pozisyon} AND position <= {$new_pozisyon} and id <> {$id}";

}

and we do the same for the other condition but we make position+1 this time. Hope this helps

@DaveE has the right idea but results in all sorting data being either one higher or lower (depending on direction) for the other rows. Here's my PHP/MySQL:

if ($direction == 'up') {
    $q1 = "UPDATE table SET sort = ($sort +1) WHERE id != $pgid AND sort = ($sort -1);";
    if($r = mysqli_query($q1, $dbsvr)) {
        $q2 = "UPDATE table SET sort = (sort -1) WHERE id = $pgid;";
        $r = mysqli_query($q2, $dbsvr);
    }
} else if ($direction == 'down') {
    $q3 = "UPDATE table SET sort = ($sort -1) WHERE id != $pgid AND sort = ($sort +1);";
    if($r = mysqli_query($q3, $dbsvr)) {
        $q4 = "UPDATE table SET sort = (sort +1) WHERE id = $pgid;";
        $r = mysqli_query($q4, $dbsvr);
    }
} else {
    echo 'no direction';
}