Changing Field Order in Table

Databases
1

Is it possible to change the order of Fields in a Table in phpMyAdmin without having to delete the Field and then add it back?

Is it possible to change the order of Fields in a Table in general without having to delete the Field and then add it back?

Debbie

2

in general, yes, it’s possible

whether phpmyadmin can do it, with its gui screens, i have no idea

3

So how do you do it with pure SQL?

Is it an ALTER TABLE thingy?

Debbie

4

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Googling for ‘mysql change column order’ gave me the manual on page 1.

5

I looked at that, but am unsure which one to use and how to use whichever one would apply.

(I was always told you couldn’t do what I want to do without dropping a Field and then adding it back, but you seem to say that isn’t so?!)

Debbie

6

I never changed the order of columns in a table, but reading the manual, I see (hidden quite well in a huge amount of text):

You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

So why don’t you try


ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

and see what happens.

Be aware that:

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.

7

guido2004,

Be aware that:
When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.

What “Attributes” would those be? :-/

Here is my table layout…

id
email
activation_code
salt
hash
temp_password
temp_reset_on
first_name

I would like to move first_name BEFORE email

Can we make a practice example so I can get a better idea of what you mean?

I am bolding the parts where I am confused…

ALTER TABLE member MODIFY [COLUMN] first_name column_definition AFTER id

Thanks,

Debbie

8

when you look at the syntax in the manual, and you see a capitalized word in square brackets, like this –

[COLUMN]

that means it’s optional

when you look at the syntax in the manual, and you see a phrase in lower case italics, like this –

[I]column_definition[/I]

that means you have to supply something there

read through the manual for ALTER TABLE (what you’re trying to do) and you will see this –

  • column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 12.1.5, “CREATE TABLE Syntax”.

i’ll leave it to you to look up the CREATE TABLE syntax and discover what column_definition actually entails

come on, debbie, you can do it

you’ve got us reading the manual for you, but you really should be doing that, and trying the statements, yourself

9

Um, I did read the manual, but I wasn’t following what it was saying as stated above.

FWIW, I have learned to read the PHP Manual, but the MySQL Manual is incoherent, and even though I try, I usually don’t get very far, like now!

Debbie