MySQL structure help

[pamma83]

Hello

I wanted to know if there was a way to move the fields in a table up and down in the order that they appear in the structure view of the table. Or if there is a way to copy data from a old table into a new table, but the new table has a different structure than the old one, and some new fields as well.

Thanks

[mitchind]

You're not giving us much to work with here. The simple answer is yes.

With more details we can expand that answer. I'm assuming you're looking at building a query to append data from one table to another. Do all the matching fields have matching field names?

[pamma83]

Hello

This is the structure for the old table:

-- Table structure for table `platinum_users`
--

CREATE TABLE `platinum_users` (
`id` smallint(7) unsigned NOT NULL auto_increment,
`username` varchar(16) NOT NULL default '',
`password` varchar(16) NOT NULL default '',
`hint` varchar(100) NOT NULL default '',
`realname` varchar(48) NOT NULL default '',
`description` text NOT NULL,
`age` tinyint(2) unsigned NOT NULL default '0',
`user_type` tinyint(3) unsigned NOT NULL default '0',
`state` varchar(32) NOT NULL default '',
`country` varchar(32) NOT NULL default 'United_States.gif',
`email` varchar(48) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`quote` varchar(255) NOT NULL default '',
`image` enum('here','there') NOT NULL default 'there',
`image_url` varchar(144) NOT NULL default '',
`image_ext` varchar(4) NOT NULL default '',
`image_status` enum('enabled','disabled','queued','approved') NOT NULL default 'enabled',
`total_ratings` smallint(5) unsigned NOT NULL default '0',
`total_points` mediumint(9) unsigned NOT NULL default '0',
`average_rating` decimal(6,4) NOT NULL default '0.0000',
`total_comments` int(10) unsigned NOT NULL default '0',
`subscribed` enum('yes','no') NOT NULL default 'yes',
`md5key` varchar(32) NOT NULL default '',
`signup` varchar(14) NOT NULL default '',
`signup_ip` varchar(16) NOT NULL default '',
`signup_port` varchar(16) NOT NULL default '',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`is_approved` enum('0','1') NOT NULL default '1',
`total_files` smallint(6) NOT NULL default '0',
`last_logged` datetime NOT NULL default '0000-00-00 00:00:00',
`birthdate` varchar(8) NOT NULL default '',
`Zip_Code` varchar(9) default NULL,
`Maritial_Status` varchar(40) default NULL,
`Ethnicity` varchar(30) default NULL,
`Religion` varchar(30) default NULL,
`Do_You_Smoke` varchar(30) default NULL,
`Do_You_Drink` varchar(30) default NULL,
`AIM_screen_name` varchar(30) default NULL,
`Yahoo_ID` varchar(30) default NULL,
`MSN_Messenger` varchar(30) default NULL,
`Height` text,
PRIMARY KEY (`id`),
UNIQUE KEY `md5key` (`md5key`),
KEY `sex` (`user_type`),
KEY `timestamp` (`timestamp`),
KEY `signup` (`signup`),
KEY `username` (`username`),
KEY `email` (`email`),
KEY `subscribed` (`subscribed`)
)



This is the structure for the new table:

-- Table structure for table `platinum_users`
--

CREATE TABLE `platinum_users` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`username` varchar(16) NOT NULL default '',
`password` varchar(16) NOT NULL default '',
`hint` varchar(100) NOT NULL default '',
`realname` varchar(48) NOT NULL default '',
`description` text NOT NULL,
`age` tinyint(2) unsigned NOT NULL default '0',
`user_type` tinyint(3) unsigned NOT NULL default '0',
`state` varchar(32) NOT NULL default '',
`zip` varchar(20) NOT NULL default '',
`country` varchar(32) NOT NULL default 'United_States.gif',
`email` varchar(48) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`quote` varchar(255) NOT NULL default '',
`image` enum('here','there') NOT NULL default 'there',
`image_url` varchar(144) NOT NULL default '',
`image_ext` varchar(4) NOT NULL default '',
`image_status` enum('enabled','disabled','queued','approved') NOT NULL default 'enabled',
`total_ratings` smallint(5) unsigned NOT NULL default '0',
`total_points` mediumint(9) unsigned NOT NULL default '0',
`average_rating` decimal(6,4) NOT NULL default '0.0000',
`total_comments` int(10) unsigned NOT NULL default '0',
`subscribed` enum('yes','no') NOT NULL default 'yes',
`md5key` varchar(32) NOT NULL default '',
`signup` varchar(14) NOT NULL default '',
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`is_approved` enum('0','1') NOT NULL default '1',
`total_files` smallint(6) NOT NULL default '0',
`last_logged` datetime NOT NULL default '0000-00-00 00:00:00',
`birthdate` varchar(8) NOT NULL default '',
`signup_ip` varchar(60) NOT NULL default '',
`signup_port` varchar(30) NOT NULL default '',
`height` varchar(10) NOT NULL default '',
`notify_comments` enum('yes','no') NOT NULL default 'yes',
`allow_pm_email` enum('yes','no') NOT NULL default 'yes',
`hits` int(15) NOT NULL default '0',
`maritial_status` varchar(20) NOT NULL default '',
`private_uname` varchar(30) NOT NULL default '',
`private_pass` varchar(30) NOT NULL default '',
`Ethnicity` varchar(30) default NULL,
`Religion` varchar(30) default NULL,
`Do_You_Smoke` varchar(30) default NULL,
`Do_You_Drink` varchar(30) default NULL,
`AIM_screen_name` varchar(30) default NULL,
`Yahoo_ID` varchar(30) default NULL,
`MSN_Messenger` varchar(30) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `md5key` (`md5key`),
KEY `sex` (`user_type`),
KEY `timestamp` (`timestamp`),
KEY `signup` (`signup`),
KEY `username` (`username`),
KEY `email` (`email`),
KEY `subscribed` (`subscribed`)
)


Basically what I want to do is not to lose the data from the old table. I had gotten some new features for my site, and the new table is the tablethat was made for that, but I want to be able to keep all the user data from the old tableso I don't lose any user information. So if there is a way to copy the data from the old table into the new one that would be great. But the structure is a little different though.

Thanks again

[mitchind]

Assuming all the old fields exist in the new table, you can try the following SQL:
INSERT INTO my_new_table SELECT my_old_table.* FROM my_old_table;
If there's field names that are slightly different you can build a select query and rename the old field names to the new names. Then use that query in place of 'my_old_table' above.

By the way, the manual is a great place to start.

http://dev.mysql.com/doc/refman/4.1/en/insert.html