Dynamically reordering fields in mysql

Question

In my project, I made a lot of changes to the database, and at some point I need to reassign the order for some table in my database. so what i want. I have the 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 ... for example

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 my order field to be updated from 0 to plus one, and more ... How to do this, I have no idea ... I'm trying, but I'm also not close to a solution. How to do it? Please help me.

Answer 1

You can use the following query:

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

EDIT:

In CodeIgniter, you can do the following:

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