Exporting from Excel to MySQL
It looks quite straight forward to export fields from Excel into fields in the one table of a MySQL database (by maybe using a converter tool or a simple PHP script). However my knowledge is lacking as to the best approach to export from Excel to MySQL when the target is more than one MySQL table (i.e. the MySQL table relationship utilises foreign keys).
I'll give you an example of the column headers of my excel spreadsheet.
firstname ¦ surname ¦ telephonenumber ¦ town
then I would like to export the data underneath these headings into a mysql database (below):
Here's an example of the MySQL tables:
PeopleTable
firstname (Dave)
surname (Smith)
telephonenumber (01234322333)
town (3)
TownsTable
townID (3)
town (London)
The problem here is that the data in the excel file will include the word 'London' but will not include the townID. Assuming that the Towns mysql table is already populated, would the best approach be to put some kind of dropdown field in the excel town field where the value is the TownID. I suppose the data that needs to populate this Excel drop-down field could come from an initial query to the MySQL database?
Are they any MySQL users out there who appreciate what I'm trying to do and can lend some advice? Should I try and produce a PHP script or can anyone recommend a tool that would do this, or a script that has already been written?
