SQL query to filter database records with checkboxes

[nboss]

Hello,

I am creating a website results page (in Macromedia Dreamweaver) that yields records from my database depending on what checkboxes a user checks on the search page. I am using a MS Access database and Coldfusion.

Basically, my database has a field for each checkbox I have on my search page. In my database, these checkbox fields are populated as "True" or "False", correlating to whether the checkbox is checked, or unchecked. I only want database records to appear that correlate to the checkboxes checked.

I would like the results page to show all records that have false fields in the db, have true fields in the db that the user has checked, and false db fields that the user checked as true.

For example... say there are 3 checkboxes on my search page: checkbox1, checkbox2, checkbox3.

ID checkbox1 checkbox2 checkbox3
1 TRUE TRUE TRUE
2 TRUE FALSE TRUE
3 FALSE FALSE TRUE
4 TRUE TRUE FALSE
5 FALSE FALSE FALSE

Say the user checks checkbox1 and checkbox2, and leaves checkbox3 unchecked. The records returned would be: 4 and 5.

So since the TRUES in the db were checked as TRUE on the search page, record 4 was returned. The False for checkbox3 doesn't matter since this field would be satisfied regardless if the user checked it as true or false. Record 5 is returned for the same reason.

Records 1, 2, and 3 are not returned since they require a TRUE value entered by the user for checkbox3.

How can I create a recordset in Dreamweaver to do this? I assume I must use the "Advanced" section of the recordset wizard, or simply write the SQL code by hand. Please let me know how I can proceed.

Thanks!

[Newsgroup_User]

i always write my sql by hand, so can't really tell you if you can do
this as Advanced RS in DW, but i suspect you will have to do this one by
hand anyway, since you will have to be using <cfif> inside your sql...

i do hope you know at least the basics of sql to write it by hand... if
not there are a lot of examples and tutorials online and the MS Access
help is quiet useful in teaching basic syntax, too... a book "Teach
yourself SQL in 10 minutes" has been repeatedly recommended in this and
other forums as a great start, too

from your example it looks like you want to:
- display a record no matter what its value is if the corresponding
checkbox has been checked;
- only display a field with FALSE value if its checkbox has not been
checked.

if so, then in your query should look something like:
SELECT ....
FROM yourtablenamehere
WHERE 1=1
<cfif NOT isdefiend("form.checkbox1")>
AND yourtablenamehere.checkbox1field = 'FALSE',
</cfif>

(repeat the above 3 lines for each filed you have)
AND 1=1

NOTE: the first and last 1=1 parts in the sql are to make sure your sql
statement is valid: without then you may end up with a trailing "," or a
"WHERE AND" phrase in your sql statement, both of which will make it
invalid...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[nboss]

Thanks Azadi. I will give it a shot. Yes, I am new to SQL, however, I know I can pick it up relatively quickly. I will check out the reference you mentioned.

Thanks again,

Nick

[Newsgroup_User]

cool. shout if you need more help!

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[nboss]

OK, so i'm still a little stuck. Maybe I can explain it a bit better.

My database consists of recipes. Each record is a recipe. Each field is an ingredient ("True" if the recipe calls for the ingredient, "False" is the recipe does not include the ingredient). My search webpage has checkboxes for each ingredient.

A user will check off all ingredients he/she has available. He/She will then hit the submit button, taking them to my results webpage. Here, all the recipes from the database that includes the ingredients the user has, will be displayed.

One catch is, that recipes will only be returned if the user has ALL the ingredients necessary for that recipe. If a user specifies he/she has Ingredients 1,2, and 3... a recipe calling for any of those ingredients, or, a combination of these ingredients will be displayed on the results page. If a recipe calls for ingredients 2 and 4... it will not be displayed since the user does not have ingredient 4.

Does this make more sense? Thanks!

[Newsgroup_User]

what exactly are you stuck on? the explanation you gave helps, but it
does not really add much to your app's logic you have described before:

based on user's selections only records that have one or more of
user-selected ingredients and no non-selected ingredients should be
returned.

please be more specific about what you are having trouble with.


on a (important) related note:
i suggest you review & revise your database structure and normalize it
before you have gone any further with your app development. if you are
not sure what a normalized database is - google "data modeling" or
"normalized database". your current database structure, based on your
explanations, if far from normalized.

e.g:

you should have all your individual ingredients stored in a separate
table, i.e. INGREDIENTS (ingredient_id -
autonumber/integer+autoincrement; ingredient_name - text/varchar; ....).
your recipes (name, description, etc, but NO ingredients) should be in
another table, i.e. RECIPES (recipe_id -
autonumber/integer+autoincrement, recipe_name - text/varchar;
recipe_descr - memo/longtext, ....)
your third table should link your individual recipes with individual
ingredients, i.e. RECIPE_INGREDIENTS (ri_id - autonumber; ingredient_id
- integer/number, FK to INGREDIENTS table; recipe_id - integer/number,
FK to RECIPES table)

with this (or similar, depending on your db system) structure you will
not have to deal with "FALSE" ingredients


---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com