MySQL Script Migration Reference
I am working on a site that lists a directory of various restaurants and am currently in the process of migrating to a newer CMS. The problem is that both CMSs represent restaurant data differently.
Old CMS
Cross-reference database so it can display a record for an example like this:
ID / FieldID / ItemID / data p>
3/1/6/123 Foo Street
4/2/6 / Bar
One reference table that refers to field id 1 as street, field id 2 as city.
Another reference table that references ItemID 6 as Delicious Restaurant.
New CMS
As the database is on the new CMS, when I set up the sample list, everything is straight lines, no cross-referencing. So instead of data for the same restaurant there will be:
ID / Name / Street / City
3 / Delicious Restaurant / 123 Foo Street / Bar
There are about 2,000 restaurant lists, so this is not a HUGE amount in terms of the size of the data in the SQL row, but of course not even to consider re-entering all the restaurant lists manually.
I have a few ideas, but this will be very messy and take a while and I am not a MySQL expert, so I am here for some ideas how I should solve it.
Many thanks to those who can help.
source to share
You can join 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 did not provide names for all tables, so I made several names. If you have more fields to retrieve, it should be trivial to extend this query.
source to share