mysql - Change Database records sort order to move record position in between other records with PHP

Solution:

i suppose this would work, but i think i'd add a column for "sort order", not messing with IDs :o and assuming you're using PDO as $this->db - i think the trick is to give the current owner of newid, a temporary id, then move the oldid to the newid, then move the tempid to oldid

public function moveTaskIdToListId($oldid,$newid){

$this->db->beginTransaction();
$tmpid=$this->db->query('SELECT max(`id`) FROM `table`');
++$tmpid;
$this->db->query('UPDATE `table` SET `id` = '.$this->db->quote($tmpid).' WHERE `id` = '.$this->db->quote($newid));
$this->db->query('UPDATE `table` SET `id` = '.$this->db->quote($newid).' WHERE `id` = '.$this->db->quote($oldid));
$this->db->query('UPDATE `table` SET `id` = '.$this->db->quote($oldid).' WHERE `id` = '.$this->db->quote($tmpid));
$this->db->commit();
//todo: error handling and handle case when newid does not exist yet..(if that ever happens)
}
Copy code

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.