Skip to main content
T
tjbee
Participant
February 26, 2014
Question

Loop through result set and delete row

  • February 26, 2014
  • 1 reply
  • 2239 views

so here is what I need -

I have a query that pulls rows from the Database through a stored procedure.(these are properties in an area)

Before I start looping through the query, I need to check the distance between my current location and the property. If it less than 5 miles, only then should I display that property. The distance in miles will be chosen while submitting the search form.

So is there a way to delete rows from the result set based on the criteria? Or

Is there a better way to accomplish this? I am using the the google api to get the latitudes and longitudes. The other issue to keep in mind is the load time.

Thanks

This topic has been closed for replies.

1 reply

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
February 27, 2014

tjbee wrote:

So is there a way to delete rows from the result set based on the criteria?

A fundamental question, to which there is a fundamental answer: query of a query. For example

<cfquery name="newResultSet" dbtype="query">

select *

from originalQuery

where col1='criterium 1' and col2='criterium 2'

</cfquery>

T
tjbeeAuthor
Participant
February 27, 2014

My result set is coming from a stored procedure.

I want to loop through that check, check for a condition and then remove the row if it doesn't meet the criteria

R
Reed_Powell-ttnmOb
Inspiring
February 27, 2014

You can do this the easy way or the hard way.  Depends on whether your condition that needs to be checked can be expressed in the form of a SQL where clause.  If it can, then do what BKBK suggested, and use a query of query to create a new resultset that only has the rows from the original resultset that don't meet your condition.

If the calculation of the condition is more complex, then do a CFLOOP over the query and examine each row to see if you want to keep it or toss it.  if you want to toss it, the delete that row - there is a function in CFLIB.ORG called querydeleterow that should help you.  Or, you could just clear out the row's contents and then do the query of query as described in BKBK's post to create a new resultset that doesn't include the blank rows.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices