MySQL Migration Script Help

Question

I am working on a site that lists a directory of various restaurants, and currently in the process of switching to a newer CMS. The problem I have is that both CMSes represent the restaurant data differently.

Old CMS

A Cross Reference Database so it may list an entry for an example like this:

ID / FieldID / ItemID / data

3 / 1 / 6 / 123 Foo Street

4 / 2 / 6 / Bar

One reference table that reference FieldID 1 as street, FieldID 2 as City.

Another reference table that references ItemID 6 as Delicious Restaurant.

New CMS

The way the database is on the new CMS when I set up a sample listing, is all direct rows, no cross referencing. So instead the data for the same restaurant will be:

ID / Name / Street / City

3 / Delicious Restaurant / 123 Foo Street / Bar

---

There are about 2,000 restaurant listings so it's not a HUGE amount in terms of SQL row data size, but of course enough to not even consider re-entering all the restaurant listings by hand.

I have a few ideas, but it would be extremely dirty and take a while, and I'm not a MySQL expert so I am here for some ideas how I should tackle it.

Many thanks to those who can help.

Answer 1

You can join against the data table multiple times to get something like this:

insert into newTable
select oldNames.ItemID,
       oldNames.Name,
       oldStreets.data,
       oldCities.data
from   oldNames
    inner join oldData as oldStreets on oldNames.ItemID = oldStreets.ItemID
    inner join oldData as oldCities on oldNames.ItemID = oldCities.ItemID
    inner join oldFields as streetsFields 
        on oldStreets.FieldID = streetsFields.FieldID
        and streetsFields.Name = 'Street'
    inner join oldFields as citiesFields 
        on oldCities.FieldID = citiesFields.Field
        and citiesFields.Name = 'City'

You didn't provide names for all of the tables, so I made some names up. If you have more fields that you need to extract, it should be trivial to extend this sort of query.