Lol no worries about the recipe
As you say I currently have 2 tables in my DB (that matter in this instance).
The ingredient table looks like this..
ingr_no, ingr_name, yield, fermentability, ingr_cost, per_qty, ingr_unit
It's populated with only a few items to get me started.
The recipe table looks like this...
recipe_no, recipe_name, recipe_desc, recipe_ingr, ingr_qty, recipe_proc... etc
Everything is very simple to populate other than the "recipe_ingr" (and ingr_qty but that will be exactly the same) as obviously each recipe will have a number of ingredients. My problem is that I have no idea how to create an array dynamically based on a table of unknown quantity. My thinking is that all I really need to store in the "recipe_ingr" is the "ingr_no" of each item, as all the other information will be populated from it via the ingredients table.
I'm looking to use a dropdown to select the ingredient. When it's selected it's automatically added to the "recipe_ingr" array and the recordset will then update on the screen showing this. But I'm not sure what to write in order to have this action happen.
I'm possibly in over my head here as I'm even finding it hard to describe what I'm after lol.
You should have 3 tables: recipes, ingredients, recipes_ingredients
recipes should hold common things like id, name, description, etc
ingredients should hold common things like id, ingredient, cost, etc
recipes_ingredients would hold things like id, recipe_id, ingredient_id, qty
You would then query the database and get all of the r_i and loop over that result set to display.
I've had a little bit of a breakthrough just by fumbling along.
I'm still only using the 2 tables. I don't see any real reason to use a third at all. I would only be populating it with the same information over and over. For example if I had 3 ingredients with 20 recipes the 3rd table would have 60 entries. Seems like overkill to me.
Anyway, my solution will be along the following....
When a new recipe is created a variable array is made and the record is saved.
When an ingredient is added the array is adjusted to include that ingredient and the record is again saved, then displayed.
I'm simply using a loop to display all the ingredients for each recipe, the loop displays all of the array values via "foreach".
The above is working for me at the moment using the implode and explode delimiters.
Anyway, I'm sure I'll be able to fumble my way through it now.
>or example if I had 3 ingredients with 20 recipes the
>3rd table would have 60 entries. Seems like overkill to me.
This is not overkill. This is the standard way to model the data. The 3rd table only needs to contain a few columns to identify, such as:
1) The recipe_id
2) The ingredient_id
3) quantity used
Don't worry about how many rows you are using. DBMS's are built to deal with extremely large amounts of data.
>When a new recipe is created a variable array is made and the record is saved.
When you store more than one piece of data into a single column, you are violating one of the most basic rules of data normalization.
>The above is working for me at the moment using the implode and explode delimiters.
Yes, you can get it to work but it's not a good practice and will make writing basic queries a big pain. Plus, how would you store recipe specific ingredient data, like quanity used?