Database Construction (PHP and mySQL)?

Question

I'm creating a website where people can browse through recipes and save any recipes that they like to their account.

At the moment, I'm constructing the database for it.

I have a table called recipes that has 5 fields:
* recipe_id
* recipe_name
* recipe_ingredients
* recipe_prep
* recipe_keywords

My question centers around the ingredients field. If I allow that single field to contain all the ingredients of a given recipe

(say, for example:

1 whole banana
2 cups strawberries
1/2 pint blueberries )

how can I then use PHP to display the list of ingredient on the page? Won't it just be a jumble of text (that looks something like:

1 whole banana 2 cups strawberries 1/2 pint blueberries

) rather than the orderly, line-by-line list I had intend? *IS* there a way to store the entire list of ingredients in a single field and still be able to break the list up so that I can control how it's output on the webpage?

Several people have suggested that use a separate ingredients table and inner join them, but I would really prefer to keep the recipes all together, in one place, if I can.

I don't have any experience with joins, and, although I can certainly learn them if I need to, I have so much on my plate with trying to learn PHP and mySQL that I'd like to just keep it as simple as possible.

(Of course, a separate table connected with joins might be the easiest way, in which case, I guess I'd better just suck it up and start studying... :-) )

Answer 1

when you entered

"1 whole banana
2 cups strawberries
1/2 pint blueberries"

into mysql, it will store exactly like this:
"1 whole banana\n2 cups strawberries\n1/2 pint blueberries"
with '\n' is byte code for 'return'.

so, when you embedded this into html code, the browser displayed as

"1 whole banana 2 cups strawberries 1/2 pint blueberries"
as you seen.

You need to replace all "\n" with "
", to do this you simply call this function: nl2br($text);

$newtext = nl2br($ingredient);
echo $newtext;

Hope this help

Answer 2

Recipes are notoriously loosely structured (number of ingredients may vary, there could be separate recipes for the dish and the sauce/side within the same recipe, etc.) So, in my opinion, you should simply store the entire recipe (ingredients and prep combined) as HTML. So your table will look like this:

* recipe_id
* recipe_name
* recipe_body
* recipe_keywords

Inside recipe_body, you will have something like this:

Answer 3

If you really don't want to separated your 'Ingredient' then make a 'blob' field in your msSql.

You can write you recipe 'ingredient' in text format and save it to mySql blob field.

yes.. i think that's the simple way.

the downside of this 'blob' field is : more 'code' you must build to find the ingredient, and the 'code' is beyond the SQL scope.

EDIT :
in my experience :
to find the recipes ingredient in 'blob' field,
- you must open the 'blob' field
- load it to memory (fastest way)
- find the keywords in there.
except mySql provide some add-in function to find some text in text blob fields.

for 500 records, it's not a big problem, but for 5000 records , you will get another big problems.