Coldfusion MX 7 and MS Access 2002: Recipe database website

[lespaul00]

Hello,

I have been struggling for awhile now to get my website up and running. Here is some background:

Website development: Dreamweaver 8
Dynamic Programming language: ColdFusion MX 7
Database software: MS Access 2002
Type of website: Recipes returned from database input (by user)

Goal: To have website visitors use checkboxes (or radio buttons) to select all the ingredients he or she has available to them. Then, they will hit "submit" and all recipes that include these ingredients will be returned. BUT, if the user is missing 1 or more ingredients from a recipe, the recipe will not be returned.


Example:
Say I have bread, peanut butter, and a banana lying around the house. I go to the website, check off "bread", "peanut butter", and "banana" on the webpage, and click "submit". A page is created with the results:

Recipe name: Recipe Ingredients:
Banana with Peanut Butter One banana, 2oz. peanut butter

What DOES NOT show up is a "Peanut butter and jelly sandwich" because the "jelly" checkbox was not checked, and thus not satisfied (would need bread, peanut butter, and jelly all to be checked off). The user must have all ingredients for a recipe to be returned.

Progress so far:
1. Website testing server set up, and coldfusion working.
2. Can return a table from database without criteria (i.e. the connection between the website and the database with coldfusion is working).
3. My MS Access 2002 database is as follows:

Table 1: TBLRECIPES

RECIPE_ID RECIPE_NAME
1 PEANUT BUTTER & JELLY SANDWICH
2 BANANA AND PEANUT BUTTER
3 MILK AND COOKIES
4 CHOCOLATE MILK


Table 2: TBLINGREDIENTS

INGREDIENT_ID INGREDIENT
1 PEANUT BUTTER
2 JELLY
3 BREAD
4 BANANA
5 COCOA
6 MILK
7 COOKIES


Table 3: TBLRECIPEINGREDIENTS

RECIPE_ID INGREDIENTS_ID
1 1
1 2
1 3
2 4
2 1
3 6
3 7
4 5
4 6

Query 1:
RECIPE_ID linked between tables 1 and 3.
INGREDIENTS_ID linked between tables 2 and 3.

Displayed in query is RECIPE_NAME.

Criteria for query: INGREDIENT. This must be linked to the user input criteria from the webpage. (don't know how to do this)

Next steps, and what I need to know:

1. Is the above database correct for what I'm trying to do? If not, how should it be modified?
2. How do I get a user's checkbox "check" become a criteria input for the database (i.e. an ingredient)? (I assume coldfusion code?)


I'm sure there may be a few other questions, but I feel this is the best starting point. Like I said, it's been bugging me awhile (yes I am a beginner ). Any help would be appreciated!!!

- Nick

 

[philhege]

Your ingredient checklist should have the same name for each ingredient:

<input type="checkbox" name="ingredient" value="2">Jelly
etc.

This has the effect of placing each selected item in a comma-delimited list in the form variable. So selecting our ingredients on-hand gives 1,3,4.

So you can use this in a query:

SELECT recipe
FROM tblRecipe r
JOIN tblRecipeIngredients ri
ON ri.recipe_ID = r.recipe_ID
JOIN tblIngredients i
ON i.ingredient_ID = ri.ingredientID
WHERE i.ingredient_ID IN (#form.ingredient#)

So far, we'll get every recipe that has any of the selected ingredients. But we really need only those recipes that can be made with the ingredients at hand. So let's eliminate recipes that have more than the selected items:

(My apologies that this syntax is Transact-SQL, not Access. You should be able to play with Access functions to get the same result. I also loathe the Access JOIN syntax, so I don't attempt to duplicate it here.)

SELECT recipe_name

FROM tblRecipe r
JOIN tblRecipeIngredients ri
ON ri.recipe_ID = r.recipe_ID
JOIN tblIngredients i
ON i.ingredient_ID = ri.ingredient_ID

GROUP by recipe_name , r.recipe_id
HAVING count(r.recipe_ID) = SUM(CASE WHEN i.ingredient_ID IN(#form.ingredients#) THEN 1 else 0 end)

This has the effect of matching records with exactly what we have, and nothing more.


HTH,