Table in desperate need of repair

Anon

When first learning php and mySQL, I put together a very poorly structured table. The table is a list of schedules for 100 teams, with each team having about 10 games. The way I structured the table has the ten games listed for each team, so there is a lot of unnecessary doubling of games. I also neglected to include a primary key (like I said, very poor design).

The current table has the following fields: team_id, game_date, home_away, opponent, result, score

An example of a game listed would look like this:
103|2000-08-29|h|112|w|10-5
112|2000-08-29|a|103|l|5-10

I would like to rebuild it into a table with the following new field structure: game_id, game_date, home_team_id, away_team_id, score (with gamed_id being the primary key)

The same game listed would now look like this:
1|2000-08-29|103|112|10-5

The advantages of the second game listing are obvious. My question is, is there any way that anyone can come up with that would allow me to somehow export the data from the original table (without the doubling of games), into the proper format to be imported into the new table (as a delimited file of some kind)? I realize it's a bit confusing, but I've really put myself in a bit of a jam, and I would greatly appreciate any possible suggestions. Thanks very much in advance.

Anon

You need to use mysqldump to set up a tab-delimited text file of the data in your current table. See:

http://www.mysql.com/documentation/mysql/full/manual.html#mysqldump

You'll get all rows separated by tabs. Then you could import this text file into a spreadsheet. There you can move your columns around, and delete/insert columns. After you're done there, export as tab-delimited.

Create your new table, then use mysqlimport. See:
http://www.mysql.com/documentation/mysql/full/manual_Tools.html#mysqlimport

Anon

Exporting to ascii is an option, but why not build your new table structure, and use a statement like this:

INSERT INTO new_table (new_col1_name, new_col2_name) SELECT (old_col1_name, old_col2_name) FROM old_table;

that would copy the data from your old table directly into your new table without the messy export to ascii.

You can use the old_col1_name and new_col1_name to copy data from any column in the old table into any column of the new table.

Anon

Seems that there is ALWAYS a better way. Thanks for stating it -- wish I'd known about this a couple days ago.

Thanks to you in particular, Vincent, for all your help in this forum -- your many good responses have helped me greatly.

--ph

Anon

blush