Looping, Setting and Updating! Oh My.

[CF_noobi_wan]

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>

[duncancumming]

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>

[CF_noobi_wan · Accepted solution]

Yo Thanks. Worked perfect. Im sure that was easy for you but you saved me countless google searches! I owe you one.

[duncancumming]

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>

[CF_noobi_wan]

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?

[duncancumming]

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>

[CF_noobi_wan]

Seriously, thanks!