TheDesigners
asked on
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>
>> I build arrays from comma serperated values
Oops.. looks like you've already got a list ;-) So you can pass directly into your query.
Oops.. looks like you've already got a list ;-) So you can pass directly into your query.
ASKER
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.descrip tion#)>
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.
<cfreturn (#qGetDescriptions.descrip
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.
ASKER
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.cf c" 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.s ource#">
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]=des cription>
</cfloop>
<cfset totalRecords = qGetDescription.RecordCoun t>
<cfloop index="Counter" from="1" to="#totalRecords#">
<cfoutput>
Description: #qGetDescription.descripti on#[1]
</cfoutput>
</cfloop>
<cfreturn results>
</cffunction>
</cfcomponent>
<cfcomponent displayname="optionList.cf
<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.s
SELECT description
FROM dbo.options
WHERE id IN (<cfqueryparam value="#options#" cfsqltype="cf_sql_varchar"
</cfquery>
<cfset results = arrayNew(1)>
<cfloop query="qGetDescription">
<cfset results[CurrentRow][1]=des
</cfloop>
<cfset totalRecords = qGetDescription.RecordCoun
<cfloop index="Counter" from="1" to="#totalRecords#">
<cfoutput>
Description: #qGetDescription.descripti
</cfoutput>
</cfloop>
<cfreturn results>
</cffunction>
</cfcomponent>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Your solution works, The reason for the array is to be able to append additional information to the return.
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 ;-)
<cfquery name="qGetDescriptions" datasource="#request.dsn.s
SELECT description
FROM dbo.options
WHERE id IN ( <cfqueryparam value="#id#" cfsqltype="cf_sql_varchar"
</cfquery>