php - mySQL update sort - order from table with unlimited childs after delete one or more -

i have table this:

id | parent_id | order   1 |     0     |   1     2 |     1     |   1  3 |     1     |   2  4 |     1     |   3  5 |     0     |   2  6 |     0     |   3  7 |     5     |   1  8 |     5     |   2 

it takes unlimited categories , childs in same table parent_id. have form checkboxes delete 1 or more , want re-ordering after delete 1 or more rows group parent_id.

i have write code:

mysql_query("set @rownumber = 0;"); $sql_previous_order = "update `cms` set `order` = (@rownumber:=@rownumber+1) order parent_id, `order` asc"; 

but not turn rownumber 0 (0) after change parent_id.

you can introduce additional variable - p_id. compare parent_id , reset rownumber 0 when parent_id changed.

 set @rownumber = 0;  set @p_id = 0;  select id,         case @p_id              when parent_id @rownumber:=@rownumber+1              else @rownumber:=0 end new_order,        @p_id:=parent_id  p_id cms order parent_id asc ;