1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Coldfusion MX 7 and MS Access 2002: Recipe database website

Discussion in 'Programming' started by lespaul00, Sep 28, 2007.

  1. #1
    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 :rolleyes:). Any help would be appreciated!!!

    - Nick
     
    lespaul00, Sep 28, 2007 IP
  2. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Yes, that looks like a good structure.

    Yes, the first step is to create a form using ColdFusion code. At a basic level, the form will contain checkboxes for each ingredient and a submit button. You can generate the checkboxes dynamically by using a cfquery. The cfquery will contain the ingredients from table 2 (TBLINGREDIENTS)


    
    FORM 
    
    <!--- get the available ingredients (not tested) --->
    <cfquery name="getIngredients" datasource="yourDatasource">
       SELECT ingredient_id, ingredient
       FROM   TBLINGREDIENTS
       ORDER BY ingredient 
    </cfquery>
    
    <form method="post" action="yourActionPage.cfm">
            <!--- display a checkbox next to each ingredient item --->   
    	<cfoutput query="getIngredients">
    		<input type="checkbox" name="ingredient_id" value="#INGREDIENT_ID#">#Ingredient#<br>	
    	</cfoutput>
    	<input type="submit">
    </form>
    
    Code (markup):
    You may notice all of the checkboxes have the same name: "ingredient_id". This is deliberate. When you give form fields the same name, the values will be submitted as a comma-delimited list on the action page. For example

    
    ACTION PAGE  (not tested)
    
    <!--- checkboxes only exists if something was checked --->
    <!--- so I'm defining a default value in case nothing was checked --->
    <cfparam name="form.ingredient_id" default="">
    
    <!--- display the values of the checked boxes --->
    <cfoutput>
    form.ingredient_id = #form.ingredient_id# 
    </cfoutput>
    
    Code (markup):
    The next step is to use form field value in your query.
     
    cfStarlight, Sep 28, 2007 IP
    lespaul00 likes this.
  3. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thank you very much for the response! I tried not replying right away, and try to figure out the next steps on my own. Unfortunately, I got stumped again.

    So I did what you said. The form page seems OK. Here is a shot of what it looks like when I preview it from Dreamweaver:

    Noname.jpg

    Then, when I check "Peanut Butter", "Jelly", and "Banana", I click "Submit". The problem is, I get the following on my Action screen:

    Noname2.jpg

    It only shows the values that I selected (the numbers). I know this is the step in the right direction, because this is the data I want my database to receive. I assume I need this data to go into my database query.

    So, here is a screen shot of my database query (titled Query1):

    Noname3.jpg

    I need to understand what to place in the "criteria" slot for the INGREDIENT (where I placed the ??? for now).

    Questions:
    1. Am I on the correct track?
    2. What do I place in this "criteria" spot to correctly send the user input data from the form page into the database query, to correctly return the recipes on the action page?
    3. Will this achieve what I am trying to do?

    Thanks a bunch!!!

    - Nick
     
    lespaul00, Oct 7, 2007 IP
  4. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    cfStarlight, Oct 8, 2007 IP
  5. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Yes, I have. I was not sure if the syntax he described was supposed to be integrated into my Access database or within Dreamweaver. I assume the former. When I tried it, I kept getting a syntax error with how I used the "FROM" function. Needless to say, I'm a beginner with Access and Coldfusion.

    Which unfortunately brings me back to my my recent post. I guess I just need to understand how to connect the data input to a query in MS Access. Thoughts?

    (Plus, your explanation was easy to follow and fully understandable).

    Thanks,

    Nick
     
    lespaul00, Oct 8, 2007 IP
  6. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #6
    You connect the two by using a <cfquery> on your action page. I don't use Dreamweaver or MS Access's query builder much so I can only tell you what the action page code would probably look like.

    Two reasons why you might have gotten an error when you tried to run the code on the other page

    1. IIRC Access requires parenthesis when you JOIN more than two tables. (This was mentioned on the other thread) Access uses the parenthesis to determine which JOINs should be processed first. If you omit the parenthesis, you'll receive an error

    2. The other code uses what's called a CASE statement. Its like a series of if/else if/else statements. I don't know if Access 2002 supports CASE statements. If it doesn't, Access would throw an error because it doesn't know what CASE means. In this scenario you could use IIF instead.

    This is not tested, but try this on your action page

    
    ACTION PAGE  (not tested)
    
    <!--- checkboxes only exists if something was checked --->
    <!--- so I'm defining a default value in case nothing was checked --->
    <cfparam name="form.ingredient_id" default="0">
    
    <!--- display the values of the checked boxes --->
    <cfoutput>
    form.ingredient_id = #form.ingredient_id# 
    </cfoutput>
    
    <cfquery name="findRecipes" datasource="yourDatasource">
    SELECT   r.recipe_id, r.recipe_name, 
                 count(i.ingredient_id) AS NumOfIngredients
    FROM    [B][COLOR="Red"]([/COLOR][/B]TBLRECIPES AS r 
    	INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.recipe_id = ri.recipe_id
            [B][COLOR="Red"])[/COLOR][/B]
    	INNER JOIN TBLINGREDIENTS AS i ON ri.ingredient_id = i.ingredient_id
    GROUP BY r.recipe_id, r.recipe_name
    HAVING count(i.ingredient_id) =  SUM(IIF(i.ingredient_id IN (#form.ingredient_id#), 1, 0))
    </cfquery>
    
    <cfoutput query="findRecipes">
       found recipe id=#recipe_id# name=#recipe_name#<br>
    </cfoutput>
    
    Code (markup):
    Explanation
    The key part of the query is the HAVING clause. COUNT(i.ingredient_id) will count the total number of ingredients in each recipe.

    
    ... COUNT(i.ingredient_id) 
    
    Code (markup):
    The IIF() statement will check each recipe ingredient. It will return a value of "1" if its one of the selected ingredients, and "0" if it is not.

    
    IIF(i.ingredient_id IN (#form.ingredient_id#), 1, 0)
    
    Code (markup):
    Then SUM() calculates the total number of selected ingredients found.

    
    SUM(IIF(i.ingredient_id IN (#form.ingredient_id#), 1, 0))
    
    Code (markup):
    Finally, the two values are compared and only recipes that contain all of the the selected ingredients are returned.
    
    HAVING count(i.ingredient_id) =  SUM(IIF(i.ingredient_id IN (#form.ingredient_id#), 1, 0))
    
    Code (markup):
    There are a few other issues to consider, but try the code above first.
     
    cfStarlight, Oct 9, 2007 IP
  7. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Thanks for the response.

    The form page works. The ingredients are displayed with checkboxes, and there's a submit button. When clicked, it takes me to the action page, but I get an error. I get the typical http 500, website cannot be displayed. I have CF administrator setup properly. I believe there may be a slight error in the action page code.


    Here are my codes. My database is titled recipedatabase.

    Form page:


    </head>
    
    <body>
    <p>&nbsp;</p>
    <p>
    FORM 
    <cfquery name="getIngredients" datasource="recipedatabase">
    SELECT INGREDIENT_ID, INGREDIENT
    FROM   TBLINGREDIENTS     
    ORDER BY INGREDIENT   
    </cfquery>
    
    <form method="post" action="ttt.cfm">
    	<cfoutput query="getIngredients">
    		<input type="checkbox" name="INGREDIENT_ID" value="#INGREDIENT_ID#" />#Ingredient#<br>
    	</cfoutput>
    <input type="submit">
    </form>  
    </body>
    </html>
    Code (markup):
    My action page is named ttt.cfm. Here is its code:

    </head>
    
    <body>
    
    <cfparam name="form.INGREDIENT_ID" default="0">
    
    <cfoutput>
      <p>form.INGREDIENT_ID = #form.INGREDIENT_ID#</p>
      <p>&nbsp; 
        </p>
    </cfoutput>
    
    <cfquery name="findRecipes" datasource="recipedatabase">
    SELECT   r.RECIPE_ID, r.RECIPE_NAME, 
                 count(i.INGREDIENT_ID) AS NumOfIngredients
    FROM    (TBLRECIPES AS r 
    	INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID
            )
    	INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENT_ID = i.INGREDIENT_ID
    GROUP BY r.RECIPE_ID, r.RECIPE_NAME
    HAVING count(i.INGREDIENT_ID) =  SUM(IIF(i.INGREDIENT_ID IN (#form.INGREDIENT_ID#), 1, 0))
    </cfquery>
    
    <cfoutput query="findRecipes">
       found RECIPE ID=#RECIPE_ID# name=#RECIPE_NAME#<br>
    </cfoutput>
    </body>
    </html>
    Code (markup):
    Any idea why the action page isn't working?:confused:
     
    lespaul00, Oct 18, 2007 IP
  8. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #8
    cfStarlight, Oct 18, 2007 IP
  9. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Thank you for the quick response.

    At the top of the action page, it does have this:

    form.INGREDIENT_ID = 7,6

    The 7 and 6 correspond to the two ingredients I checked off to test the page.

    Then, it is followed by the CF error below:

     
    lespaul00, Oct 18, 2007 IP
  10. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Thank you for the quick response.

    At the top of the action page, it does have this:

    form.INGREDIENT_ID = 7,6

    The 7 and 6 correspond to the two ingredients I checked off to test the page.

    Then, it is followed by the CF error below:

     
    lespaul00, Oct 18, 2007 IP
  11. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #11
    duplicate post
     
    cfStarlight, Oct 20, 2007 IP
  12. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #12
    That suggests a problem with the data type of one of the columns. Is INGREDIENT_ID a "text" (ie. varchar) column by any chance?
     
    cfStarlight, Oct 20, 2007 IP
  13. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    You got it! I simply changed the Ingredient ID from text to number, and it worked.

    It seems like the code works. It returns the Recipe name on the action page.

    I have a couple more questions:

    Form page:
    1. How do I get the ingredients to display in groups? (Right not, all ingredients just list in one column) For instance, maybe I can have headings called "Fruits", "Grains", "Spices", "Dairy", etc... and have all the ingredients associated with these headings, be displayed beneath. I assume I'd have another field in my TBLINGREDIENTS table called "type", where I'd choose one of these headings. But how can I get the form checkboxes to fall under each respective heading?

    2. Can I apply CSS attributes to the form checkbox names?

    Action page:
    1. How do I get the recipes found to be listed? Right now it just says "found ...." and lists the recipes the query found.

    Thanks.
     
    lespaul00, Oct 23, 2007 IP
  14. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #14
    Yes, adding a type column is exactly what you need to do. But I would suggest creating a separate table to store the ingredient types. Then store the "IngredientType_ID" value in your ingredients table.

    IngredientType_ID, Type
    1, Fruits
    2, Grains
    ...


    Next, in your form query, do a JOIN with the ingredienttypes table. (Important - you must ORDER BY the type column first or the cfoutput query below will not work correctly)

    
    untested
    
    <cfquery name="getIngredients" datasource="yourDatasource">
    SELECT it.Type, i.ingredient_id, i.ingredient
    FROM   TBLINGREDIENTS AS i INNER JOIN TBLINGREDIENTTYPES it
    	ON i.IngredientType_Id = it.IngredientType_Id
    ORDER BY it.Type, i.ingredient
    </cfquery>
    
    Code (markup):
    Then inside your form, use cfoutput's GROUP feature to generate the headings

    
    untested
    
    <cfoutput query="getIngredients" group="type">
       <!--- display heading --->   
       <b>#Type#</b><br>
       <cfoutput>
       <!--- display a checkbox for each ingredient item --->   
       <input type="checkbox" name="ingredient_id" value="#INGREDIENT_ID#">#Ingredient#<br>	
       </cfoutput>
       <br>
    </cfoutput>
    
    Code (markup):
     
    cfStarlight, Oct 23, 2007 IP
  15. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #15
    Yes, you can apply CSS styles either to the checkboxes themselves or to the text next to it (ie. ingredient name).
     
    cfStarlight, Oct 23, 2007 IP
  16. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #16
    Thanks again for the prompt response and loads of help.

    Quick question about the code above... What does the i. and the it. mean and do? I don't really understand this portion, and it was also used in one of the earlier posts with the recipes.

    Are the i and the it arbitrary letters used as special variable identifiers? by placing i before .Type, does it make sure it keeps it's database ID along with it?


    Also, I see how this will work with listing ingredients in specific categories. But I think this will still list the specific ingredients in one column under each heading. How do I get them to be displayed in, say, 4 columns... so it doesn't take up so much vertical space?

    Thanks.
     
    lespaul00, Oct 23, 2007 IP
  17. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #17
    Yes, that's essentially it. "i" and "it" are what's called a table alias. Designating "i" as an alias for TBLINGREDIENTS like this

    
    FROM TBLINGREDIENTS AS [b]i[/b] 
    
    Code (markup):
    ... means that I can use the alias whenever I need to refer to a column from TBLINGREDIENTS in my query, instead of typing out the whole table name. So I can just type this:

    
    SELECT [b]i[/b].ingredient_id, [b]i[/b].ingredient
    FROM TBLINGREDIENTS AS i 
    .... 
    
    Code (markup):
    Instead of

    
    SELECT TBLINGREDIENTS.ingredient_id, TBLINGREDIENTS.ingredient
    FROM TBLINGREDIENTS 
    .... 
    [/ode]
    
    But either style is fine.  Its really a matter of personal preference.  So use whichever you feel more comfortable with. 
    
    [quote]
    But I think this will still list the specific ingredients in one column under each heading. How do I get them to be displayed in, say, 4 columns.
    [/quote]
    
    Yes, you're right.  There's definitely a way display the values in columns.  I don't think you can do it with cfoutput and "group" though.  Let me think on it a bit and I'll post something later.
    Code (markup):
     
    cfStarlight, Oct 24, 2007 IP
  18. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #18
    One way is to take the results and create a list of the recipe_id's that were found. Then use that list of ID's in another query to retrieve the details (ie ingredients)

    
    <!--- not tested --->
    <cfif findRecipes.recordCount eq 0>
        Sorry no recipes were found
    <cfelse>
       <cfset recipeIDList = ValueList(findRecipes.Recipe_id)>
    
       <cfquery name="getRecipeDetails" datasource="YourDatasource">
        SELECT   r.RECIPE_ID, r.RECIPE_NAME, i.INGREDIENT_ID, i.INGREDIENT
        FROM    (TBLRECIPES AS r 
    	    INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID
                )
    	    INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENT_ID = i.INGREDIENT_ID
        WHERE   r.RECIPE_ID IN 
               (
    	      <cfqueryparam value="#recipeIDList#" 
                          list="true" 
                          cfsqltype="cf_sql_integer">
               )
    
        <!--- loop through query and display contents here .... --->
    </cfif>
    
    Code (markup):
    Note, I'm assuming the "recipe_id" column type is an autonumber.
     
    cfStarlight, Oct 24, 2007 IP
  19. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #19
    You could display the data in 4 columns, using either <div> tags or <table>

    
    <cfset typeCounter = 0>
    <cfset columnsPerRow = 4>
    <cfset columnWidth = 100 / columnsPerRow>
    
    <table border="1">
    <cfoutput query="yourQuery" group="Type">
    	<cfif typeCounter MOD columnsPerRow EQ 0>
    		<tr valign="top">
    	</cfif>
    	<td><b>#Type#</b><br>
       		<cfoutput><input type="checkbox" name="ingredient_id" value="#ingredient_id#"> #ingredient# <br></cfoutput>
    	</td>	
    	<cfif typeCounter GT 0 AND (typeCounter MOD (columnsPerRow-1)) EQ 0>
    		</tr>
    	</cfif>
    	<cfset typeCounter = typeCounter + 1>
    </cfoutput>
    </tr>
    </table>
    
    Code (markup):
     
    cfStarlight, Oct 27, 2007 IP
  20. lespaul00

    lespaul00 Peon

    Messages:
    283
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #20
    Hello. In response to your posting #18:

    Everything works on the form page. On the action page, (if i check nothing on the form page), it correctly displays "Sorry no recipes were found". If it actually finds recipes (i.e. I check some ingredients on the form page), nothing is displayed. I feel there must be something wroong with the code at the cfelse function down... Any thoughts?

    <body>
    
    <cfparam name="form.INGREDIENT_ID" default="0">
    
    <cfquery name="findRecipes" datasource="recipewebsite">
    SELECT   r.RECIPE_ID, r.RECIPE_NAME, 
                 count(i.INGREDIENT_ID) AS NumOfIngredients
    FROM    (TBLRECIPES AS r 
    	INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID
            )
    	INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENT_ID = i.INGREDIENT_ID
    GROUP BY r.RECIPE_ID, r.RECIPE_NAME
    HAVING count(i.INGREDIENT_ID) =  SUM(IIF(i.INGREDIENT_ID IN (#form.INGREDIENT_ID#), 1, 0))
    </cfquery>
    
    <cfif findRecipes.recordCount eq 0>
        Sorry no recipes were found
    <cfelse>
       <cfset recipeIDList = ValueList(findRecipes.RECIPE_ID)>
    
       <cfquery name="getRecipeDetails" datasource="recipewebsite">
        SELECT   r.RECIPE_ID, r.RECIPE_NAME, i.INGREDIENT_ID, i.INGREDIENT
        FROM    (TBLRECIPES AS r 
    	    INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID
                )
    	    INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENT_ID = i.INGREDIENT_ID
        WHERE   r.RECIPE_ID IN 
               (
    	      <cfqueryparam value="#recipeIDList#" 
                          list="true" 
                          cfsqltype="cf_sql_integer">
               )
    </cfquery>
    
    </cfif>
    </body>
    Code (markup):
     
    lespaul00, Oct 31, 2007 IP