Recipes Database Dilemna

Jewelie

I am making a site with a recipe section. I am attempting to store the recipes in a MySql database and displaying it on my site via php code.

My dilemna is that I can't figure out how to get the img to display and how to get the ingredients to be listed correctly.

Here is my planned database structure:

Recipes Table:

recipe_id
recipe_title
recipe_img
recipe_ingredients
recipe_instructions
recipe_category

The problem with this is that it is not normalized because the ingredients field will have multiple values. I am guessing I need to make a separate table for ingredients, but am not sure what the structure would be.

Maybe:

Ingredients Table:
recipe_id
ingredient_description

The problem with this is that each recipe will have varying measures.

Hope this makes sense. I am just lost and would appreciate anyone would can help me get to the next step.

Also, how do you store an image in a database? Do you just store the name? then how do you display it with php.

onion2k

First part - Tables:

Theres two ways to store the ingredients, either as a string or in a seperate table. If you store them as a string you could implode() them to put them in, and explode() them into an array for display later. Thats the easy way to do it, but it limits what you can do later.

Using a seperate table is more difficult, but not really too hard. Just make a new table and have a key that matches the receipe id.

Table structure (example):

ingredient_id
receipe_id
ingredient_name

So the receipe_id of the ingredient matchs the receipe_id of the receipe in the receipes table. To select the receipe and its ingredients:

select * from receipe r left join ingredient i on i.receipe_id = r.receipe_id

Second Part - Images:

I would just store the filename in the database, and when you want to use the image use:

echo "<img src=\"".$filename."\">";

Jewelie

Thank you for replying so quickly. I've taken your advice and tried to make my page work, but I am still having some problems fitting your code into mine correctly.

Here is my revised MySQL table structure:

recipes:
recipe_id
recipe_name
recipe_instructions
recipe_image

ingredients:
ingredient_id
recipe_id
ingredient_name

Now here is an abbreviated real life example:

recipes:
1
Cool as a Cucumber Soup
Blend well.
soup_cuke.jpg

ingredients:
1
1
2-3 large cucumbers

2
1
1 c tomatoes

3
1
1 c lemon juice

4
1
1/2 c onion

5
1
1 t sea salt

Now here is an example of my code to show this recipe:

<?php

// Request the text of all the recipes
$result = @('SELECT recipe_name, recipe_instructions, recipe_image FROM recipes');
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

while ($row = mysql_fetch_array($result)) {
$recipe_name=$row['recipe_name'];
$recipe_ingredients=$row['recipe_ingredients'];
$recipe_instructions=$row['recipe_instructions'];
$recipe_image=$row['recipe_image'];
$ingredients_name=$row['ingredients_name'];

echo_
?>
<div class="recipe">
<h1 class="title"> <?php echo "$recipe_name"; ?></h1>
<div align=center><img src="images/r/ <?php echo "$recipe_image" ?>"></div>
<div class="instr">
<div class="ing">
<h1>Ingredients</h1>
<p> <?php echo "$recipe_ingredients" ?> </p>
</div>
<h1 class="ingredients">Instructions</h1>
<p> <?php echo "$recipe_instructions" ?> </p>
<div style="clear:both"></div>
</div>
</div>
<?php ;

}

Now in your reply you said to use the following to select the ingredients

select * from recipe r left join ingredient i on i.recipe_id=r.recipe_id

How do I add this to my SELECT command? This is how I've been doing it, but it is not working:

$result = @('SELECT recipe_name, recipe_instructions, recipe_image, ingredients_name FROM recipes, ingedients, recipe r left join ingredient i on i.recipe_id=r.recipe_id');

Where did the i and r come from and what do they represent? I am guessing they represent the table names, but then shouldn't it have been:

SELECT * FROM recipe_id.recipes LEFT JOIN recipe_id.ingredients ON ingredients.recipe_id=recipes_recipe.id

Then I am left with the puzzle of fitting it into my echo statement. This is my proposed new structure:

echo_
?>
<div class="recipe">
<h1 class="title"> <?php echo "$recipe_name"; ?></h1>
<div align=center><img src="images/r/ <? php echo "$recipe_image" ?>"></div>
<div class="instr">
<div class="ing">
<h1>Ingredients</h1>
<p> <?php echo "$ingredients_name" ?> </p>
</div>
<h1 class="ingredients">Instructions</h1>
<p> <?php echo "$recipe_instructions" ?> </p>
<div style="clear:both"></div>
</div>
</div>
<?php ;

}

I feel like I am almost there, I am just not grasping a major concept. If it is blatantly obvious to you what I am doing wrong, could you let me know? Thanks.

onion2k

SELECT recipe_name, recipe_instructions, recipe_image, ingredient_name FROM recipes, ingedients, recipe r left join ingredient i on i.recipe_id=r.recipe_id

Quite close.. but not quite there..

SELECT recipe_name, recipe_instructions, recipe_image, ingredient_name FROM recipes r left join ingedients i on i.recipe_id=r.recipe_id

i and r are mysql shortcuts. When you write a select statement you can use a shortcut writing out the whole table name by putting a space and then whatever you want to use as the shortcut. So ingredients i makes i a shorter way of writing "ingredients". I'm awfully lazy and tend to use shorthand methods whenever I can. If you didn't use i and r you'd need to write the table name instead so that mysql knows which table you're refering to when you have a column in both that has the same name, eg:

SELECT recipe_name, recipe_instructions, recipe_image, ingredient_name FROM recipes left join ingedients on ingedients.recipe_id=recipes.recipe_id

Once you've got your SQL sorted just use the results in the same way as any other results.. there'll be one ingredient per row in the results.

... ... ...

All that said mind you, since you're echo'ing the ingredients as a list after the main receipe details I'd just use 2 select querys coz that'd be simpler. So you'd get your receipe with:

SELECT recipe_name, recipe_instructions, recipe_image FROM recipes where recipe_id = '$id'

And then, later in the script, to get the ingredients you'd use:

SELECT ingredient_name FROM ingredients where recipe_id = '$id'

Jewelie

Thanks so much for helping me, first of all.

Secondly, I think I almost understand. 😉

All that said mind you, since you're echo'ing the ingredients as a list after the main receipe details I'd just use 2 select querys coz that'd be simpler. So you'd get your receipe with:

SELECT recipe_name, recipe_instructions, recipe_image FROM recipes where recipe_id = '$id'

And then, later in the script, to get the ingredients you'd use:

SELECT ingredient_name FROM ingredients where recipe_id = '$id'

Where did $id come from? would you have to state somewhere that '$id'=recipe_id? I'm still confused on that.

As if you can't tell, I am a beginner and I'm trying to learn by piecing together bits of information from all over the web, so I think my conceptual understanding of this is skewed. Practice makes perfect though and I intend on learning this.

onion2k

$id would be the id of the recipe the user is looking at. Unless you're going to make them view all the recipes at once? That'd be quite annoying (I reckon. Depends on the site I guess).

As for being a beginner, we all were once...

Jewelie

No, I'm not going to view them all at once. Rather, I am planning on sorting them by categories and viewing all in a category. I am still working on this. I cannot get everything to work all at once. Here is my code:

$query = "SELECT * FROM recipes r LEFT JOIN ingredients i ON i.recipe_id = r.recipe_id ORDER BY recipe_name ASC ";

$result = @ ($query); // Run the query

while ($row = mysql_fetch_array ($result, MYSQL_ASSOC) ) {
echo ' <h1 class="title"><a name="#">' . $row['recipe_name'] . '</a></h1>' ;
echo' <h1 class="ingredients">Ingredients</h1> '
echo 'p' . $row['ingredient_name'] . 'p';

}

echo '<h1 class="ingredients">Instructions</h1> ' .
'<p>' . $row['recipe_instructions'] . '</p>'
;
}

I know that there are major errors in this, because it produces a blank page. Any advice?

Here is the html template for a recipe listing :

<div class="recipe">
<div class="instr">
<h1 class="title"><a name="#">Title</a></h1>
<div align=center><img src="#"></div>
<div class="ing">
<h1>Ingredients</h1> 
<p></p>
<p></p>

</div>
<h1 class="ingredients">Instructions</h1> 
<p></p>
<div align=center>
<a name="appetizerindex">back to appetizer index</a>
</div>
<div style="clear:both"></div>
</div>

Is it better to echo something like this using single quotes and then . the strings together, like in the above code? Or is is better to use double quotes and just backslash all of the quotation marks. I've seen it done both ways, but can't seem to get either to work.