CiviCRM Community Forums (archive)

My choice would be to write a php script to compare names and old addresses, and update the location file with the new adresses. The only other option I can see is fiddle about with MySQL writing a procedure that would do the same, but that would not be my choice. Would you have enough knowledge to create such a script?

Hi guys, thanks for your replies, I have explored my options.

In the end, I ended up not using any PHP (the API or otherwise), cause I didn't see a clear way to join sets of data (one without Internal ID and one with) using PHP scripting, APIs or modules.   I found the API to be unnecessary and burdensome, I'm sorry if I have dissapointed you, but those were my thoughts.  When I know exactly what to do in MySQL, and how to do it, I tend to avoid spending a large amount of extra time involving PHP to accomplish my goals.

I set up a couple sandbox mySQL tables locally where I did some basic work.  The entire process was simple, required only phpMySQLAdmin and CiviCRM built-in Import and export features, and took me just over 1 hour.

Tables contained:
1. Used existing CiviCRM Export feature....Exported data from CiviCRM, including Names, Address fields, and most importantly Internal ID
      ~15000 rows named this table civi
2. Data from our mail vendor which includes Name, Address (old and new) but no Internal ID  ~2000 rows named this table returns

I updated returns table #2 based on a set of matching criteria:
   a. name
   b. city
   c. state
   d. zip

This was enough to match 98% of all Contacts, where I then gathered the ID from one table and updated it into the other table

This query tests my data and my theory:
SELECT DISTINCT returns.Name, civi.Internal_ID, returns.Current_Delivery_Address
FROM `returns` JOIN `civi` ON
returns.Name = CONCAT(civi.First, ' ', civi.Last) AND
returns.Previous_City = civi.City AND
returns.Previous_State = civi.State AND
returns.Previous_ZIP = civi.Postal

This was the query that did the heavy lifting and actually made the updates:
UPDATE `returns` SET returns.Internal_ID = (
SELECT civi.Internal_ID FROM `civi` WHERE
returns.Name = CONCAT(civi.First, ' ', civi.Last) AND
returns.Previous_City = civi.City AND
returns.Previous_State = civi.State AND
returns.Previous_ZIP = civi.Postal
)

This query double checks my work:
SELECT returns.Name as ReturnsName, CONCAT(civi.First, ' ', civi.Last) as CiviName,
returns.Internal_ID, civi.Internal_ID
FROM
`returns` JOIN `civi` USING (Internal_ID)

Once my data was ready, I exported it from mySQL using phpMySQLAdmin as a CSV, and then mapped and imported this data into CiviCRM using the CiviCRM Import tool, careful to map to External ID set my duplicates to "update".    I found that the Import tool froze if I imported more than 1000 records, so I split my import in half.  I find this is a best practice when importing into Civi anyway