`position`列在数据库中，如何重新排序？

Question

I have the following table:

id | name | position
1    Bob     4
2    Jim     5
3    Harry   73
4    Paul    89

I want the user to be able to re-order the columns as they see fit, like move one row up and down, using the position column. I have thrown in the big numbers in there (73 and 89) just to cater for all events.

Is there a way to dynamically re-order the table via SQL? Or will I have to manually re-order all tables when someone selects 'move up' on 'Harry'? I can only imagine I will have to:

1. Find the ID before Harry's (in this case '2')
2. Move all ID's (including '2's) up by one.
3. Set Harry's position to to '2'.

There must be a quicker easier way to do this using MySQL/PHP?

Answer 1

For those that find this page in the future, I found some code from an old shop that did the trick:

if((isset($_POST['moveup_x'])) || (isset($_POST['movedown_x']))) {
$current = select("SELECT `position` FROM `events` WHERE `id`='".rEsc($eventid)."';", true,true);

    if(isset($_POST['moveup_x'])) $target = ($current - 1); 
    if(isset($_POST['movedown_x'])) $target = ($current + 1); 

    if($current > 1) {
        $counter = 1;
        foreach(select("SELECT `id` FROM `events` ORDER BY `position`;") as $val) 
        {
            $newposition = $counter;
            if($current == $newposition) {
                $newposition = $target;     
            } elseif(($current > $newposition) && ($target <= $newposition)) {
                $newposition++;
            } elseif(($current < $newposition) && ($target >= $newposition)) {
                $newposition--;
            }
            runSQL("UPDATE `events` SET `position`='{$newposition}' WHERE `id`='{$val['id']}';");
            $counter++;
        }
        array_push($msgs,"Successfully moved event.");
    } else {
        array_push($msgs,"!Cannot move the event any higher up the list.");
    }
}

You need to know your current, which you can get from $_POST (in this case I got from rowID in the database) and your target (I had img submit buttons so I just minused or plussed from whichever button the user clicks). Once you have that, loop through each row in the table however you see fit, (set a counter beforehand) and run the IF/ELSE statement. Modify for your need.

It works very well though. There problably is a quicker/easier way, i.e. all in one SQL statement, instead of updating every single row in the database, but I couldnt find it.