CODEWITHSUNDEEP
htmlcoldfusion
espresso_coffee
espresso_coffee

Reputation: 6110

How to show in Active elements if my check box is checked?

I have a problem solving this query. In my where clause I have a logic that gives me just active fields from my table. But I want if my checkbox is checked to give me active and inactive elements. How can I solve that? Is it possible to put cfif around where clause or some other way? Here is my query: 

SELECT Date, Name, Time, Active
       Case when Active = 1 then 'Yes' else 'No' end as Active
FROM City
<cfif form.ActiveCheck GT 0>
WHERE Active = 0
</cfif>
ORDER BY Date DESC

and here is my check box code:

<input type="Checkbox" name="ActiveCheck" id="ActiveCheck" value="0" unchecked>All<br>

Upvotes: 1

Views: 201

Answers (2)

James A Mohler
James A Mohler

Reputation: 11120

This similar to John's answer, but I don't like <cfif> inside of <cfquery>. I want the DB server to have as few query plans as possible. Note: IIF() is an old-school approach.

<cfquery name="myQ">
DECLARE @Active bit = <cfqueryparam value="#IIF(stuctkeyExists(form, 'activecheck'), 1, 0)#" cfsqltype="cf_sql_bit">

SELECT Date, Name, Time, Active,
    Case when Active = 1 then 'Yes' else 'No' end as Active
FROM City
WHERE @ActiveCheck = 0 
OR   (@ActiveCheck = 1 AND Active = 1)
ORDER BY Date DESC
</cfquery>

Upvotes: 0

John Whish
John Whish

Reputation: 3036

If I understand correctly you don't want to filter by the active column in your query when the checkbox is ticked? To do that you'd do something like:

<cfquery name="myQ">
    SELECT Date, Name, Time, Active
        Case when Active = 1 then 'Yes' else 'No' end as Active
    FROM City
    <cfif !structKeyExists(form, "ActiveCheck")>
        <!--- checkbox is not ticked so just get inactive --->
        WHERE Active = <cfqueryparam value="0" cfsqltype="cf_sql_integer">
    </cfif>
    ORDER BY Date DESC
</cfquery>

....

<input type="Checkbox" name="ActiveCheck" id="ActiveCheck" value="0">All<br>

Upvotes: 2

Related Questions