How to change the sequence of columns of a table in mysql – phpmyadmin

Nabtron

14 years ago

This tutorial explains how you can change the sequence of the columns in a mysql database’s table (in phpmyadmin or by using php code)

I wrote this article while doing this practically right now as i wasn’t able to find any proper information online about it, and among those that i found, NONE was working. So anyone out there in similar situation, this one is for you!

Forexample, if i have:

a database named my_database
a table named my_table
having columns:
- my_column_1
- my_column_2
- my_column_3

And now what i want to do is:

alter the table’s column structure
without deleting them or losing any data in them
want to move my_column_1 to the end of the table columns list making the sequence to be:
- my_column_2
- my_column_3
- my_column_1

To do this, simply:

goto phpmyadmin
select the database: my_database
click on sql tab on the top menu
run this query: ALTER TABLE my_table MODIFY my_column_1 int(11) AFTER my_column_3

The query is explained like this:

The main query to use is:

ALTER TABLE table_name MODIFY column_to_move column_type AFTER column_to_reference

Please note that the part column type is necessary.

You can run the direct query without going into the database first (and by using php code) by using full name of the table which includes database name like:

ALTER TABLE `database_name`.table_name MODIFY column_to_move column_type  AFTER column_to_reference

Hope it helps!

You might also like