?
Solved

Coldfusion loop and cffunction return

Posted on 2010-09-10
8
Medium Priority
?
1,038 Views
Last Modified: 2012-05-10
How do I get a cffunction to return results from a looped query? The follow is my code and while one result is returned (the last query), I need a way for the return to contain all the results from the looped query.

<cfcomponent displayname="optionList.cfc" hint="I build arrays from comma serperated values.">
	
  <cffunction
    name="dataList"
    access="public"
    returntype="any"
    output="false"
    hint="I invoke the createArray component and return the results">
    
  <cfargument
      name="options" 
      type="string" 
      required="false"
      hint="I contain the comma seperated list">
    
    <cfset arrayObject = CreateObject("component","/com/createArray").listToArray(
          listData = arguments.options,
          Delimiter = ",",
          Qualifier = """"
          ) />
        
    <cfreturn getDescriptions( arrayObject=arrayObject )>
  </cffunction>
  
  <cffunction
    name="getDescriptions"
    access="private"
    returntype="any"
    output="false"
    hint="I get the option descriptions">
    
    <cfargument
      name="arrayObject"
      type="array"
      required="true"
      hint="I contain the array object">
      
    <cfloop index="i" from="1" to="#arrayLen(arrayObject[1])#">
    <cfset id = arrayObject[1][i]/>
        
    <cfquery name="qGetDescriptions" datasource="#request.dsn.source#">
          SELECT description
          FROM dbo.options
          WHERE id = '#id#'
     </cfquery>
       
    </cfloop>
      
    <cfreturn (#qGetDescriptions.description#)>        
  </cffunction>
  
</cfcomponent>

Open in new window

0
Comment
Question by:TheDesigners
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 33651159
I think a better approach is to just run a single query using IN (....). First loop to generate a list of id's.  Then pass that list to your query

<cfquery name="qGetDescriptions" datasource="#request.dsn.source#">
SELECT description
FROM dbo.options
WHERE id IN (  <cfqueryparam value="#id#" cfsqltype="cf_sql_varchar" list="true"> )
</cfquery>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33651161
>> I build arrays from comma serperated values

Oops.. looks like you've already got a list ;-) So you can pass directly into your query.
0
 

Author Comment

by:TheDesigners
ID: 33651226
OK, passing the list to one query works to get all the results (tested in SMSS),  but what do I set the return to? When left as:

<cfreturn (#qGetDescriptions.description#)>

only the first "description" is returned. This is how I'm invoking the cffunction, and I'm displaying the results for testing using a cfdump:

<cfinvoke
      component="com.optionList"
      method="dataList"
      options="1,3,43,99"
      returnvariable="result">
</cfinvoke>
<cfdump var="#result#"/>

So, the query does get all the results, as tested in SMSS, but only the first description in the query is displayed in the cfdump.
0
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

Author Comment

by:TheDesigners
ID: 33651932
Figured it out on my own. Solution was to place the query results into an array, return that array, and finally loop through that array to display the results:

<cfcomponent displayname="optionList.cfc" hint="I build arrays from comma serperated values.">
      
  <cffunction
    name="dataList"
    access="public"
    returntype="any"
    output="false"
    hint="I query a database table based on the variables passed in">
   
    <cfargument
      name="options"
      type="string"
      required="false"
      hint="I contain the comma seperated list">
   
    <cfquery name="qGetDescription" datasource="#request.dsn.source#">
        SELECT description
        FROM dbo.options
        WHERE id IN (<cfqueryparam value="#options#" cfsqltype="cf_sql_varchar" list="true">)
      </cfquery>
     
      <cfset results = arrayNew(1)>
     
      <cfloop query="qGetDescription">
            <cfset results[CurrentRow][1]=description>
      </cfloop>
     
      <cfset totalRecords = qGetDescription.RecordCount>
      <cfloop index="Counter" from="1" to="#totalRecords#">
            <cfoutput>
              Description: #qGetDescription.description#[1]
        </cfoutput>
      </cfloop>
       
      <cfreturn results>
  </cffunction>
 
</cfcomponent>
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 33658758
>> Solution was to place the query results into an array, return that array, and finally
>> loop through that array to display the results:

I think you're making it too complicated.  There is really no need for the extra conversion to an array. Just return the query object.  
ie <cfreturn qGetDescriptions>

When you want to display the results, loop through the query as usual.
<cfoutput query="qGetDescriptions">
     Description: #qGetDescription.description#
</cfoutput>

0
 

Author Closing Comment

by:TheDesigners
ID: 33700409
Thanks. Your solution works, The reason for the array is to be able to append additional information to the return.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33702234
Okay.  As long as that's the only way to append the extra information.  Sometimes people create extra arrays or loops when they're not needed ;-)
0

Featured Post

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question