php - Gap fill in priority column + keep order -

is there algorithm "fill" gaps in priority (or other) column of table? e.g.

example

i have table structure looks this:

id | text | subcategory | priority 

and filled example this

1 | books    | null | 1 2 | dvds     | null | 2 5 | action   | 2    | 1 8 | romantic | 2    | 2 9 | fantasy  | 1    | 1 4 | sci-fi   | 1    | 2 6 | comics   | 1    | 3 

in design there option change subcategory, if happens priority set "new" priority , value highest in subcategory in.
e.g change "action (id:5)" subcategory "books (id:1)", priority 4, ok, "romantic (id:8)" row has priority 2, , 1 , in subcategory dvds (id:2).

1 | books    | null | 1 2 | dvds     | null | 2 8 | romantic | 2    | 2 9 | fantasy  | 1    | 1 4 | sci-fi   | 1    | 2 6 | comics   | 1    | 3 5 | action   | 1    | 4 

-> change fantasy (id:9) subcategory dvds (id:2) , priority going 3.

1 | books    | null | 1 2 | dvds     | null | 2 8 | romantic | 2    | 2 9 | fantasy  | 2    | 3 4 | sci-fi   | 1    | 2 6 | comics   | 1    | 3 5 | action   | 1    | 4 

it alrgiht, need function re-order dont have manualy change values of priority column. priority column starts 1.

gaps start in row id 8, 4. furthermore change comics category , change back, going messy there right order wont nice expect (for administration purposes).

any ideas? pseudocode or logic nice.

edit - solution: pseudocode + logic

since know count of rows in category , can make select going ordered priority can assign right number each "new" priority.

e.g:
ordered select returns priorities follows: 1, 4, 5, 9, 10
count(select) = 5
"new" priorities must follows : 1, 2, 3, 4, 5. assign in foreach loop new key value.

since in codeigniter:
$this->category_model->getpriorities("2") gets priorities in order (asc) of 1 subcategory in case 2.

public function prioritize(){     $p = $this->category_model->getpriorities("2");         ($i = 1; $i < count($p)+1; $i++) {              echo "new[".$i."]->id[".$p[$i-1]->id."]->old_value[".$p[$i-1]->priority."]<br>";             } } 

output:

new[1]->id[9]->old_value[1] new[2]->id[13]->old_value[3] new[3]->id[14]->old_value[5] new[4]->id[15]->old_value[8] new[5]->id[11]->old_value[10] 

update table tb1, table tb2 set tb2.priority = tb2.priority - 1 tb2.priority > tb1.priority , tb1.text = 'action' , tb2.subcategory = tb1.subcategory;

this should solve gap, every item higher id, should drop 1

i.e. have in subcategory:

| priority | |   1      | |   2      | |   3      | |   4      | 

if move item priority=2

3 , 4 move 2 , 3

p.s.: solution gap, since move issue solved in other answer. should done before movement, because subcategory change after it, , not met clause (the items old subcategory)

---

about reordering random gap:

let's have priorities:

5, 10, 11, 12, 18, 20

which want make as:

1, 2, 3, 4, 5, 6

so maximum number here 20, needs marked 6

when it's done, maximum number 18, should 5.

so everytime use update ... set ... priority = max(priority) maximum priority.

here's simple test:

<?php ($i = 6; $i>=1; $i--) {     echo "update table set priority = $i priority = max(priority) , subcategory = x;" . "<br/>"; } ?> 

which produces:

update table set priority = 6 priority = max(priority) , subcategory = x; // 20 becomes 6 update table set priority = 5 priority = max(priority) , subcategory = x; // 18 becomes 5 update table set priority = 4 priority = max(priority) , subcategory = x; // 12 becomes 4 update table set priority = 3 priority = max(priority) , subcategory = x; // 11 becomes 3 update table set priority = 2 priority = max(priority) , subcategory = x; // 10 becomes 2 update table set priority = 1 priority = max(priority) , subcategory = x; // 5 becomes 1 

so need make select selecting subcategory, put instead of "x", need count rows while loop:

for ($i = $count_rows; $>=1; $i++) { ... 

ofcourse, need put statement in right query function, instead of echoing it, test purpose.