Skip navigation
CF_noobi_wan
Currently Being Moderated

Looping, Setting and Updating! Oh My.

Aug 26, 2013 9:12 PM

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_Numbe r,City,Zip_Code,Subdivision_Name,Bedroom,Baths_Total,Full_Baths,Half_B aths,Square_Feet,Lot_Sqft,Garage_Capacity,Garage_Type,High_School,Juni or_School,Property_Description,Listing_Office_Name,Listing_Office_Id,L isting_Agent_Name,Listing_Agent_Phone,Listing_Agent_Id,Short_Sale,Open _House_Flag,Last_Image_Update,Price_Change_Date,Image_Count,Latitude,L ongitude

       

            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#/#ListingShort.Street_Number#%20#ListingShort .Street_Name#?o=xml&key=AgM3wR0ojSpxYaJeh6WS7p2kcckECqVQ5HkrweFcZCcyOj E3mYVvIrF_WzbETMeb" timeout = "2" method="GET">

 

 

<cfset xbinglocation = xmlparse(cfhttp.filecontent)>

<cfset lat=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Locatio n.Point.Latitude.XmlText>

<cfset long=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Locati on.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>

 
Replies
  • Currently Being Moderated
    Aug 27, 2013 2:13 AM   in reply to CF_noobi_wan

    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#/#ListingShort.Street_Number#%20#ListingShort .Street_Name#?o=xml&key=AgM3wR0ojSpxYaJeh6WS7p2kcckECqVQ5HkrweFcZCcyOj E3mYVvIrF_WzbETMeb" timeout = "2" method="GET">

     

        <cfset xbinglocation = xmlparse(cfhttp.filecontent)>

        <cfset lat=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Locatio n.Point.Latitude.XmlText>

        <cfset long=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Locati on.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>

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 27, 2013 8:37 AM   in reply to CF_noobi_wan

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 27, 2013 9:21 AM   in reply to CF_noobi_wan

    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#/#ListingShort.Street_Number#%20#ListingShort .Street_Name#?o=xml&key=AgM3wR0ojSpxYaJeh6WS7p2kcckECqVQ5HkrweFcZCcyOj E3mYVvIrF_WzbETMeb" 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.Locatio n.Point.Latitude.XmlText>

                <cfset long=xbinglocation.Response.ResourceSets.ResourceSet.Resources.Locati on.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>

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points