• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 857
  • Last Modified:

<cfloop> textarea variables into <cfquery>?

I have this code looping through a text area pulling out zip codes separated by commas:

<cfloop
   index = "NewZips"
   list = "#FORM.ZipArea#"
   delimiters = ",">
       <td><cfoutput>#NewZips#</cfoutput></td><br>
</cfloop>
0
kabookis
Asked:
kabookis
  • 4
  • 3
  • 2
  • +3
1 Solution
 
adonis1976Commented:
what is the error msg u r getting?
0
 
kabookisAuthor Commented:
I'm not getting any errors.
When I run:

<cfloop
   index = "NewZips"
   list = "#FORM.ZipArea#"
   delimiters = ",">
       <td><cfoutput>#NewZips#</cfoutput></td><br>
</cfloop>
0
 
kabookisAuthor Commented:
Let's try this again:

I'm not getting any errors.
When I run:

<cfloop
   index = "NewZips"
   list = "#FORM.ZipArea#"
   delimiters = ",">
      <td><cfoutput>#NewZips#</cfoutput></td><br>
</cfloop>

 I can output the results OK with:

<cfoutput>#NewZips#</cfoutput><
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
pinaldaveCommented:
so what is your question?
Regards,
---Pinal
0
 
mrichmonCommented:
Okay so you want them used in a cfquery

Maybe like this:

<cfquery datasource="yourdsn" name="myquery">
SELECT * FROM mytable WHERE Zip IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.ZipArea#" list="Yes">)
</cfquery>

So that you pull all records that match any of the ones in the list.  Is that what you wanted?
0
 
tpiazza55Commented:
little confused myself -- what is you question
0
 
trailblazzyr55Commented:
just a bit of info... " , " is default in lists, if that it what the list is using, you don't need to add that.

this is what I mean...

this..
<cfloop
   index = "NewZips"
   list = "#FORM.ZipArea#"
   delimiters = ",">

becomes
<cfloop index="NewZips" list="#FORM.ZipArea#">
0
 
trailblazzyr55Commented:
I have a funny feeling you are getting an error because your list of zips may have looked like this...

12345, 12345,12345, 12345, 12334,......

try this also

<cfloop index="NewZips" list="#trim(FORM.ZipArea)#">
0
 
kabookisAuthor Commented:
OK I'm going to try posting this again. Apparently my comments aren't making it through intact.
The posts are cut off well before the question.

This was the original question:

I have this code looping through a text area pulling out zip codes separated by commas:

<cfloop
   index = "NewZips"
   list = "#FORM.ZipArea#"
   delimiters = ",">
       <td><cfoutput>#NewZips#</cfoutput></td><br>
</cfloop>

I need a way to set these variables up for validation against multiple tables with multiple criteria and then be used in a query:


<cfquery name="ZipPrices" datasource="yadda..">
SELECT ri.item, ri.list_order, z.zip1
FROM  residential_items ri, bwm_zips z
WHERE fid = 1
AND zip1 = #NewZips# <----This is the part I don't know how to handle.
GROUP BY  zip1,item
ORDER by zip1 ASC
</cfquery>


      <cfoutput query ="ZipPrices3" group = "zip1">
      <cfoutput group = "zip1"> #zip1#</cfoutput>

      <cfoutput group = "item">
      #item#
       </cfoutput>  

So, how do I put the two together?

Thank you.

####Somehow mrichmon figured out that I wanted to use FORM.ZipArea in a query.

His/her answer works. It works whether the <cfloop> is run or not.

But I can still generate an error if two commas are side-by-side in the form textbox, like this-

49009,49008,49001,     49024, 49034  works but

49009,49008,,49001,     49024, 49034  doesn't.

"Error Occurred While Processing Request

Error Diagnostic Information

ODBC Error Code = 94 ()"

But if the <cfloop> code:

<cfloop
   index = "NewZips"
   list = "#FORM.ZipArea#"
   delimiters = ",">
       <td><cfoutput>#NewZips#</cfoutput></td><br>
</cfloop>

Is run all by itself, there is no error. So that's why I was thinking it would be best to loop through the Form.textarea first as a kind of validation, and then run the query with the results of <cfloop>
Is that a bad idea?

(end of comment)









0
 
mrichmonCommented:
Well it is an idea.

You could do that like this:

<cfset ZipList = "">
<cfloop index="NewZip" list="#FORM.ZipArea#">
<cfif NewZip NEQ ""><cfset ZipList = ListAppend(ZipList, NewZip)></cfif>
</cfloop>

<cfquery datasource="yourdsn" name="myquery">
SELECT * FROM mytable WHERE Zip IN (<cfqueryparam cfsqltype="cf_sql_varchar" value="#ZipList#" list="Yes">)
</cfquery>
0
 
kabookisAuthor Commented:
Yep, this is fabulously effective. Thank you very much.

One other question for mrichmon:

Re: "Well it is an idea."

Do you mean that this strategy with the <cfloop> is not particularly elegant?  Without doing lots of extra work, would you suggest other better approaches?

Thanks again. This was a real lifesaver.
0
 
mrichmonCommented:
There are other ideas I was tossing around and I haven't put much more thought into - like stripping any double commas and extra spaced from the original string using a regular expression.

Probably more efficient, but harder to code.  I couldn't code it for you off the top of my head - I'm only so-so, not an expert in regular expressions

;o)

Glad this worked for you.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now