I have two databases, the first contains user information. Some, but not all users have provided their area codes and cities. The second database is a ZIPcode lookup with info on the city, state, areacode, zip, etc.
The goal is to populate the user database with the most accurate ZIP code data. (I know it won't be at all perfect, but better than nothing.) I've come up with this code -- but it's extremely slow and probably not the best way to do things. I'm just learning this stuff. Can anyone suggest a better way to do this? Is it possible to combine queries from two different databases? -B <!--- select a userid, city, areacode where the areacode and city are not empty ---> <cfquery name="GetUserID" datasource="users"> SELECT UserID, areacode , City FROM tUsers WHERE NOT areacode IS NULL and NOT City IS NULL; </cfquery> <cfloop query="GetUserID" startrow="1" endrow="1000"> <!--- get the zipcode, City, State from ZipCodes based on the areacode ---> <cfquery name="GetZipCode" datasource="zipcodes"> SELECT ZipCode, City, StateCode FROM tZIPCODES <!--- make sure the zipcode is the "standard" type, the city is close to what the user has entered and citytype is D - Default City Name - recommended by the USPS ---> WHERE AreaCode = '#GetUserID.areacode #' and City like '%#GetUserID.City#%' and ZIPCodeType = 'S' and CityType = 'D' </cfquery> <!--- make sure a value was found otherwise skip ---> <cfif GetZipCode.recordcount NEQ 0> <!--- now update the tUsers table with new values ---> <cfquery name="UpdateZip" datasource="users"> Update tUsers SET Zip = '#GetZipCode.ZipCode#', City = '#GetZipCode.City#', State = '#GetZipCode.StateCode#' WHERE UserId = #GetUserID.UserID# </cfquery> </cfif> </cfloop> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
