Inserting data into multiple tables PHP MySQL

Question

1.40M viewsSeptember 10, 2022dataset mysql php

3

Ronnie September 7, 2022 0 Comments

I have a basic data structure for storing recipes. It consists of three tables as below:

Table 1 – Recipes (recipe_id, recipe_name)

Table 2 – Ingredients (ingredient_id, ingredient_name)

Table 3 – Recipe_Ingredients (recipe_id, ingredient_id)

I have come across a problem when adding a new recipe and would like to know the best practice for inserting.

Currently, on submitting the form I insert into the Recipes table, the recipe_id is auto generated. I then insert into the Ingredients table, again the ingredient_id is auto generated. The third step on submit is to then insert into the Recipe_Ingredients table, but how do I get the values of the recipe and ingredient ids that have just been created in order to insert them into the Recipe_Ingredients table?

I currently have separate PHP functions to insert into the Recipes and Ingredients tables.

3 Answers

score 0 · Answer 1 · 2022-09-08T05:41:18+00:00

mysql_insert_id() will give you the auto generated id of the rows you just inserted

score 0 · Answer 2 · 2022-09-08T02:15:09+00:00

After each insert, do mysql_insert_id() I think, so it gets back the inserted id of the previous insert.

score 1 · Answer 3 · 2022-09-08T01:57:40+00:00

You have to get the inserted id value after each insert operation.

The function to get the last insert id is: mysql_insert_id()