Writing INSERT query for multiple tables

Question

I am writing a recipe database app and I have the database schema sorted but I am not struggling to figure out what query to write to insert the recipe into the database.

I have 3 tables

ingredients ingredients_id...

recipes recipe_id ...

recipes (pivot table) ingredients_id recipe_id

but it was my understanding that you can only insert data into one table at a time.

This is where my problem lies. If I have a form to input a recipe how do I add the recipe which is split over 3 tables? Do i use multiple queries. If so how can I guarantee they will match.

Sorry if this sounds like a dumb question but it is frying my brain, thanks in advance for the feedback.

Best regards

http://stackoverflow.com/questions/10961967/inserting-data-into-multiple-tables-php-mysql?rq=1 — javier_domenech, Jun 29 '14 at 01:23
possible duplicate of [MySQL Insert into multiple tables? (Database normalization?)](http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) — 1' OR 1 --, Jun 29 '14 at 01:36

spencer7593 · Accepted Answer · 2014-06-29T01:50:07.483

You guarantee the rows are related to one another by assigning common values to the appropriate columns.

If you have decided to define the recipe_id column in recipes to be AUTO_INCREMENT, and you insert a row which has a generated value assigned to that column, you can use the LAST_INSERT_ID() function to retrieve the value assigned to the column

INSERT INTO recipes (recipe_id, col2, col3) VALUES (NULL, 'fee', 'fi');

If the insert statement succeeded, you can retrieve the value assigned to recipe_id column (in the same MySQL session, before you execute any other statements), by executing a statement like this:

SELECT LAST_INSERT_ID() AS recipe_id;

NOTE: Some MySQL client libraries have methods/functions that perform this for you, so you can retrieve the value without having to prepare and execute another SQL statement in your code, e.g.

PDO::lastInsertId ref: http://www.php.net/manual/en/pdo.lastinsertid.php

mysqli::$insert_id ref: http://www.php.net/manual/en/mysqli.insert-id.php

You could then supply the value returned in the INSERT to the association table, for example

INSERT INTO recipe_ingredient (recipe_id, ... ) VALUES ( 42, ... )

Thanks this is just what I have been looking for. – jim_at_jibba Jun 29 '14 at 02:18 — jim_at_jibba, Jun 29 '14 at 02:18

Writing INSERT query for multiple tables

1 Answers1