Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

Coldfusion loop and cffunction return

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
TheDesigners
Asked:
TheDesigners
  • 4
  • 3
1 Solution
 
_agx_Commented:
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
 
_agx_Commented:
>> 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
 
TheDesignersAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TheDesignersAuthor Commented:
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
 
_agx_Commented:
>> 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
 
TheDesignersAuthor Commented:
Thanks. Your solution works, The reason for the array is to be able to append additional information to the return.
0
 
_agx_Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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