Skip to main content
Participant
August 27, 2013
Answered

Looping, Setting and Updating! Oh My.

  • August 27, 2013
  • 1 reply
  • 1124 views

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#/#ListingShort.Street_Number#%20#ListingShort.Street_Name#?o=xml&key=AgM3wR0ojSpxYaJeh6WS7p2kcckECqVQ5HkrweFcZCcyOjE3mYVvIrF_WzbETMeb" 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>

    This topic has been closed for replies.
    Correct answer CF_noobi_wan

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

    1 reply

    Participating Frequently
    August 27, 2013

    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=AgM3wR0ojSpxYaJeh6WS7p2kcckECqVQ5HkrweFcZCcyOjE3mYVvIrF_WzbETMeb" 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_wanAuthorCorrect answer
    Participant
    August 27, 2013

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

    Participating Frequently
    August 27, 2013

    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>