Copy link to clipboard
Copied
Hello all, I am very new to CF and our regular guy is on vaction so I could really use some help. I am getting geo location from Bing for multiple address that I have in a db. Then add the latitude and longitude to the latitude field and longitude field in the respective row I am able to run the function(s) indivulally but I need help looping through each row. Here is what I have. I hope this makes sense!
<!---query the db--->
<cfquery datasource="square" name="ListingShort">
SELECT
MLS_Id,List_Price,Public_Address,Street_Number,Street_Name,Unit_Number,City,Zip_Code,Subdivision_Name,Bedroom,Baths_Total,Full_Baths,Half_Baths,Square_Feet,Lot_Sqft,Garage_Capacity,Garage_Type,High_School,Junior_School,Property_Description,Listing_Office_Name,Listing_Office_Id,Listing_Agent_Name,Listing_Agent_Phone,Listing_Agent_Id,Short_Sale,Open_House_Flag,Last_Image_Update,Price_Change_Date,Image_Count,Latitude,Longitude
FROM GLVAR_daily_bulk
WHERE MLS_Id=1375233
</cfquery>
<!---get the data back from Bing--->
<cfhttp url="http://dev.virtualearth.net/REST/v1/Locations/US/NV/#ListingShort.Zip_Code#/#ListingShort.City#/#Lis..." timeout = "2" method="GET">
<cfset xbinglocation = xmlparse(cfhttp.filecontent)>
<cfset lat=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Location.Point.Latitude.XmlText>
<cfset long=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Location.Point.Longitude.XmlText>
<!---update the latitude and longitude fields--->
<cfquery datasource="square" name="addGeo">
UPDATE glvar_daily_bulk
SET Latitude = #lat#,
Longitude = #long#
WHERE MLS_Id=1375233
</cfquery>
Yo Thanks. Worked perfect. Im sure that was easy for you but you saved me countless google searches! I owe you one.
Copy link to clipboard
Copied
Do you need to loop over all the rows, or just some of them? I'm assuming the former. In which case simply modify the first query to get all the records, then move the rest of the code into a loop over the query.
<!---query the db--->
<cfquery datasource="square" name="ListingShort">
SELECT MLS_Id,Street_Number,Street_Name,City
FROM GLVAR_daily_bulk
</cfquery>
<cfloop query="ListingShort">
<!---get the data back from Bing--->
<cfhttp url="http://dev.virtualearth.net/REST/v1/Locations/US/NV/#ListingShort.Zip_Code#/#ListingShort.City#/#Lis..." timeout = "2" method="GET">
<cfset xbinglocation = xmlparse(cfhttp.filecontent)>
<cfset lat=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Location.Point.Latitude.XmlText>
<cfset long=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Location.Point.Longitude.XmlText>
<!---update the latitude and longitude fields--->
<cfquery datasource="square" name="addGeo">
UPDATE glvar_daily_bulk
SET Latitude = #lat#,
Longitude = #long#
WHERE MLS_Id = #ListingShort.MLS_Id#
</cfquery>
</cfloop>
Copy link to clipboard
Copied
Yo Thanks. Worked perfect. Im sure that was easy for you but you saved me countless google searches! I owe you one.
Copy link to clipboard
Copied
No problem. Also it's a good idea to use <cfqueryparam> in all your queries when passing in values from elsewhere. Basically it helps performance and reduces chances of SQL injection. e.g.
<cfquery datasource="square" name="addGeo">
UPDATE glvar_daily_bulk
SET Latitude =<cfqueryparam value="#lat#" cfsqltype="cf_sql_float">,
Longitude = <cfqueryparam value="#long#" cfsqltype="cf_sql_float">
WHERE MLS_Id = <cfqueryparam value="#ListingShort.MLS_Id#" cfsqltype="cf_sql_integer">
</cfquery>
Copy link to clipboard
Copied
Cool, thanks again. I hate to keep asking but is there a simple cfcatch or cftry that will skip the row if there is incorrect data that throws an error? Example in one row the city was set to "other" and Bing didn't like it. Could it be ignored and have the loop continue?
Copy link to clipboard
Copied
It would depend a little bit on what Bing was doing. e.g. if it returned a 404 or 500 error, you could watch for that using cfhttp.statusCode.
Or if it still returns a bunch of XML, you could check for that being in the correct format.
Or just wrap the whole thing in a try-catch (usually a good idea when calling an external website anyway).
This code combines the first and last ideas.
<cfloop query="ListingShort">
<cftry>
<!---get the data back from Bing--->
<cfhttp url="http://dev.virtualearth.net/REST/v1/Locations/US/NV/#ListingShort.Zip_Code#/#ListingShort.City#/#Lis..." timeout = "2" method="GET">
<cfif val(cfhttp.statusCode) EQ 200> <!--- will turn "200 OK" into 200 --->
<cfset xbinglocation = xmlparse(cfhttp.filecontent)>
<cfset lat=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Location.Point.Latitude.XmlText>
<cfset long=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Location.Point.Longitude.XmlText>
<!---update the latitude and longitude fields--->
<cfquery datasource="square" name="addGeo">
UPDATE glvar_daily_bulk
SET Latitude =<cfqueryparam value="#lat#" cfsqltype="cf_sql_float">,
Longitude = <cfqueryparam value="#long#" cfsqltype="cf_sql_float">
WHERE MLS_Id = <cfqueryparam value="#ListingShort.MLS_Id#" cfsqltype="cf_sql_integer">
</cfquery>
</cfif>
<cfcatch type="any">
<cfoutput>Oops, an error on row ID = #ListingShort.MLS_Id#</cfoutput> <cfdump var="#cfcatch#">
</cfcatch>
</cftry>
</cfloop>
Copy link to clipboard
Copied
Seriously, thanks!