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

i have project management php/mysql/javascript app has project task lists have task records.

a tak list name/heading nothing more task record database column indicated used heading/list name , task records can organized under separate task list based on there sort order database column

so based on demo below consists of 17 task records , 3 task lists records

id  | task name          | sort order 01  | - task list 1      | 1 02  | --- task item 1    | 2 03  | --- task item 2    | 3 04  | --- task item 3    | 4 05  | --- task item 4    | 5 06  | --- task item 5    | 6 07  | - task list 2      | 7 08  | --- task item 6    | 8 09  | --- task item 7    | 9 10  | --- task item 8    | 10 11  | --- task item 9    | 11 12  | --- task item 10   | 12 13  | - task list 3      | 13 14  | --- task item 11   | 14 15  | --- task item 12   | 15 16  | --- task item 13   | 16 17  | --- task item 14   | 17 18  | --- task item 15   | 18 

so consider scenario want update task item 13 id = 16 , sort order 16 , update sort order "moves" task record task list 1

i have simple use php function complete type of job of moving task record new sort order shows under new task list this:

/**  * move task record new task list id  * $taskid int task id number  * $tasklistid int task list record id number  */ $task->movetaskidtolistid($taskid, $tasklistid); 

i appreciate please not sure how exactly?

after thought here think might involved...

possible steps complete moving task records new lists

1. to move task item 13 list 3 list 1. query db , sort order value next list after 1 moving to. sort order list 2 7.
2. now subtract -1 list 2's sort order , gives number task item 13's sort order must changed in order move list 1
3. now task item 13 , task list 2 record both have sort order of 7.
4. so starting @ task list 2 record , records after it, need increment there sort order numbers +1

any suggestions on how this, doe steps above sound right this? again realize not best structure trying make work on existing system

i suppose work, think i'd add column "sort order", not messing ids :o , assuming you're using pdo $this->db - think trick give current owner of newid, temporary id, move oldid newid, move tempid oldid

public function movetaskidtolistid($oldid,$newid){  $this->db->begintransaction(); $tmpid=$this->db->query('select max(`id`) `table`'); ++$tmpid; $this->db->query('update `table` set `id` = '.$this->db->quote($tmpid).' `id` = '.$this->db->quote($newid)); $this->db->query('update `table` set `id` = '.$this->db->quote($newid).' `id` = '.$this->db->quote($oldid)); $this->db->query('update `table` set `id` = '.$this->db->quote($oldid).' `id` = '.$this->db->quote($tmpid)); $this->db->commit(); //todo: error handling , handle case when newid not exist yet..(if ever happens) }