Can you rearrange table columns order?

Using phpmyadmin & MySQL

[jaymullah]

I am transfering my data from my current database to a new one. Using phpmyadmin. So I am trying to get my tables to match the ones in the new database, that way I can export my data and then easly import it in the new database.

I know how to add and delete columns to my current tables, to make them match the tables on my new database. However, I dont know how to re-arrange the order of the columns in the table.

For example, my current table, holding all the data I want to transfer, has columns in this order:

CREATE TABLE `phpbb_topics` (
 `topic_id` mediumint(8) unsigned NOT NULL auto_increment,
 `forum_id` smallint(8) unsigned NOT NULL default '0',
 `topic_title` varchar(60) NOT NULL default '',
 `topic_poster` mediumint(8) NOT NULL default '0',
 `topic_time` int(11) NOT NULL default '0',
 `topic_views` mediumint(8) unsigned NOT NULL default '0',
 `topic_replies` mediumint(8) unsigned NOT NULL default '0',
 `topic_status` tinyint(3) NOT NULL default '0',
 `topic_vote` tinyint(1) NOT NULL default '0',
 `topic_type` tinyint(3) NOT NULL default '0',
 `topic_last_post_id` mediumint(8) NOT NULL default '0',
`topic_first_post_id` mediumint(8) unsigned NOT NULL default '0', 
 `answer_status` tinyint(1) NOT NULL default '0',
 `topic_moved_id` mediumint(8) unsigned NOT NULL default '0',
 `topic_attachment` tinyint(1) NOT NULL default '0',
 `topic_icon` tinyint(2) NOT NULL default '0',
 `rating_rank_id` smallint(5) NOT NULL default '0',
 `title_compl_infos` varchar(255) default NULL,
 `topic_priority` smallint(6) NOT NULL default '0',
 PRIMARY KEY (`topic_id`),
 KEY `forum_id` (`forum_id`),
 KEY `topic_moved_id` (`topic_moved_id`),
 KEY `topic_status` (`topic_status`),
 KEY `topic_type` (`topic_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1217 ;

The columns in question are `topic_last_post_id` and `topic_first_post_id` . In the NEW database, `topic_first_post_id` comes before `topic_last_post_id` . So if I export the data, then import it to the new database the data will not be in the correct order. Here is the table structure in the new database:

CREATE TABLE `phpbb_topics` (
 `topic_id` mediumint(8) unsigned NOT NULL auto_increment,
 `forum_id` smallint(8) unsigned NOT NULL default '0',
 `topic_title` varchar(120) NOT NULL default '',
 `topic_poster` mediumint(8) NOT NULL default '0',
 `topic_time` int(11) NOT NULL default '0',
 `topic_views` mediumint(8) unsigned NOT NULL default '0',
 `topic_replies` mediumint(8) unsigned NOT NULL default '0',
 `topic_status` tinyint(3) NOT NULL default '0',
 `topic_vote` tinyint(1) NOT NULL default '0',
 `topic_type` tinyint(3) NOT NULL default '0',
 `topic_first_post_id` mediumint(8) unsigned NOT NULL default '0',
 `topic_last_post_id` mediumint(8) unsigned NOT NULL default '0',
 `answer_status` tinyint(1) unsigned NOT NULL default '0',
 `topic_moved_id` mediumint(8) unsigned NOT NULL default '0',
 `topic_attachment` tinyint(1) NOT NULL default '0',
 `topic_icon` tinyint(2) unsigned NOT NULL default '0',
 `rating_rank_id` smallint(5) unsigned NOT NULL default '0',
 `title_compl_infos` varchar(255) default NULL,
 `topic_priority` smallint(6) NOT NULL default '0',
 PRIMARY KEY (`topic_id`),
 KEY `forum_id` (`forum_id`),
 KEY `topic_moved_id` (`topic_moved_id`),
 KEY `topic_status` (`topic_status`),
 KEY `topic_type` (`topic_type`),
 KEY `topics_ratingrankid` (`rating_rank_id`),
 FULLTEXT KEY `topic_title` (`topic_title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1221 ;

How can I rearrange the table's column order on my current database, without losing all the data?

[coopster]

First, make a backup copy of your data. ALWAYS make a backup copy prior to table alterations and updates such as this.

Next, all you need to do is some ALTER TABLE [dev.mysql.com] work.

[jaymullah]

Ok I have read over that page and I am guessing this is close to what I'm suppose to do, only I can not figure out the right syntax to use in phpmyadmin to do it. Sorry , I'm not that great with MySQL yet, I just know the basics to get me by. I am mainly a designer.

On the reference page it says:

MODIFY [COLUMN] column_definition [FIRST ¦ AFTER col_name]

So I am trying this is in phpmyadmin:

ALTER TABLE phpbb_topics (
MODIFY `topic_last_post_id` CHANGE AFTER `topic_first_post_id`
); 

I have tried many variations and just can not get it. Can anyone help me with the syntax to use to do this operation in phpmyadmin?

I have got most of my database already transfered, I am just stuck on this one table.

Thanks for providing my with that link!

[jaymullah]

I've been reading more of the MySQL reference guide, and looks like this explains what I'm trying to do better:

[dev.mysql.com...]

So I tried:

ALTER TABLE nuke_bbtopics MODIFY COLUMN topic_last_post_id mediumint AFTER topic_first_post_id

and it worked like a charm. Thanks again for pointing me in the right direction!