SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,921
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Changing Field Order in Table

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in general, yes, it's possible
    Quote Originally Posted by da manual
    To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.
    whether phpmyadmin can do it, with its gui screens, i have no idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,921
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in general, yes, it's possible

    whether phpmyadmin can do it, with its gui screens, i have no idea
    So how do you do it with pure SQL?

    Is it an ALTER TABLE thingy?


    Debbie

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,921
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.
    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. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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
    Code:
    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. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,921
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when you look at the syntax in the manual, and you see a capitalized word in square brackets, like this --
    Code:
    [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 --
    Code:
    column_definition
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,921
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    when you look at the syntax in the manual, and you see a capitalized word in square brackets, like this --
    Code:
    [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 --
    Code:
    column_definition
    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
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •