LEON  KESSLER / BLOG 

The logic behind inserting a record is to increment every record in front of it (that is, to increment the field that you order the table by), so that a space is given.  Swapping records is a bit more tricky, even though you are simply exchanging data between two rows, at some point both pieces of data will be the same (between updating the first and second record).  So doing this on a primary key field is not an option.  I can also imagine it’s best to avoid changing data whatsoever on the primary key field, as it’s use is for reference and not really for order, problems could also later occur when tables are linked together.

So an extra field called ‘position’ was created, and the rest was done like this:

Inserting:
First we need to know the position of the record we are inserting before. I’ll assume this has been done and the position has been placed in a variable called $position. Then the query to increment each position ahead would go like this:

$query = "UPDATE tablename SET position = CASE 
WHEN position >= $position THEN position+1 
ELSE position END";
$result = mysql_query($query);

Now all the records ahead have increased position by one, all done in one query. I was originally using a loop to go through each record one by one. Using a CASE and WHEN clause is obviously much better. One thing to make sure is that the ELSE clause is put in (or mySQL will update all records).
Now to insert the new record we can use the original $position variable (as this will now be free).

Swapping:
I found this a bit more more difficult than inserting, but managed it okay like this…

Again, I’m assuming the position of the field you wish to swap is in a variable called $position. In case of moving up or moving the record down, I do this through $_GET['action'].

if($_GET['action'] == 'moveup'){
            $operator = '>=';
            $direction = 'ASC';
}elseif($_GET['action'] == 'movedown'){
            $operator = '<=';
            $direction = 'DESC';
}

These variables are created for the next query, the next step is to perform this query to get the two records to switch and put them in an array

$query = "SELECT position FROM tablename 
WHERE position $operator $position 
ORDER BY position $direction 
LIMIT 2";
$result = mysql_query($query);
	$linksToSwictch = array();  // Create the array
	while($row = mysql_fetch_array($result)){
		$linksToSwitch[] = $row[0];
	}

Now we have the array we can use an IF inside a mySQL query to switch the positions…

            if (count($linksToSwitch) == 2) 
// This conditional makes sure there are two links to switch 
// (so trying to move the highest record up or the lowest record down 
// nothing will be executed)
            {
                $newPos = $linksToSwitch[0];
                $oldPos = $linksToSwitch[1];
                $query = "UPDATE tablename 
                SET  `position` = 
                IF( position = $oldPos, $newPos, $oldPos ) 
                WHERE position IN ( $oldPos, $newPos )";
                $result = mysql_query($query);
            }