coldfusion where clause loops

[maxCohen]

I have a form where the user selects what department data they want to look at by checking off the areas they are interested in. The checkbox data is passed to the query as a list which is where I'm running into problems. Each department has its own set of data that will be output to a table on the results page. Right now the output is coming out with the whole department as a list and only one of the department's data showing up. How do I get each individual department's name and it corresponding data set to come out so it can be displayed?

Example:

<cffunction access="public" name="getInformation">
<!--- checks to see that there is a value for the department list --->
<cfif isDefined("form.department") is "True">
<cfquery name="getInfo" dataSource="departmentdata">
SELECT *
FROM DepartmentSet
WHERE department IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.department#" list="yes" separator=",">) AND location = 'Gettysburg'
ORDER BY department ASC
</cfquery>
<cfelse>
<cfquery name="getInfo" dataSource="departmentdata">
SELECT *
FROM DepartmentSet
WHERE department = 'none'
</cfquery>
</cfif>

<cfreturn getInfo>
</cffunction>

 

[Glowball]

I'm not completely sure I understand what you mean. If you put this before your cfreturn tag are you getting the results you were expecting in all cases?

<cfdump var=#getInfo#><cfabort>

Also, if this is a function then the arguments you pass to it are in the ARGUMENTS scope. The page that calls something like getInformation("HR") would know about the FORM scope, but the getInformation() function won't know that scope. See

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_f_21.html

for more information about that. See if this works for you (I didn't test it because I don't have your data, but something along this line should work):

<cffunction access="public" name="getInformation">
<cfargument name="department" type="string" default="" />

<cfquery name="getInfo" dataSource="departmentdata">
SELECT *
FROM DepartmentSet
<cfif Len(Trim(ARGUMENTS.department))>
WHERE department IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ARGUMENTS.department#" list="yes" separator=",">) AND location = 'Gettysburg'
<cfelse>
WHERE department = 'none'
</cfif>
ORDER BY department ASC
</cfquery>

<cfreturn getInfo>
</cffunction>

<cfset myvar = getInformation()>
<cfdump var=#myvar#>