Comparison Script -- PHP/MySQL and MS SQL

Objective: To determine the success rate of our client's direct mail marketing campaigns by comparing the mailing lists against the order database in an ERP. Background: Our client purchases targeted marketing lists from third party companies. These lists include first name, last name, and address information. We currently receive the list--which include multiple list sources--in a comma delimited file with at .txt extension. Current Setup: We currently have a MySQL database storing these mailing list names/addresses and campaign information. We have a quickie custom written upload utility that parses the .txt file and writes information to this database. (The current schema of this database is below.) This information is then processed by our current script which compares the mailing database against the order entry system database. The ERP database currently runs off a separate MS SQL server (which we can replicate to MySQL or leave in place on the MS SQL server). The system compares mailing list information to our names in the ERP. There are two tables in the ERP that are used for our date of creation and our demographic information. If we find the mailing list address and name in our order entry database then a match is found. We can currently pick which mailing list we want and run a scan on just that mailing list. We can also narrow the searches down by the date the customer was created in our order entry system. Current Issues: The system that we have now is very slow and doesn't seem to be accurately matching. I suspect that there are some major bugs in the system. New System Requirements: Rewrite of the analysis script to be more accurate and provide a way to email/download reports. These reports should output the criteria that was used during the scan and list out the mailing list id number along with the number of matches that it finds. We also need a selectable setting that would allow us the ability to set a parameter to do “fuzzy matching” We were trying to use the Levenshtein Distance Algorithm, but we are open to other ideas as to how to do fuzzy matching. Database Name: Mailer Table Name: campaign Fields: campaignID campaign_name campaign_tollfree_number campaign_start_date Table Name: customer_info Fields: customerId customer_firstname customer_lastname customer_street1 customer_street2 customer_city customer_state customer_zipcode plus4 campaignID mailinglistID