Reordering of fields in phpMyAdmin

Hi

I have a large table of information that I want to import into another table with a different field order.

I can´t see anywhere in PHPMyAdmin to change the field order.

Does anyone know how I could do this, or maybe of a free database app for my pc which will allow me to import the records, change and insert the fields before exporting a mysql dump.

Any ideas very much appreciated.

 

You could try the following (not tested):

1) Export structure only.
2) Edit structure file and re-order fields.
3) Export the data with "Complete inserts" Option.
4) Import edited table structure.
5) Import data.

 

As long as the fields are the same name and type the order does not matter so long as you export and then import it. The proper information will be inserted into the correct table.

There is one additional important step. After you export the file open it in a text editor like notepad2 and use the replace all function to replace the old database name with the new database name.

If there is a way to re-order the fields using phpmyadmin I never found it.

First time doing this, I would suggest you work with copies of the DB on both ends to cover your tushey.

 

I´ll give it a try

Thanks

 

Hi

Surely when you are using the import its not the names but the order of the fields that matters as its using the INSERT command e.g

INSERT INTO `table_name` VALUES (1,'Bob wilson','etc.');

 

Hi

Think I´ve found a solution

I´ve downloaded Navicat 8 for MySQL and that allows you to connect remotely with your host and change field order.

 

Why you need to re-order the fields? You can SELECT them in any order you want...

 

Hi

Depends if you are doing the import/ export route, or using sql queries.

For the Import/Export you need to have the records in the right order. e.g.

INSERT INTO `table_name` VALUES (1,'Bob wilson','etc.');

If you are doing a SQL query you can do as you say. The reason I wanted to use a app which allowed me to change the order of the fields is that my SQL is very rusty and with Navicat it allowed me to make all the changes to the table and then just do a simple

INSERT INTO `newtable` SELECT * FROM `original table`; and it was all there. ;-)

 

I think you should be able to do the following:

CREATE TABLE *TABLE NAME* AS (

SELECT column 1, column 5, column 2, column4, column3 from table

)

?

 

reordering fields using sql:

ALTER TABLE table_name MODIFY col_name original_column_definition [FIRST|AFTER col_name]

 

I like PHPMyAdmin for quick changes but for reordering fields use MySQL Workbench. It's free and is substantially more capable. MySQL Workbench is to PHPMyAdmin what Eclipse IDE is to Notepad (ok, that's an exaggeration, but you get my drift) The best benefit I've found is that you can completely diagram your database and then export the SQL to actually create it.