Import data into a MySQL database from an Access database on Linux. Solution should be able to run without human interaction beyond providing the .mdb file and running the script.
## Deliverables
This project is for a database tool in PHP 5. I need to import data from Microsoft Access files into MySQL. This is a very uncomplicated table to table import. There is no relational model to maintain - it's a simple 1 to 1 record reconciliation. The tool should look at each record one by one and compare the appropriate columns (12-15) to the MySQL table. If the MySQL table has a different value (ignoring whitespace differences), it should be updated in MySQL. The critical component here is keeping the primary keys from each table valid as the data is being used in other locations as well - there will be columns in the target DB that are not in the source DB.? If a column is found in the source DB that is NOT in the target, it should be added. No purging of existing records is required.? Additionally, the code should be readable so that changes can be made as necessary to the update statements.
The code does not require formatted output - only to make the appropriate updates.
The environment is Linux with Apache 2.0, MySQL 5.0 and PHP 5.1.6. Upgrading PHP/MySQL is NOT an option, however, installing extensions, drivers etc is. Actual source database to be provided at award of project.
Example Table 1 (Microsoft Access DB - actual table has 23-25 columns)
| Id? | Name? | Address? | City? | State? | Zip? |
| 12? | John | 123 Anystreet? | Anycity? | VA? | 12345? |
| 24? | Mike? | 234 Somelane? | Thiscity? | HI? | 09876? |
Example Table 2 (MySQL 5.0 - actual table has 26-28 columns)
| Id? | OldId? | Name? | Address? | City? | State? | ? Zip | ? Val_1 | Val_2? |
| 1? | ? 12 | John | 123 Anystreet? | Anycity? | VA? | 12345 | 12.3456 | 34.5678 |
| 2? | ? 24 | Mike? | 234 Somelane? | Thiscity? | HI? | 09876 | 23.4567 | 33.3333 |