Maintain / update record order in mysql

Question

I have a table of records in mySql. I need to maintain order for them as indicated by the user. So I added the column "position".

What would be the SQL statement to update all records when moving a particular record? I have something like:

UPDATE items SET position = '2' WHERE id ='4';
UPDATE items SET position = position+1 WHERE position >= '2' AND id != '4';

But the more there will be less than if the record went down. What trick? Thank!

Answer 1

, . , , , BL UI. , " ". , , "" ( ), .

, , .

Answer 2

?

UPDATE items 
SET position = CASE position 
  WHEN $oldpos THEN $newpos 
  ELSE position + SIGN($oldpos-$newpos)
 END
WHERE position BETWEEN LEAST( $newpos, $oldpos ) 
                AND GREATEST( $newpos, $oldpos );

, , .

Answer 3

: row_index 10 (: 0, 10, 20, ecc.). , , . 2- 4- (, row_index = 30), row_index 35; ( ) 25. :

UPDATE your_table SET row_index = 35 WHERE your_id = 1234

, row_indexes . , :

SET @pos:=-10;
UPDATE your_table SET row_index = @pos:=@pos+10 WHERE ...

( , where), row_index (0, 10, 20, 30, ecc.). , , , . , .

Answer 4

, , (, 10000) . . 11000 9000 10000, 9500 .

, . - , . , , imho.

Answer 5

, , , , , .

Answer 6

! , . , - . . .

Answer 7

, - ?

$item_id = $_GET['item_id']; 
$position = $_GET['new_position']; 
$old_position = $_GET['old_position'];

if($new_position < $old_position) {
    SQL: UPDATE items SET position = position+1 WHERE position >= $new_position AND position < $old_position  
} else {  
SQL: UPDATE items SET position = position-1 WHERE position <= $new_position AND position > $old_position
}  

SQL: UPDATE items SET position = $new_position WHERE item_id = $item_id