迁移MS Access到MySQL数据库的问题

Question

We recently developed a PHP/MySQL program that works fine when test data is entered through the program but when we migrated actual data from MS Access to MySQL using ODBC there were problems.

The MySQL and Access databases have quite different schemas. Looking at the migrated data in phpmyadmin, it appears that the data was imported (all the data is there) but when we try to view data through the program, data retrieval is selective (some data is retrieved and some is not). For example, if I select “company” on a certain page from a dropdown, it correctly displays company information but if I navigate to another page that is supposed to display products associated with a subdivision and select “subdivision” from a dropdown, it either doesn’t display any data or displays only one product even though there are several.

How do I troubleshoot this problem?

Answer 1

Originally we were looking for a protocol or some suggestions on how to troubleshoot this issue. We decided it would be more manageable to initially look at a small section of data only. We discovered that the data needed to be cleaned. There were text strings in fields that should not allow them and strange characters in certain fields. There was also an issue with blank spaces on certain data. To fix this, we had to strip all the blanks and then add them in again. Once the data was cleaned, the program was working as it should.