Coldfusion MX 7 and MS Access 2002: Recipe database website

Also, from post #19:

The code works. It places each ingredient category in its own column, with its respective ingredients below. However, I was thinking more of a layout where each category label will be listed on the left portion of the page, and solely the ingredients would be displayed in columns beneath. Also, I'd prefer if the table didn't show.

So...


Category 1:
0 Ingredient 1 .............0 Ingredient 2 ................0 Ingredient 3
0 Ingredient 4 .............0 Ingredient 5 ................0 Ingredient 6
0 Ingredient 7 .............0 Ingredient 8 ................0 Ingredient 9

Category 2:
0 Ingredient 10 ............0 Ingredient 11 ...............0 Ingredient 12
0 Ingredient 13 ............0 Ingredient 14 ...............0 Ingredient 15
0 Ingredient 16 ............0 Ingredient 17 ...............0 Ingredient 18

etc...

Where, "0" are the checkboxes, and the periods "." are simply spaces. No table borders, and 3 ingredients each row. Is it possible?

 

Yes. I didn't actually write any code to display the query. I left it up to you to do that in place of my comment here

<!--- loop through query and display contents here .... --->

 

Yes its possible. Just use <cfoutput> with "group" to show the categories only once (post #14) . Then use MOD to create a new row every 3 columns (post #19).

Important, the cfoutput won't work properly if the query results are not ordered by Type (first) then Ingredient

<!--- border=0 is old style html for no border --->
<!--- border=1 means yes, show a border --->
<table border="0">
<cfoutput query="getIngredients" group="type">
   <tr>
   	 <th colspan="3" align="left">#Type#</th>
   </tr>
   <tr>
   <cfset typeRow = 1>
   <cfoutput>
   	  <td><input type="checkbox" name="ingredient_id" value="#INGREDIENT_ID#">#Ingredient#</td>
   	  <cfif typeRow mod 3 eq 0>
	  	</tr><tr>
	  </cfif>	
	  <cfset typeRow = typeRow + 1 />
   </cfoutput>
   </tr>
</cfoutput>
</table>

Code (markup):

 

Hi Guys,

I have a client of mine in London who are a home grown TV network (one of the biggest in fact) who is looking for a ColdFusion Developer.

This is an excellent opportunity due to the companies reputation, location in London, working environment, and the money on offer. Although this is 'spiel', it doesn't make it any less true.

I'd like to hear from you if you are interested. At the end of the day, what do you have to lose in sending me an email and me telling you all about it. Who knows, your interest in your profession that led you here may lead you toward your next career step...hell, why not !! and what a career step that would be !!

Email me at Jonathan.Kowal@ithr.co.uk.

No, we are not a big recruitment company... and yes I give a shit about professionalism, courtesy, and absolute honesty...hence why I have gained some excellent clients with whom I have grown great relationships with.

 

OK.... so I added the following:

<cfoutput query"getRecipeDetails">
		   	#getRecipeDetails.RECIPE_NAME#<br/>
			</cfoutput>

Code (markup):

The problem is, it displays the recipes names multiple times (If I check the ingredients for Peanut butter and jelly sandwich, it displays that recipe name 3 times).

It seems that it is displaying a recipe as many times as there are number of ingredients in the recipe. I could be wrong.

Is there a simple way to only display one specific recipe only once?

Thanks,

Nick

 

Yes, it will display the recipe name multiple times. To display it only once you use cfoutput's "group" attribute. Its very easy to use and several of my examples so far have used it. I think I first described it here ...

 

Thanks. I did use the group function, and it corrected the problem. However, as I continue to populate my database, i'm seeing it occur again. I am getting duplicate returns of the same recipe. Here is my code for this portion:

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>

Code (markup):

As you see, I group by the recipe name - I thought this should make sure it only displays distinct returns of each recipe name (i.e. a recipe only once). Am I doing something wrong?

 

Assuming your data tables do not contain duplicates, there are two keys to using "group" successfully.

1) Your query has to ORDER BY the same field(s) you're grouping by first. You can include other fields, but the "grouped" fields must be first. Otherwise the cfoutput won't work propery. In your case the order by would be:

ORDER BY RECIPE_NAME

2) You have to use nested <cfoutput> tags.

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
     <!--- 
          put the "grouped" field just inside the first cfoutput tag
          ie the field you do [b]not[/b] want repeated
     --->
     #RECIPE_NAME# <br>

     <!---
            put the ingredients inside another cfoutput tag
      --->
      <cfoutput>
              #INGREDIENTS# <br>
      </cfoutput>

</cfoutput>

Code (markup):

 

OK...so I modified it as such:

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>

       <cfoutput>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
       </cfoutput>
</cfoutput>

Code (markup):

Unfortunately, I am still having problems. There are duplicate recipes, and also, sometimes duplicate DIRECTIONS in certain records. Ideas what may be going wrong?

I guess I will browse through my database to make sure everything is consistent.

 

You need to determine if there really are duplicates in the data, or if its just a problem with the display. If your table really does contain duplicates, you need to remove the duplicates and change your INSERTs to prevent that from happening again. You might also consider creating a "unique index" on certain columns - if its appropriate. Adding a unique index would cause an error to be thrown if you try and insert a duplicate record.

Also, I would have assumed there are only one set of directions per recipe. In which case #directions# should be placed outside the innermost cfoutput tags, so it only displays once. Just like RECIPE_NAME.

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
       </tr>
       <cfoutput>
        <tr>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
        </tr>
       </cfoutput>
        <tr>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>

Code (markup):

 

OK. There are no duplicate RECIPE_NAMES in my database (nor RECIPE_IDs of course). I figure I will paste in the code I have at the moment to troubleshoot:

My form page:

<cfquery name="getIngredients" datasource="recipedatabase">
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>

  <form method="post" action="ttt.cfm">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<cfoutput query="getIngredients" group="type">
   <tr>
   	 <th colspan="3" align="left"><h2>&nbsp;</h2>
   	   <h2><span class="style5">#Type#</span></h2></th>
   </tr>
   <tr>
   <cfset typeRow = 1>
   <cfoutput>
      	  <td width="33%"><h5>
      	    <input type="checkbox" name="ingredient_id" value="#INGREDIENT_ID#" size="50"> 
      	    #Ingredient# </h5></td>
   	  <cfif typeRow mod 3 eq 0>
	  	</tr><tr>
	  </cfif>	
	  <cfset typeRow = typeRow + 1 />
   </cfoutput>
   </tr>
</cfoutput>
</table>
<input type="submit">
  </form>

Code (markup):

My action page (ttt.cfm):

<cfparam name="form.INGREDIENT_ID" default="0">

<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>

<h2><br />
  Your search resuts:   </h2>
<p>
  <cfif findRecipes.recordCount eq 0>
    Sorry no recipes were found
    <cfelse>
      <cfset recipeIDList = ValueList(findRecipes.RECIPE_ID)>
    
       <cfquery name="getRecipeDetails" datasource="recipedatabase">
       SELECT   r.RECIPE_ID, r.RECIPE_NAME, r.DIRECTIONS, r.INGREDIENTS, r.LINK, 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>
	  <table width="90%" border="0.5" cellspacing="9" cellpadding="0">
	<tr>
		<td width="30%"><div align="center"><span class="style6">Recipe name</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Ingredients</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Directions</span></div></td>
	</tr>
<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
	<cfoutput>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
	</cfoutput>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>
</table>
  </cfif>

Code (markup):

Is it evident what may be causing duplicates? For instance, I check off all the ingredients (which should give me all the recipes - only once). I get a list of all the recipes, like, 5 times. Also, some of the DIRECTIONS are duplicated in certain rows (records).

 

Oops. Remember that I mentioned ordering is very important when using "group"? It looks like you forgot to order the results in the "getRecipeDetails" query. If the results aren't ordered correctly - grouping won't work correctly either.

 

Duh... ok... so the following should solve the problem?:

<cfparam name="form.INGREDIENT_ID" default="0">

<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>

<h2><br />
  Your search resuts:   </h2>
<p>
  <cfif findRecipes.recordCount eq 0>
    Sorry no recipes were found
    <cfelse>
      <cfset recipeIDList = ValueList(findRecipes.RECIPE_ID)>
    
       <cfquery name="getRecipeDetails" datasource="recipedatabase">
       SELECT   r.RECIPE_ID, r.RECIPE_NAME, r.DIRECTIONS, r.INGREDIENTS, r.LINK, 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 
[B][COLOR="Red"]ORDER BY r.RECIPE_NAME[/COLOR][/B]
       (
       <cfqueryparam value="#recipeIDList#" 
                      list="true" 
                      cfsqltype="cf_sql_integer">
         )
      </cfquery>
	  <table width="90%" border="0.5" cellspacing="9" cellpadding="0">
	<tr>
		<td width="30%"><div align="center"><span class="style6">Recipe name</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Ingredients</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Directions</span></div></td>
	</tr>
<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
	<cfoutput>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
	</cfoutput>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>
</table>
  </cfif>

Code (markup):

 

Yes, that should work. Assuming there are no duplicates in TBLRECIPEINGREDIENTS.

 

It worked, however, I also had to take out the independent cfoutput tags for the ingredients:

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
	<cfoutput>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
	</cfoutput>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>

Code (markup):

to

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>

		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>

		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>

Code (markup):

 

Why? If you remove the inner cfoutput tags I think the results will be wrong. ie it won't output all of the ingredients. Though maybe my sample data is different than yours ..

Let's say this is the results of the "getRecipeDetails" query: two recipes with two ingredients each.

DIRECTIONS INGREDIENTS LINK RECIPE_NAME
1 direcA ingredient A link A recipe A
2 direcA ingredient B link A recipe A
3 directB ingredient C link B recipe B
4 directB ingredient D link B recipe B

If you remove the cfoutput tags the result will be this. Notice it doesn't show all of the ingredients?

recipe A ingredient A direcA
recipe B ingredient C directB

 

i see what you mean. However, what I did, was I created an INGREDIENTS field in my TBLRECIPES table that includes all the ingredients separated by commas. So, it outputs all of the ingredients from there, and not each one separately.

It may not be the best way to do things, but it works.

I guess my other option is to display the ingredients on the individual recipe pages independently through my TBLRECIPEINGREDIENTS table. ...

Maybe that's what you meant. If so, I think maybe we need to change the INGREDIENTS to INGREDIENT. Because, with INGREDIENTS, it may refer to the field in TBLRECIPES, when we really want a list of INGREDIENT (of each unique INGREDIENT_ID) from TBLINGREDIENTS.

	  
<cfparam name="form.INGREDIENT_ID" default="0">

<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>

<h2><br />
  Your search resuts:   </h2>
<p>
  <cfif findRecipes.recordCount eq 0>
    Sorry no recipes were found
    <cfelse>
      <cfset recipeIDList = ValueList(findRecipes.RECIPE_ID)>
    
       <cfquery name="getRecipeDetails" datasource="recipedatabase">
       SELECT   r.RECIPE_ID, r.RECIPE_NAME, r.DIRECTIONS, [COLOR="Red"]r.INGREDIENTS[/COLOR], r.LINK, i.INGREDIENT_ID,[COLOR="red"] i.INGREDIENT[/COLOR]
       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 
ORDER BY r.RECIPE_NAME
       (
       <cfqueryparam value="#recipeIDList#" 
                      list="true" 
                      cfsqltype="cf_sql_integer">
         )
      </cfquery>

<table width="90%" border="0.5" cellspacing="9" cellpadding="0">
	<tr>
		<td width="30%"><div align="center"><span class="style6">Recipe name</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Ingredients</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Directions</span></div></td>
	</tr>
<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
	<cfoutput>
		<td><span class="style5">#getRecipeDetails.[COLOR="red"]INGREDIENT[/COLOR]#</span></td>
	</cfoutput>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>
</table>

Code (markup):

 

I would strongly recommend against that. Your original structure was a good one. In the long run, storing a list of ids will cause nothing but headaches. It violates the rules of good database design. Its duplication of data, error prone, hard to query and results in poor query performance.

I must be missing something here, because I'm not seeing the problem with the output below Can you explain or post an example/image/link showing what is wrong this output? ie. here is what it looks like .. here is what I want it to look like

<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
       </tr>
       <cfoutput>
        <tr>
		<td><span class="style5">#getRecipeDetails.INGREDIENTS#</span></td>
        </tr>
       </cfoutput>
        <tr>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>

Code (markup):

 

OK, I will try it. However, I need to understand one more thing:

From my first post, I mentioned this is my database structure:

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

Code (markup):

As you can see, I have no INGREDIENTS table in TBLRECIPES... (well, I do, but this is not recommended per your comment):

So, if I take my INGREDIENTS (which is just a long text string of ingredients) out of my TBLRECIPES, how with this code work?

<cfquery name="getRecipeDetails" datasource="recipedatabase">
       SELECT   r.RECIPE_ID, r.RECIPE_NAME, r.DIRECTIONS, [COLOR="red"][B]r.INGREDIENTS[/B][/COLOR], r.LINK, 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 
ORDER BY r.RECIPE_NAME
       (
       <cfqueryparam value="#recipeIDList#" 
                      list="true" 
                      cfsqltype="cf_sql_integer">
         )
      </cfquery>
	  <table width="90%" border="0.5" cellspacing="9" cellpadding="0">
	<tr>
		<td width="30%"><div align="center"><span class="style6">Recipe name</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Ingredients</span></div></td>
		<td width="30%"><div align="center"><span class="style4 style8">Directions</span></div></td>
	</tr>
<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5"><a href="#getRecipeDetails.LINK#">#getRecipeDetails.RECIPE_NAME#</a></span></td>
	<cfoutput>
		<td><span class="style5[COLOR="Red"][B]">#getRecipeDetails.INGREDIENTS#</[/B][/COLOR]span></td>
	</cfoutput>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>
</table>
  </cfif>

Code (markup):

I'll try, but I would think we'd need to pull the ingredients from the TBLINGREDIENTS table.

My thought process:

 

r.INGREDIENTS,

Oooh! That's what was throwing me for a loop Until you mentioned it, I didn't realize "INGREDIENTS" was a column you added to the recipes table. I was assuming it was a typo. I think somewhere along the line you changed the original example, which was more like this

It pulls the ingredient names by using a join with TBLINGREDIENTS . That's the query I thought you were using.

 <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>

Code (markup):