Upload CSV and replace data with foreign keys

Question

Hi

I want to upload data through CSV files in my contact management application. The CSV file structure is like this:

Name, Phone, City
John Doe, 555-555-5555, New York

While the table structure in the db is like this:

name, phone, city_id

In the database, the city name is stored in another table and the foreign key is referenced in the contacts table.

My question is how can I replace the city names in the CSV file with city id for insertion into db.

Background info: Language is PHP and database is MySQL

Thanks

Answer 1

Load firstly into temporary table with structure (Name, Phone, City), and after that make proper insert:

Select name, phone, city_id
from temptable t, cities c
WHERE t.city = c.CityName