Migrate mysql data to multi tables with new schema

Question

In the past i created the unlucky mysql schema below and cleaned it now in some points ... This is the baddest part:

|ID|ParentID |listing1|image1|comment1|listing2|image2|comment2|listing3|image3|comment3
|1 |50       |abc     |img1  |abc     |xyz     |img2  |xyz     |qwe     |img3  |qwe
|2 |51       |abd     |img1  |abd     |vyz     |img2  |vyz     |qwz     |img3  |qwz
|3 |52       |rtz     |img1  |rtz     |ghj     |img2  |ghj     |bnm     |img3  |bnm

Now i created the new structure for the old data:

Table1 (listings):

|ID|ParentID|listing|comment
|1 | 50     |abc    |abc 
|2 | 50     |xyz    |xyz
|3 | 50     |qwe    |qwe
|4 | 51     |adb    |adb

Table2 (media):

|ID|ParentID|image
|1 | 50     |img1   
|2 | 50     |img2   
|3 | 50     |img3   
|4 | 51     |img1    

My problem is how to get the old values in the new schema. My first idea is to write a php foreach to fetch the values, store them and insert them again but how ... Or could i do it directly in mysql?

Thanks.

Answer 1

You could create proper table1 and table2 with autoincrement

create table1 (
  id int(11) not null autoincrement primary key,
  parentID int(11) not null ,
  listing varchar(255),
  comment varchar(255)
)
;
create table2 (
  id int(11) not null autoincrement primary key,
  parentID int(11) not null ,
  image varchar(255)
)
;

then a couple of insert select based on Union

insert into table1 (parentID, listing, comment)
select parentID, listing1, comment1
from old_table
union 
select parentID, listing2, comment2
from old_table
union 
select parentID, listing3, comment3
from old_table
;




insert into tabl2 (parentID, image)

select parentID, image1
from old_table
union 
select parentID, image2
from old_table
union 
select parentID, image3
from old_table