[SOLVED] Move Column order with phpmyadmin

[phppaper]

Hello,

 

I have a table with 3 columns:

 

1. name

2. age

3. gender

 

I want to reorder the age and put it in front of name and become:

 

1. age

2. name

3. gender

 

is there any way to do it with phpmyadmin?? or any other way to do it??

 

I have many records in there so, I dont want to delete and do a new table, please help, thanks!

[mysqldba]

Why would you want to do that? If you need the columns returned in a different order for SELECT statements, just name the fields in the right order - relying on field order in the table is just setting yourself up for a future FAIL.

 

You can reorganise the table without deleting any data, but there's a very good chance that the MySQL server will lock the tables while it's sorting things out - just to warn you.

 

With no schema posted, I'll have to demonstrate with an example table:

 

mysql> CREATE TABLE bob (a INT, b VARCHAR(3), c DATETIME);
Query OK, 0 rows affected (0.04 sec)

mysql> DESCRIBE bob;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | int(11)    | YES  |     | NULL    |       | 
| b     | varchar(3) | YES  |     | NULL    |       | 
| c     | datetime   | YES  |     | NULL    |       | 
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE bob MODIFY b VARCHAR(3) AFTER c;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE bob;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | int(11)    | YES  |     | NULL    |       | 
| c     | datetime   | YES  |     | NULL    |       | 
| b     | varchar(3) | YES  |     | NULL    |       | 
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

 

You need to use ALTER TABLE ... MODIFY to do the edit. You're effectively redefining the column, so make sure your new definition is the same as the current one (SHOW CREATE TABLE is useful here)

[phppaper]

thanks!!

 

looks like the "ALTER TABLE bob MODIFY b VARCHAR(3) AFTER c;" could do the job,

 

but before I go preform this operation, I wonder if the data of the 2 column will be there afterwords??

[mysqldba]

Provided you don't have any TIMESTAMP fields, the data should be unchanged (provided the column type definition remains the same)

[phppaper]

Got it + thanks!!