如何在php和mysql中更改db行位置并维护顺序

Question

i have a php project i've been working on using Doctrine 1.2.4, mysql and Zend (not related to the issue). there has been a new requirement where from the admin panel, one can change the appearance of their team members on their team page by position.

So i added a position int column to the table. Now the main problem is to change the position and maintain the order. I've been scratching my head and found a workaround using arrays in php but it's buggy. here is my way: select position as key and id as value into an array, and work on the reordering on that array and reupdate the table based on the rearranged array. here is my code:

//here i use string as my id to be able to view the changes .  
//you can swap to this:
  $anarray = array("23", "12", "4", "6", "2");
//$anarray = array("car", "dog", "cow", "cup", "plane");

var_dump($anarray);

function preserveSort($oldposition, $newposition, $arraytosort) {
  // this assumes that there is no zero in position
  $oldposition--;$newposition--;
  $indice = $newposition - $oldposition;
  $tmpNewPosistionData = $arraytosort[$oldposition];
  if ($indice > 0) {

      for ($i = $oldposition; $i < $newposition; ++$i) {
          echo $i . "<br/>";
          $arraytosort[$i] = $arraytosort[$i + 1];
      }
  } else {
      for($i=$oldposition;$i >$newposition; $i--){
          echo $i."<br/>";
          $arraytosort[$i] = $arraytosort[$i-1];        
      }
  }
  $arraytosort[$newposition] = $tmpNewPosistionData;
  var_dump($arraytosort);
}

echo "<br/>";
echo "changing position 1 to 4 <br/>";
preserveSort(1, 4, $anarray);

I thought it could have worked perfectly but after some tries the positions are getting mixed up. I'm wondering if there is out there someone who have solved this problem already. if Yes i little help would much appreciated

thanks for reading this

Answer 1

function move_element($input, $from, $to) { // I suggest $input as first paramter, to match the PHP array_* API
    // TODO: make sure $from and $to are within $input bounds
    // Assuming numeric and sequential (i.e. no gaps) keys
    if ($from == $to) {
        return $input;
    } else if ($from < $to) {
        return array_merge(
            array_slice($input, 0, $from),
            array_slice($input, $from +1, $to - $from),
            array($input[$from]),
            array_slice($input, $to +1, count($input) - $to)
        );
    } else if ($from > $to) {
        return array_merge(
            array_slice($input, 0, $to),
            array($input[$from]),
            array_slice($input, $to, $from - $to),
            array_slice($input, $from +1, count($input) - $from)
        );
    }
}