Multiple Fields to loop into one field (database / php / sql)

I have a database connected to my recipe website for a university project. The aim is to give the user the opportunity to add their own recipe to the database.

The database tables recipe, ingredients and instruction are connected, to tidy up the tables and keep the ingredients and instructions laid out neatly rather than blank bullet points - for example if one recipe had 4 ingredients and another had 10, it saves the first recipe having 6 extra untidy spaces…

Now my problem is how do I add multiple ingredients and instructions to these tables if i only have one field for them and they also need to link back the the recipe table, and the fact the tables are as follows: instruction(id, recipe_id, instructNo, instruction)

I assume I will need some kind of loop, but I’m not sure how to work this. any help is appreciated!!

<ol class="breadcrumb">
<!--Instructions 1-5-->
<b>Type in the instructions:<br></b>

    Instruction 1<br><textarea name="instruction1" id="instruction1" rows="3" cols="114"></textarea><br>
    Instruction 2<br><textarea name="instruction2" id="instruction2" rows="3" cols="114"></textarea><br>
    Instruction 3<br><textarea name="instruction3" id="instruction3" rows="3" cols="114"></textarea><br>
    Instruction 4<br><textarea name="instruction4" id="instruction4" rows="3" cols="114"></textarea><br>
    Instruction 5<br><textarea name="instruction5" id="instruction5" rows="3" cols="114"></textarea><br>
</ol>
<input type="button" name="save" class="btn btn-lg btn-default btn-block" value="Add Meal" onClick="saveRecipe()">

This is then connected to the function saveRecipe ()

    function saveRecipe()
    {
    var xmlhttp;
    xmlhttp= new XMLHttpRequest();
    xmlhttp.open("GET", "insert1.php?&name="+document.getElementById("recipeName").value    
    +"&ingredient1="+document.getElementById("ingredient1").value
    +"&meatQty="+document.getElementById("meatQty").value       
    +"&ingredient2="+document.getElementById("ingredient2").value           
    +"&fruitvegQty="+document.getElementById("fruitvegQty").value
    +"&ingredient3="+document.getElementById("ingredient3").value
    +"&carbsQty="+document.getElementById("carbsQty").value
    +"&instruction1="+document.getElementById("instruction1").value
    +"&instruction2="+document.getElementById("instruction2").value
    +"&instruction3="+document.getElementById("instruction3").value
    +"&instruction4="+document.getElementById("instruction4").value
    +"&instruction5="+document.getElementById("instruction5").value, false);
    xmlhttp.send(null);
    alert("Record insert successfully!"); 
    }

And then the insert1.php is as follows:

    <?php
    $recipeName=$_GET["name"];
    $ingredient1=$_GET["ingredient1"];
    $ingredient2=$_GET["ingredient2"];
    $ingredient3=$_GET["ingredient3"];

    $meatQty=$_GET["meatQty"];
    $fruitvegQty=$_GET["fruitvegQty"];
    $carbsQty=$_GET["carbsQty"];

    $instruction1=$_GET["instruction1"];
    $instruction2=$_GET["instruct2"];
    $instruction3=$_GET["instruct3"];
    $instruction4=$_GET["instruct4"];
    $instruction5=$_GET["instruct5"];


    mysql_connect("localhost","root","");
    mysql_select_db("website");
    mysql_query("INSERT INTO recipe VALUES(NULL, '$recipeName', '$ingredient1', '$meatQty', 
    '$ingredient2', '$fruitvegQty','$ingredient3', '$carbsQty' , '$photo')");      

*** mysql_query("INSERT INTO instruction VALUES(NULL, '$instructions') WHERE recipe.id=instruction.recipe_id"); ?>

I need help to know what is need for this above ***query

The problem One of the problems here is database design. Don’t try to put everything into one table, associative databases hold multiple tables for a reason: so you can associate data between different tables.
So you may have a table for recipes, then another table for ingredients, the ingredients may have a column which contains the ID of the recipe it belongs to, that would be a “one to many” relationship. But if you wanted to reuse the same ingredients in numerous recipes, you would also have a “look up table” containing key pairs for recipe and ingredient IDs, that is a “many to many” relationship.
Then to get this data, your query would contain joins, to join the different table together.
Association tables is a bit of a big subject to cover in one post, and possibly a little confusing for a newcomer, but it is essential knowledge in this case.

And again, seriously stop using mysql functions. If you are new to coding, don’t start by learning obsolete methods, it’s a complete waste of time.

As mentioned in another thread, the first thing you need to do is stop using the old-style mysql calls to connect to the database. I accept you might have been taught this way, so there’s perhaps an issue that if you use the libraries that are still part of the language (mysqli and PDO) you may be marked down, but it seems a pity to learn how to do things in a way that aren’t in the language any more.

As to the actual question, you might find it easier to create the multiple inputs as an array of inputs rather than giving them separate names. That would make it slightly nicer to iterate through the array, check if there is something in the text, and if so, write it into the database.

But you can just do something like

for ($x = 1; $x <6; $x++) { 
  if (isset(${"instruction$x"})) { 
    $text = ${"instruction$x"};
    // now write $text into the instruction table
    }
  }

Your instruction table needs to have its own unique id, and a further column that contains the id of the recipe. Then when you retrieve a recipe, you can JOIN all instructions that also have that recipe id, whether there is one or fifty.

I’m also a bit concerned about passing that amount of data using $_GET, and I’d imagine you’d need at the very least to encode it so that as soon as the first user types “apples & bananas” it doesn’t all go wrong.

ETA - as @SamA74 said, the database design is a little strange. If you have a one-to-many relationship anywhere, then the “many” should be in a separate table with a suitable link. Like you seem to have done for the “instructions”, you should do the same for ingredients. If you merge more than one value into a single table column, think about how you would find every recipe that includes “pepper”. Have a read up on “Database Normalisation” for more information than you will ever want.

Whether you migrate over to the MySQLi (note the “i” in there) extension or PDO, you should use prepared statements. As your code stands you’re a sitting duck for SQL Injection attacks. You should look at adding some validation of the user submitted data (NEVER trust any user submitted data no matter how much you might trust the user!!!)

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.