Resorting table structure

netfrugal

This is a question that may or may not be attainable via phpmyadmin or some other database management tool.

Is there a way to sort the fields in a table differently, after creating them?

Example:

Table Customers Structure -

customer_id
firstname
lastname
email
phone

I want to reorder 'email' to be below 'phone'.

Anyone know how this is done?

thank you!

bpat1434

I don't think it's really possible without using bunch of alter table statements to:

Create a new column where you want (after "phone")
[indent]ALTER TABLE sometable
ADD column_name VARCHAR(255) NOT NULL
AFTER othercolumn[/indent]
Copy data from old column to new column
[indent]UPDATE sometable SET column_name = othercolumn[/indent]
Now just drop the previous column
[indent]ALTER TABLE sometable
DROP othercolumn[/indent]
And finally rename the column to be what you want:
[indent]ALTER TABLE sometable CHANGE column_name new_name VARCHAR(255) NOT NULL[/indent]

Hope that helps. I successfully did each of these steps in phpMyAdmin, but I had to do it one by one (add a new column, transfer the data (by executing the SQL), drop the original column, rename the new column). So it can be done, it just might take a bit.

Shrike

It would probably be most easily done with a temp table - select the lot, rename or drop the old table, create new table with the correct order and insert. However the physical ordering of the columns should never be an issue - SQL statements can order the columns however you like.