condition cfqueryparam in cfquery

iccsi wrote: I want CFQUERYPARAM VALUE = 1,3,5 when my Check box1 is checked by user and CFQUERYPARAM VALUE = 2,4,6 when my check box2 is checked by user. Is it possible to use an if statement or switch in CFQUERYPARAM to set value base on the check box on the form?

That logic is not watertight. What if the user checks no checkbox or both checkboxes? If you wish to make the choices mutually exclusive the most appropriate field to use is Radio. In any case, here is a suggestion

<cfset values = "">

<!--- I have assumed that selecting one checkbox precludes selecting the other --->

<cfif isDefined("form.checkBox1")>

    <cfset values = "1,3,5">

<cfelseif isDefined("form.checkBox2")>

    <cfset values = "2,4,6">

</cfif>

<CFQUERY>

SELECT   *

FROM   MyTable

<cfif values IS NOT "">

    WHERE MyField  in (<CFQUERYPARAM LIST="yes" VALUE="#values#" SEPARATOR=",">)

</cfif>

</CFQUERY>

What do you want to do if both boxes are checked?

<cfqueryparam value="<cfif IsDefined('FORM.checkBox1')#Trim(FORM.checkBox1)#</cfif>" list="1" cfsqltype="CF_SQL_NUMERIC" />

Conditional checking inside "Value" attribute in <CFQUERYPARAM> does not evaluate correctly.

If I am not wrong here, you want to slelect a set of record by passing ID list into CFQUERYPARAM; and this ID list will differ as per checkbox checked. (also ID list would be set in checkbox "value" attribute). Here is the sample code, it may point you a way to do.

<cfform name="update_form" method="POST" action="#CGI.SCRIPT_NAME#">

    <cfinput type="checkbox" name="filter_id_list" id="checkBox1" value="1,3,5" /> Update 1,3,5

    <br />

    <cfinput type="checkbox" name="filter_id_list" id="checkBox2" value="2,4,6" /> Update 2,4,6

    <br />

    <cfinput type="submit" name="do_update" value="submit" />

</cfform>

<cfif IsDefined('FORM.filter_id_list') and Len(FORM.filter_id_list)>

    <cfquery name="myQuery" datasource="myDSN">

        SELECT *

        FROM MYTABLE

        WHERE

            ID IN (<cfqueryparam value="#Trim(FORM.filter_id_list)#" list="1" cfsqltype="CF_SQL_NUMERIC" />)

    </cfquery>

    <cfdump var="#myQuery#">

</cfif>

So this brings me to using cfqueryparam and indicating that it is a list of values instead of a single value using the ‘list’ attribute.

Using cfqueryparam as a listof values

Code:

<CFQUERY NAME="MyQuery" DATASOURCE="MyDataSource" USERNAME="MyUser" PASSWORD="MyPassword">

SELECT   * FROM   MyTable

     WHERE    MyField  in <CFQUERYPARAM VALUE="#Trim(form.myText)#" list="true" >

</CFQUERY>