Copying information from one MySQL DB Table to another, PHPMyAdmin

[Punkrulz]

Hey guys,

I'll tell y'all what I have, what I'm doing, and what the problem is so you can understand it better.

Previously I've taught myself a bit of PHP and created mysefl a form with PHP that I could add calls into a database and call them back. I had created 12 seperate pages, 1 for each month for fire calls [it's for my fire department], and 12 seperate tables, one per month. I have recently been told that is bad programming and what I can do to fix it.

So now, I've added another field to the tables I've been working on that would allow me to show all information for a specific month, and sort by the run number, which is the primary key and an auto-incrementing digit.

The problem I'm running into is this: I created the table "2005" with the same fields as the other tables. I've been trying to copy the information out of one table and into 2005, starting with June since that's the first table that has information in it. I've been using the Operations tab under the table I want to copy as the source. There is a conflict though. When I start copying the other months, sometimes the primary key conflicts with another that was previously copied.

Since they were seperate tables, you might find run #2 in June, and run #2 in July. Is there a way that I could use a SQL Script, to copy the information out of the table, and without damaging the existing table [incase anything goes wrong] drop the primary key in the existing table, copy it over to 2005, and then reapply the keys in numerical order? Basically getting assigned a new primary key in the copy process?

Hope you guys understand what I'm talking about, I think that's the easiest way I can explain it. Please let me know if you have any questions, and thanks in advance!

 

[Geffy]

look at the Operations tab for the table in phpMyAdmin, or also take a look a the Export one as well

 

[Punkrulz]

look at the Operations tab for the table in phpMyAdmin, or also take a look a the Export one as well

In my post I had stated that's what I had been using, it has an option to copy table with structure, data, or both to another table. I was copying data only. As I had stated though, the problem is that my primary key in each table sometimes conflicts with the primary key in another table, which messes up the copying entirely.

I need a way that it sort of starts copying it, and then it dismisses the primary key in what's being copied, then applies it to the new table and then reassigns the primary key... in that order. Otherwise, if I get rid of the primary key in the first step, that's going to give me problems with my already existing table.

 

[Khayman]

create a new empty table with the same structure
then populate it with the other 2 tables
using
insert into temp (field names (not PK)) select field names (not PK) from table 1
insert into temp (field names (not PK)) select field names (not PK) from table 2

then rename temp
than should do it.

At least i think thats what you want

 

[albybum]

If your Run number is the only thing used as your primary key in the new table, and that is where your conflict lies, change the primary key on the new table to include the month or some other unique combination.

Primary Key (month,run)

instead of

run datatype primary key;