Insert into two table with one transaction with second table using the ID generated from the first

using mysql and pdo is it possible to insert into one table and then insert into the second table inserting the id generated from the first query in one transaction?

INSERT INTO item (creatorId, name) VALUES (:m, "this is  a test);
INSERT INTO details ( creatorId, itemId, term, content ) VALUES ( :m, LAST_INSERT_ID(), "owner", :m );
INSERT INTO colors( creatorId, itemId, colors ) VALUES ( :m, LAST_INSERT_ID(), "green");

Open in new window


How can i refer to the first insert and get that ID?
LVL 6
J NUnicorn wranglerAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SET @var1 := last_insert_id()
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Why dont you  store that value in a variable and reuse it;
0
 
J NUnicorn wranglerAuthor Commented:
Is it possible to create a variable in an sql statement? such as $var = INSERT INTO....?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
J NUnicorn wranglerAuthor Commented:
Would this need to be in a stored procedure?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I always prefer stored procedures over Inline sql statements.  Otherwise it has to be a single dynamic sql statement.
0
 
nociSoftware EngineerCommented:
sorry, not quite the correct answer.

To make it an transaction a COMMIT is needed..... between them, if history is unknown then:

COMMIT; // allprevious outstanding...
INSERT INTO item (creatorId, name) VALUES (:m, "this is  a test);
SET @var1 := last_insert_id()
INSERT INTO details ( creatorId, itemId, term, content ) VALUES ( :m, :var1, "owner", :m );
INSERT INTO colors( creatorId, itemId, colors ) VALUES ( :m, :var1, "green");
COMMIT; // store.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.