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

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:

  1. goto phpmyadmin
  2. select the database: my_database
  3. click on sql tab on the top menu
  4. 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!

  • Jon

    The instructions are very clear, and… they work!

    Thank you for posting this.

    • http://doctornabeel.com Dr. Nabeel

      thanks for appreciating Jon!