Page 1 of 1

How do you change the order of fields within a table?

PostPosted: 07. November 2008 22:26
by Granpoh
Thanks for all help.

PostPosted: 07. November 2008 22:59
by Sharley
Granpoh wrote:How do you change the order of fields within a table?

1. Select the database to change in the left hand column.

2. Select the table to change in the left hand column.

3. Select Browse from the top menu.

4. From the top menu select Operations.

5. Under Alter table order by - from the drop boxes make your selections of choice.

6. Click on Go in the same box.

7. Select Browse again from the top menu to check the order of things - repeat until satisfied.

Message - Your SQL query has been executed successfully

Job done - Have a celebratory drink, cheers!. :D

PostPosted: 07. November 2008 23:24
by Granpoh
Sharley,

All that seems to do is permit me to take a field and choose if I want the data received into that field to be in either ascending or descending order. What I'm actually trying to do is alter the order of the fields/columns themselves. So if I have 3 fields/columns in the table, e.g. "One", "Two", "Three", instead of it being in that order, I'd want the column order to be "Two", "Three", "One". Any idea? Thanks a lot.

PostPosted: 07. November 2008 23:42
by Sharley
Granpoh wrote:All that seems to do is permit me to take a field and choose if I want the data received into that field to be in either ascending or descending order.
Incorrect.

From the first drop box select say "Two" leave the next box "Ascending" and select Go.
Now when you check the field order you will have the field ordered like "Two" "Three" "One".

Select "Three" click go will move "Three" to the top etc.

Experiment until you are happy.

The alternate is a bit messy and entails you export the database and manually edit, with a text editor, the .sql file to the field order you require.

Then import the edited .sql file back into the database.

This first method I posted should give you what you want but you will have to experiment with the order of the fields

PostPosted: 07. November 2008 23:53
by Granpoh
I played around with it quite a bit, and yet the order of the table is exactly the same as it was when I first started messing with it. Nothing ever changes. :(

PostPosted: 08. November 2008 01:43
by Sharley
My "incorrect" statement was wrong and you were right- just checked a database on my XAMPP installation.

So lets recap.

Database name: blah
Table names: blah1 blah2 blah3
Field names in table blah1: one two three
Field names in table blah2: four five six
Field names in table blah3: seven eight nine

Is this example roughly what your structure is?



To change the Field name order in table blah1 to two three one you will have to use an SQL query like so:

1. After selecting the table blah1 click on SQL in the top menu.

2. Delete any other query so you have an empty text box.

3. You need to locate the column type of the field name you wish to move from the list in the structure window, for example it might be field one varchar(255) and I will use that example in the SQL query below.

Type in the box: ALTER TABLE blah1 MODIFY COLUMN one varchar(255) AFTER three

4. Click on Go

The full syntax is and just add your own specific names like in the example above.
ALTER TABLE table MODIFY COLUMN columnnamebefore colnamebeforecoltype AFTER columnnametomoveafter

I just tested this on a database in my installation and it works a treat.

Hopefully, job done. :)

PostPosted: 08. November 2008 05:51
by Granpoh
Sharley wrote:3. You need to locate the column type of the field name you wish to move from the list in the structure window, for example it might be field one varchar(255) and I will use that example in the SQL query below.

Type in the box: ALTER TABLE blah1 MODIFY COLUMN one varchar(255) AFTER three [/b]

4. Click on Go

The full syntax is and just add your own specific names like in the example above.
ALTER TABLE table MODIFY COLUMN columnnamebefore colnamebeforecoltype AFTER columnnametomoveafter


Thanks for your help, but I'm not really following you on the above statement. I tried doing it as stated but it didn't work, so I assume I'm just not understanding it correctly.

PostPosted: 08. November 2008 06:04
by Sharley
You will get an error message if it is not working - paste the error messages here.

Also paste here exactly what you are typing in the SQL query text box.


ALTER TABLE blah1 MODIFY COLUMN one varchar(255) AFTER three [/b]
Sorry for my typo that should be minus the [/b]
ALTER TABLE blah1 MODIFY COLUMN one varchar(255) AFTER three

You will need to change the bold names to suit your own database names.

ALTER TABLE = is blah1 table
MODFIFY COLUMN = is field one
Column Type = is varchar(255)
Put field one AFTER field three


I will edit my other post to get rid of the errant forum tag.

PostPosted: 08. November 2008 06:30
by Granpoh
Sharley wrote:You will get an error message if it is not working - paste the error messages here.

Also paste here exactly what you are typing in the SQL query text box.


ALTER TABLE blah1 MODIFY COLUMN one varchar(255) AFTER three [/b]
Sorry for my typo that should be minus the [/b]
ALTER TABLE blah1 MODIFY COLUMN one varchar(255) AFTER three

You will need to change the bold names to suit your own database names.

ALTER TABLE = is blah1 table
MODFIFY COLUMN = is field one
Column Type = is varchar(255)
Put field one AFTER field three


I will edit my other post to get rid of the errant forum tag.


This is what I entered:
ALTER TABLE users MODIFY COLUMN Last Name varchar(20) AFTER First Name


This is the error message:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name varchar(20) AFTER First Name' at line 1

PostPosted: 08. November 2008 06:40
by Sharley
No spaces in the names, use an underscore instead and rename the fields and anything else in the databases if it has a space in the name - Windows is OK but anything Apache/MySQL is a no_no :)
Last_Name
First_Name

Or rename them to firstname and lastname respectively if preferred.
Spaces are not allowed in names.

To rename a field select the table (users) in the left column - select or tick the field (First Name) then click on the edit icon - looks a bit like a pencil and is the second icon in the row of Seven icons under the list of Fields in the Structure window - click Save when you are happy with the name change.

PostPosted: 08. November 2008 12:08
by Wiedmann
Spaces are not allowed in names.

That's not correct.
-->
Code: Select all
ALTER TABLE `users` MODIFY COLUMN `Last Name` varchar(20) AFTER `First Name`;

( But it's easier to work without them. It's also better to work only with lower case letters.)

PostPosted: 08. November 2008 23:47
by Granpoh
Thanks Sharley and Wiedmann for the help.

Wiedmann wrote:
Spaces are not allowed in names.

That's not correct.
-->
Code: Select all
ALTER TABLE `users` MODIFY COLUMN `Last Name` varchar(20) AFTER `First Name`;

( But it's easier to work without them. It's also better to work only with lower case letters.)


Wiedmann, the code you provided me did the trick. Thanks a lot.
To help me understand the workings of SQL a little more, I have a question for you: Why is it easier to work without spaces, and why is it better to work only with lower case letters? Also, are the single quotation marks necessary around the table name and column names for the code you provided me?


Thanks again

PostPosted: 09. November 2008 12:22
by Dave_L
If you use spaces in identifier names, they have to be quoted as above, so they won't be interpreted as field separators. It's simpler if you don't use spaces.

Mixing uppercase and lowercase characters leads to problems and confusion when you're in a case-sensitive environment. If you always use all lowercase, you never have to worry about that.

The backticks (`), not single quotes ('), around the identifier names are needed only if an identifier contains spaces or other characters used by MySQL, or if the identifier is a MySQL reserved word. But it's a good idea to use the backticks all the time, to avoid possible problems. This applies to database names, table names, column names, index names and aliases.

PostPosted: 09. November 2008 21:57
by Granpoh
Dave_L wrote:If you use spaces in identifier names, they have to be quoted as above, so they won't be interpreted as field separators. It's simpler if you don't use spaces.

Mixing uppercase and lowercase characters leads to problems and confusion when you're in a case-sensitive environment. If you always use all lowercase, you never have to worry about that.

The backticks (`), not single quotes ('), around the identifier names are needed only if an identifier contains spaces or other characters used by MySQL, or if the identifier is a MySQL reserved word. But it's a good idea to use the backticks all the time, to avoid possible problems. This applies to database names, table names, column names, index names and aliases.


Thanks Dave. That was very helpful.