New Programmer, Need a few pointers on importing and modifying data

[Anthony Accetturo]

I need a few points on a directory im working on rebuilding. It uses a classic database design and is being moved to wordpress, so I built a custom CSV importer but need help getting the data ready to work with the wordpress format.

First issue, I need to do a comparison of two databases. The first is the original database that includes all the listings information, geolocation data, human readable addresses, etc.

The new Wordpress site uses a database mapping all US locations to
City Id | Country Id | Zones Id | City Name
-------------------------------------------------
6 | 226 | 3713 | Holtsville

Country & Zone Id is self contained. Country ID will always be 226. There are 50 possible Zone ID's for all the US States.Some City names maybe duplicated as some towns have the same name so we have a zone ID system.


The goal is to compare the old directory database containing the listings location information with this list. When importing, it must use the ID's to properly work. I do have the City and State separated. For state, I can do that easily as theres only 50 to replace. There are around 30,000 towns, so it will need to not replace but create an additional table in my new database with the matching ID's My research says a SQL Join may do the trick, but I am self taught and just need a little help.

Keep in mind, this database will not be imported, its just being used to store and format the data before I do a CSV import. Its way to large for excel.


Second issue is I am attaching the old listing ID's to the new database entries so if I ever need to update the database or listing with new info I can. How can I perform a action similar to this. The old database has the slugs for URL's I would like to pull them out then reimport them into my new MYSQL database and match them. Can the logic be explained to me as it wont be using the primary key ID?