• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Loop through result set and delete row

New Here ,
Feb 26, 2014 Feb 26, 2014

Copy link to clipboard

Copied

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

Views

1.8K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 26, 2014 Feb 26, 2014

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 27, 2014 Feb 27, 2014

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 27, 2014 Feb 27, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 27, 2014 Feb 27, 2014

Copy link to clipboard

Copied

Thanks for the that. It seems to be working but when the do a totalRowCount on the query it gives me the count including the rows that were deleted? How should I fix this?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 27, 2014 Feb 27, 2014

Copy link to clipboard

Copied

You have to do the row count on the new result set, not on the original query.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 27, 2014 Feb 27, 2014

Copy link to clipboard

Copied

I am deleting rows from the original query itself

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 27, 2014 Feb 27, 2014

Copy link to clipboard

Copied

LATEST

tjbee wrote:

I am deleting rows from the original query itself

Could you let us see some code? I am sure it will inspire suggestions straight away.

In any case, as I said before, you could do that as a query of a query. That is convenient because you will end up with a smaller result set. You could then use this smaller query as you would the original.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation