Form Queries

Ok so I want to write a query based on form input. The select boxes, and text boxes are easy. The think I have not a schmick about is how to base a query on checkboxes. Essentially I want to list all records that contain a certain (yes/no) field, IF the user checked the box. Anyone have any ideas?

You can just check to see if the checkbox form field is defined using isDefined( 'form.myCheckboxName' ). If it is, the values in the form variable will be a list of the checkbox values the user checked. You can loop over the list and output whatever SQL you need to within the query.

Hi -

Not exactly sure what your end goal is... You mention that you want to list all records that "contain" a certain field. Do you mean you want to list all records that have a certain value for that certain field?

Checkboxes are essentially passed as lists. Multiple checkboxes can have the same NAME attribute. Each checkbox is either CHECKED (and the value is concatenated to the name variable) or it is not checked (no value is passed for this instance).

Here's an example:

<input type=checkbox name=testCheckbox value=1>
<input type=checkbox name=testCheckbox value=3>
<input type=checkbox name=testCheckbox value=5>
<input type=checkbox name=testCheckbox value=8>

When passed, testCheckbox will have the value of whatever is checked, as a comma-delimited list. Assuming all are checked, testCheckbox would have the value: 1,3,5,8 in the above example.

To build a query based on this, you would just test to see if the passed variable contained a specific value. If it did, you would add a WHERE clause for the associated value to the SQL statement:

<cfquery datasource="#db_source#" name="getList">
SELECT *
FROM table
WHERE 0 = 0
<cfif FORM.testCheckbox CONTAINS "1">
AND YESNOfieldname_1 = "yes"
</cfif>
<cfif FORM.testCheckbox CONTAINS "3">
AND YESNOfieldname_3 = "yes"
</cfif>
.... so on for each of the possible fields
</cfquery>

Is that what you're looking to do?

-------------------------------

Just realized that the CONTAINS is not a good option. The conditional checking for the existence of "1" would return TRUE for 1, 10, 17, 31, 100.... you get the point. kiteless' approach of looping through to generate the query is much better...

Ok this code:

Gives the following error:



Anyone know why?

What data type is the column "ensuite"? You must make the query match the data type of the column. So if ensuite is boolean, try:

ensuite = yes

or:

ensuite = true

or maybe:

ensuite = 1

Thanks for the help guys! I got it figured out. Turns out I had extra 'WHERE' statments in there. Now I have a new problem. I want to check against a select box. It's a little tricky though, because instead of a simple equality (WHERE form.x = y) I need to check it ONLY if a certain option is NOT selected (my N/A option indicated that the user doesn't care about that field). This is the code I have to check it:



The problem is that it returns the following error:


#form.type# is the form variable, which is a select box. 'type' is from my db, and is a string datatype.

I don't understand why it's not working.

Can you post the SQL that CFMX is trying to run? It should be in the debugging info or the error information.

How do I output the debug info? I Unselected database activity in Administrator, and put ' debug="yes" ' in my cfquery, but I don't see and debugging information.

You must enable CF debugging under the Debugging section of the CF administrator.

Ok this is all I get out of the debugging screen:

Ok it's weird, when I take that code out everything works, when I put it back in I get nothing, and the error occurs on the </cfif> tag in the last conditional statement. Here's what I get:

Is there a field in your table called "Apartment"? Or is the field supposed to be "Type"? In the query above, you're checking to see if the field Apartment = 'Apartment'....

Try this?

56 : <cfif (form.type is 'Apartment')>
57 : and listings.type = "Apartment"
58 : </cfif>

I tried your code and got the same problem. And yes the form field is called type. "Apartment" is one possible value.

No one knows eh?

Resonant:

From what you've described, the following doesn't seem like a valid SQL statement against your DB:

SQL select * from listings where ((0 <= rent) AND (rent <= 5000)) and (baths >= 0) and Apartment = "Apartment" order by listings.listing_number

Do you have a *database* field called "Apartment"? Or is the database field actually "type", and one possible value for that field is "Apartment"? If it's the latter, your query is wrong. You need to remove the #'s around type. That's what I was trying to point out previously...

The only other thing I can recommend at this point is to rearrange your SQL statement slightly:

SELECT * from listings where ((rent >= 0) AND (rent <= 5000)) and (baths >= 0) and type = "Apartment" order by listings.listing_number

You also might try using BETWEEN in your statement. r937 should have some input here...

Hope this helps.