Menu
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.
343
An "ALTER TABLE ORDER BY" statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: - only accept *one* column, as in "ALTER TABLE t ORDER BY col;" - is used to reorder physically the rows in a table, for optimizations.
The syntax to modify a column in a table in MySQL (using the ALTER TABLE statement) is: ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ]; table_name. The name of the table to modify.
column order does not matter. This is purely a convenience feature. just to allow you to restructure your database table the way you like after it has been created.
To add a column in a table in MySQL, we can use ALTER command with add column command. First, let us create a table with columns Id and Name. After that, we will add column name Age and Address with the help of ALTER command. The following is the query to create a table.
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");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
