How do I change the order of my table fields without deleting the field and re-inserting it, using PHP myAdmin?
Add a comment |
9 Answers
ALTER TABLE `table_name` MODIFY `column_you_want_to_move` DATATYPE AFTER `column` DATATYPE is something like DATETIME or VARCHAR(20) ..etc
1 Comment
cssyphus
For me, it must be: etc MODIFY COLUMN
column_you_want_etcIf you have phpMyAdmin 4.0.0+, you can use the phpMyAdmin Feature under Structure:
1 Comment
Yevgeniy Afanasyev
It is only work since version 4.0, phpMyAdmin. I checked phpMyAdmin Version 3.5.1 and there was no such function.
Something like this will help
ALTER TABLE Person MODIFY COLUMN last_name VARCHAR(50) AFTER first_name; This will move last_name right after first_name in order.
2 Comments
Max Williams
What if you want to make the column the first column? Ie, so it's not AFTER anything. I tried
"... BEFORE current_first_col" but it doesn't recognise BEFORE.Max Williams
Aha, just answered my own comment - the syntax is
ALTER TABLE Person MODIFY COLUMN last_name VARCHAR(50) FIRST; to make it the first column.http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html
From the Aforementioned Source:
If you decide to change the order of table columns anyway, you can do so as follows:
Create a new table with the columns in the new order.
Execute this statement:
mysql>
INSERT INTO new_table -> SELECT columns-in-new-order FROM old_table;Drop or rename old_table.
Rename the new table to the original name:
mysql>
ALTER TABLE new_table RENAME old_table;
Comments
Since version 4.0, phpMyAdmin has a "Move columns" dialog in Structure, that permits you to graphically move columns in the structure.
2 Comments
machineaddict
It isn't actually moving the columns. Go to other browser and see that the columns order are the same after teh change.
Marc Delisle
Well, it's working for me, actually moving the columns.
alter table table_name modify column col_name type after col_name 
Comments
It's simple. Just go to PHPmyadmin, click on your database, then click table. Then click on structure. Below the table look for the button, "Move columns". Click and order the columns the way you want.
Comments
Another alternative:
CREATE new_table SELECT columns-in-new-order FROM old_table; 
Comments
if you have MySQL Workbench you can easily reorder columns using mouse, graphically.
Just connect to your database, select your table and after right click, alter table and then drag columns to reorder them.
