6 Replies Latest reply: Jul 31, 2012 3:25 PM by bregent RSS

    Beginner stumped, possible array solution?

    Scotster1

      Hi there,

       

      I am completely new to using anything other than frontpage for designing webpages so please bear with me

       

      I've been experimenting with dreamweaver all week and it's just phenomonal. Of course through doing this I have gotten a taste of using PHP and Javascript, but I'm completely winging it and googling like a mad-un trying to find answers to, very simple, queries.

       

      Anyway, here is what I am up to now...

       

      I'm creating a very simple website that will host recipes. I have created the databases required etc but I can't fathom out how to dynamically attach a list of ingredients to a recipe.

       

      Eg

       

      Recipe name - WOW

      Description - Home made wine

       

      Ingredients | QTY

       

      Sugar | 1000g

      juice | 2L

      water | 3L

       

      Now for the main database I would like to have 5 columns, Recipe_No, Recipe_name, Recipe_Desc, Recipe_Ing and Recipe_qty.

       

      I just have no idea how to do this. I'm guessing it would be an array? Obviously different recipes would have different amounts of ingredients so the array would need to be made dynamically depending on that.

       

      Even just a nudge in the right direction would be great, I am a complete novice though and have only slightly dabbled with the code side of dreamweaver.

       

      thanks

       

      Scott

       

      (Oh, sorry for the stupid username I got fed up with it refusing the names I was trying so I just typed in any old random key strokes)

        • 1. Re: Beginner stumped, possible array solution?
          bregent CommunityMVP

          You would need at least two tables in your database. One to store the recipe name, number and description, and another to list the ingredients.

           

          And as an amatuer winemaker myself, I must ask that you do not actually publish that recipe

          • 2. Re: Beginner stumped, possible array solution?
            Scotster1 Community Member

            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.

            • 3. Re: Beginner stumped, possible array solution?
              dunnmaks Community Member

              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.

              • 4. Re: Beginner stumped, possible array solution?
                bregent CommunityMVP

                >As you say I currently have 2 tables in my DB (that matter in this instance).

                 

                You do, but as dunnmaks and I have already explained, you will need one that stores the ingredient id's used in each recipe.

                • 5. Re: Beginner stumped, possible array solution?
                  Scotster1 Community Member

                  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.

                   

                  Thanks

                  • 6. Re: Beginner stumped, possible array solution?
                    bregent CommunityMVP

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