Hello there and in advance, thanks for the time.
I have set up a recipe database: Recipe Database ER Diagram. Consisting of 4 tables;
recipes (recipe_id, recipe_name, recipe_description, recipe_time)
ingredients (ingredient_id, ingredient_name)
recipe_ingredients (recipe_id, ingredient_id, amount)
instructions (recipe_id, steps, step_description)
I would the like to, through a website, let my users add recipes to the recipes table with ingredients to the ingredients table. So that new ingredients, not previously added to the database, will be added and those that are already added won't be (this should already not be a problem).
The problem that I am having is to link these ingredients (and the instructions, for that matter), both new and old, to the recipe that my user is adding, in the recipe_ingredients table.
My code so far is (html):
<form action="insert.php" method="post">
<p>
<label for="RecipeName">Recipe Name</label>
<input type="text" name="recipename" id="recipeName">
</p>
<p>
<label for="IngredientName">Ingredient Name</label>
<input type="text" name="ingredientname" id="ingredientName">
</p>
<input type="submit" value="Add Records">
And my php:
$sql = "INSERT INTO ingredients (ingredient_name) VALUES ('$ingredient_name');";
$sql .= "INSERT INTO recipes (recipe_name) VALUES ('$recipe_name')";
if ($link->multi_query($sql) === TRUE) {
echo "New records created successfully";}
else {
echo "Error: " . $sql . "<br>" . $link->error;
}
Please ask any question, I welcome any help I can get.
I don't know if I understood all your problem and also don't know if SO is the best place to solve it, but here are some tips that I hope can help you to find a solution:
Recipe to add a new Recipe:
First, Add the recipe (new or modified)
Second, Add the ingredients, in turn, to the new recipe.
Details:
First of all, based on the provided model, put a amount field in your form:
<form action="insert.php" method="post">
<p>
<label for="RecipeName">Recipe Name</label>
<input type="text" name="recipename" id="recipeName">
</p>
<p>
<label for="IngredientName">Ingredient Name</label>
<input type="text" name="ingredientname" id="ingredientName">
</p>
<p>
<label for="amountOf">Amount</label>
<input type="number" min="0" max="99" name="amount" id="amountOf">
</p>
<input type="submit" value="Add Records">
After submission, you'll need a first part to looking for repeated recipes by the name and define if it is a new recipe or a existent recipe. A query like SELECT FROM recipes WHERE recipe_name LIKE '%$recipename%' could solve this problem (I'm considering that the name of your recipes are unique. The best approach is get the recipe ID on your form, not the name).
Now, if it's a new recipe, you can do (assuming that you are using Mysqli):
$sql = "INSERT INTO recipes (recipe_name) VALUES ('$recipe_name');"
$sql. = "INSERT INTO ingredients (ingredient_name) VALUES
('$ingredient_name')";
if ($link->multi_query($sql) === TRUE) {
$recipeId = $link->insert_id;
$link->next_result();
$ingredientId = $link->insert_id;
$sql2 = "INSERT INTO recipe_ingredients(recipe_id,ingredient_id,amount) VALUES ($recipeId,$ingredientId,$amount);"
if ($link->query($sql2) == TRUE){
echo "New records created successfully";}
}
else {
echo "Error: " . $sql2 . "<br>" . $link->error;
}
}
else {
echo "Error: " . $sql . "<br>" . $link->error;
}
If the recipe is not new, you can try:
$sql = "INSERT INTO ingredients (ingredient_name) VALUES
('$ingredient_name')";
if ($link->query($sql) === TRUE) {
//retrive your $recipeId from the SELECT query
$ingredientId = $link->insert_id;
$sql2 = "INSERT INTO recipe_ingredients(recipe_id,ingredient_id,amount) VALUES ($recipeId,$ingredientId,$amount);"
if ($link->query($sql2) == TRUE){
echo "New records created successfully";}
}
else {
echo "Error: " . $sql2 . "<br>" . $link->error;
}
else {
echo "Error: " . $sql . "<br>" . $link->error;
}
These codes are just examples, you'll need to deal with possible errors and exceptions. Take a look at try/catch/finally blocks to handle Exceptions.
Here is a SO question about the use of mysqli->next_result() to help you with different ids that you'll need retrieve.
UPDATE
As asked in your comment and following your model, to insert various ingredients to the same recipe, you'll need to split your form in two different forms, one to insert recipes and another to insert ingredients in a specific (and pre-existent) recipe.
Then, repeat the queries:
INSERT INTO ingredients (ingredient_name) VALUES ('$ingredient_name')
and
INSERT INTO recipe_ingredients(recipe_id, ingredient_id, amount)
VALUES ($recipeId, $ingredientId, $amount)
for each ingredient inserted.
Just a advice, if I can. Apparently, you're having some troubles to understand your model and how it works in a real relational database.
Maybe some tutorials about this subject would be a good thing to you.
Try some:
I don't know any about these tutorials (just google it right now), but it would be a good start to a better understanding of how your database works and what queries you'll need to each task.