Coldfusion MX 7 and MS Access 2002: Recipe database website

If you switch back to the original join here

<!--- note, I omitted the new fields LINK and DIRECTIONS for clarity --->
<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[COLOR="Red"][B]S[/B][/COLOR]_ID = i.INGREDIENT_ID
    WHERE   r.RECIPE_ID IN 
           (
	      <cfqueryparam value="#recipeIDList#" 
                      list="true" 
                      cfsqltype="cf_sql_integer">
           )
</cfquery>

<!--- debug: show query results --->
<cfdump var="#getRecipeDetails#">

<table>
<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
		<td><span class="style5">#getRecipeDetails.RECIPE_NAME#</span>
		</td>
	</tr>
	<cfoutput>
	<tr>
		<td><span class="style5">-#getRecipeDetails.INGREDIENT#</span></td>
	</tr>	
	</cfoutput>
</cfoutput>
</table>

Code (markup):

The debug / query results would look something like this.

query 
  INGREDIENT INGREDIENT_ID RECIPE_ID RECIPE_NAME 
1 BANANA 4 2 BANANA AND PEANUT BUTTER 
2 PEANUT 1 2 BANANA AND PEANUT BUTTER 
3 COCOA 5 4 CHOCOLATE MILK 
4 MILK 6 4 CHOCOLATE MILK 
5 COOKIES 7 3 MILK AND COOKIES 
6 MILK 6 3 MILK AND COOKIES 
7 BREAD 3 1 PEANUT BUTTER & JELLY SANDWICH 
8 JELLY 2 1 PEANUT BUTTER & JELLY SANDWICH 
9 PEANUT 1 1 PEANUT BUTTER & JELLY SANDWICH 

Code (markup):

And the output would look like this. By using "group" the recipe name will print only once.


BANANA AND PEANUT BUTTER
-BANANA
-PEANUT
CHOCOLATE MILK
-COCOA
-MILK
MILK AND COOKIES
-COOKIES
-MILK
PEANUT BUTTER & JELLY SANDWICH
-BREAD
-JELLY
-PEANUT

 

I gotcha... that makes sense, and it works... just need to remove the red S that is in the post.

But, what if I want to include the AMOUNT of each ingredient for each recipe? Would I need to create another table? Seems complicated...

 

I guess my only problem with this is that I want it formatted as such:

RECIPE NAME............INGREDIENT 1............DIRECTIONS
..............................INGREDIENT 2
..............................INGREDIENT 3
..............................INGREDIENT 4

I tried modifying the code like this to make it work:

	  <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><tr><span class="style5">#getRecipeDetails.INGREDIENT#</span></tr></td>
	</cfoutput>
		<td><span class="style5">#getRecipeDetails.DIRECTIONS#</span></td>
	</tr>
</cfoutput>
</table>

Code (markup):

It does not work right though. It lists all the ingredients for each return in a single column, then creates a table with the recipe name and directions after. My logic was that I could create INGREDIENT row outputs withing a <td>... I guess I was wrong

 

Just curious, why that format? If you have a lot of ingredients and few directions, or vice versa, you might end up with large unbalanced areas of whitespace. But enough unsolicited advice and back to your question

Usually what I do is hard-code a <table> example with a border so I can visualize how I need to layout the query results

<table border="1">
<tr><td>RECIPE NAME</td>
	<td>INGREDIENT 1<br>
		INGREDIENT 2<br>
		INGREDIENT 3<br>
		INGREDIENT 4
	</td>
	<td>DIRECTIONS</td>
</tr>
</table>

Code (markup):

Using the example above I can see that need to create 3 <td> (ie cells) for each row, with INGREDIENTS listed in a single cell and a line break after each one.

<table>
<cfoutput query = "getRecipeDetails" group="RECIPE_NAME">
	<tr>
            <td>#getRecipeDetails.RECIPE_NAME#</td>
            <td>
	    <cfoutput>
		#getRecipeDetails.INGREDIENT#<br>
	     </cfoutput>
	     </td>
             <td>#getRecipeDetails.DIRECTIONS#</td>
	</tr>
</cfoutput>
<!--- close the table --->
</table>

Code (markup):

Its not the only way achieve that type of layout. But it should give you something to work with and at least an idea of how to go about producing the desired results.

 

I would create another table, but not for the amount. The new table would store the common units of measurement

TABLE: TBLUNIT
COLUMNS:
UNIT_ID (primary key)
UNIT (ie cup, teaspoon, tablespoon, etc)

Then add (2) additional columns to the recipe ingredients table

TABLE: TBLRECIPEINGREDIENTS
COLUMNS:
QUANTITY
UNIT_ID (FK to UNIT table)

Then modify your query to do a JOIN with the new table and include the QUANTITY and UNIT columns in the select list. This query is 100% untested, but something like this

   SELECT   r.RECIPE_ID, r.RECIPE_NAME, i.INGREDIENT_ID, i.INGREDIENT, ri.QUANTITY, u.UNIT
   FROM    ( (TBLRECIPES AS r 
	       INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID  )
	      INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENTS_ID = i.INGREDIENT_ID  )
	      INNER JOIN TBLUNIT AS u ON ri.UNIT_ID = u.UNIT_ID

    WHERE   r.RECIPE_ID IN 
           (
	      <cfqueryparam value="#recipeIDList#" 
                      list="true" 
                      cfsqltype="cf_sql_integer">
           )
ORDER BY r.RECIPE_NAME

Code (markup):

Having several tables may appear complicated at first. But its simply how well designed databases are structured. Joins between 3 or 4 focused tables are not at all unusual. With proper indexes the queries will usually perform better than if you were querying one or two large tables. Once you're comfortable with JOINs you'll find it quite easy.

http://www.eggheadcafe.com/articles/20050826.asp


Typically you want to store only one "thing" or type of information in a table. Then use FKs or separate tables containing related keys to identify relationships. The decision to use a separate table usually depends on the type of relationship. A good example is how you structured recipe / ingredient tables. a recipe may have many ingredients and an ingredient may be used in many recipes. So recipes have what is called a many-to-many relationship with ingredients. This type of relationship is best represented in a separate table. Which is exactly what you did.

Your original design is a good one. Especially the fact that you used a naming convention for your tables and columns. People often underestimate the importance of naming conventions. They may give PK (primary keys) one name like "RECIPE_ID" yet use a different name like "REC_IP" when its a FK (foreign key). That will certainly work, but it is far less intuitive when writing JOINs. By using the same name everywhere you never have to guess what a column is called.

 

Thanks for the help!!!

I included the following code... Now, I only want 1 recipe's ingredients displayed in this, hence the WHERE...

<cfquery name="recipeingredients" datasource="mydatabase">
  SELECT   r.RECIPE_ID, r.RECIPE_NAME, i.INGREDIENT_ID, i.INGREDIENT, ri.QUANTITY, u.UNIT
   FROM    ( (TBLRECIPES AS r 
	       INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID  )
	      INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENTS_ID = i.INGREDIENT_ID  )
	      INNER JOIN TBLUNIT AS u ON ri.UNIT_ID = u.UNIT_ID

    WHERE   r.RECIPE_ID = 25
</cfquery>



<table width="40%" border="0" cellpadding="0" cellspacing="9">
	<tr>
		<td width="10%" align="left" valign="middle"><div align="left"><span class="style9">Quantity</span></div></td>
		<td width="30%" align="center" valign="middle"><span class="style9">Ingredient</span></td>
	</tr>
<cfoutput>
	<tr>
		<td width="10%" align="left" valign="middle"><div align="left"><span class="style9">#recipeingredients.QUANTITY#</span></div></td>
		<td width="30%" align="center" valign="middle"><span class="style9">#recipeingredients.INGREDIENT#</span></td>
	</tr>
</cfoutput>
  </table>

Code (markup):

I keep getting an error though!!

 

It works good for me. Maybe your column data types are different?

TBLUNIT:
UNIT_ID (autonumber)
UNIT (text)

TABLE: TBLRECIPEINGREDIENTS
QUANTITY - [number ::default style::]
UNIT_ID [number ::default style::]

Also, didn't you say something about needing to remove the "s" from INGREDIENTS_ID ?

 

That's so funny... I JUST figured that out... and tried to correct my post really quick, but you're so fast!

Yes, it is the "S".

I still didn't get results to post, but it may be because I need to populate my database. I think I should be able to take it from here.

THANKS!!!!!!

 

For some weird reason though, only one of my ingredients and quantities show up.

I even placed a <br /> after the #recipeingredients.QUANTITY#, and #recipeingredients.INGREDIENT#

 

You just forgot the "query" part.

<cfoutput query="recipeingredients">
...
</cfoutput>

Code (markup):

If you do that CF only outputs the value in the first record (or an empty string if the query has no records).

<cfoutput>#queryName.columnName#</cfoutput>

Code (markup):

 

wow, duh. Thanks.

It seems there is a full line space between each row of this output. I can't seem to highlight the text and format it... so, I just applied a css tag. It didn't fix it.

Then I tried adding <br> after each (before the </td>... this also didn't do it. I also reduced cell spacing cell padding and border to "0". Still didn't eliminate the spacing between the rows.

I want the results displayed close together (vertically). As if, I hit SHIFT+ENTER for a break rather than just ENTER.

 

I figured it out ---

Seems to only be a problem with my css. I took it out, and modified the text through property pane. It's fine now.

 

An ironic thing i've noticed once I've implemented the QUANTIY and UNIT features into my database and webpage...

Some recipe ingredients may be... "1 pie crust". In this case, the Quantity is "1", the unit is blank, and the ingredient is "pie crust". Now, since I left the UNIT_ID blank for this one (in the TBLRECIPEINGREDIENTS table), this ingredient does not return on my website.

I think that these must be filled. I tried adding a "." in these fields to see if that would make the ingredient list, and it worked. But now, there is a "." displayed.

Any suggestions how to fix this? Is there a way to have a UNIT that is simply "blank" and each time this occurs, I can use this instead?

Thanks.

 

Yes. If there is no UNIT, make the UNIT_ID in the recipe ingredients table NULL. That's the keyword NULL, not the literal word 'NULL'. (You shouldn't be able to use "." because the column type should be "number").

Then change the type of join from INNER to LEFT. An INNER JOIN will only return records where the UNIT_ID in both tables matches. A LEFT join would return all records from the recipe ingredients, even if a matching unit isn't found.

This is not tested, but it should do the trick

SELECT   
       r.RECIPE_ID, r.RECIPE_NAME, i.INGREDIENT_ID, i.INGREDIENT, 
       ri.QUANTITY, u.UNIT
FROM    ( (TBLRECIPES AS r 
	       INNER JOIN TBLRECIPEINGREDIENTS AS ri ON r.RECIPE_ID = ri.RECIPE_ID  )
	      INNER JOIN TBLINGREDIENTS AS i ON ri.INGREDIENTS_ID = i.INGREDIENT_ID  )
	      [B][COLOR="Red"]LEFT [/COLOR][/B]JOIN TBLUNIT AS u ON ri.UNIT_ID = u.UNIT_ID

    WHERE   r.RECIPE_ID = 25

Code (markup):

 

Yeah you're right - What I meant was I created another record in the TBLUNIT that was "."

Anyway, your fix worked. Thank you once again!

 

Well, that could work too. If you wanted you could create a UNIT record with the value "" or NULL. Then use it to represent (no unit).

Glad its working now!