Changing field order dynamically in mysql

Question

In my project I made many changes in database and at some time I need to re-assign order to some table in my database. so, I want is. I have following table.

id     name     address     order
1      vijay    mumbai      2
3      ram      delhi       4
4      ravi     pune        5
5      rutul    surat       8
9      vipul    agra        11

And I want to update it from mysql query...like

id     name     address     order
1      vijay    mumbai      0
3      ram      delhi       1
4      ravi     pune        2
5      rutul    surat       3
9      vipul    agra        4

So I want is my order field to update from 0 to plus one and on and on... How to do that I have no idea...I try but I am also not near to the solution. How to do that? Please help me.

Answer 1

You can use following query:

SET @orderid = -1;    
update yourTableName set `order` = (@orderid:=@orderid+1)
order by id asc

EDIT:

In codeIgniter you can do like following:

$this->db->query("SET @orderid = -1");
$this->db->query("update table_name set `order` = (@orderid:=@orderid+1) order by id asc");