MySQL Forums
Forum List  »  Docs

Re: MySQL Tutorial: LAST_INSERT_ID()
Posted by: Christopher McIntosh
Date: October 18, 2004 08:07PM

Michael:

I encountered a similar issue, until I realized what was happening...

According to the tutorial (p.214, Section 3.6.6 "Using Foreign Keys"), one should perform this pattern of INSERT commands:

(first person - Antonio)
INSERT INTO person VALUES (...);
INSERT INTO shirt VALUES(...), VALUES(...), ... ;

(second person - Lilliana)
INSERT INTO person VALUES (...);
INSERT INTO shirt VALUES(...), VALUES(...), ... ;

When I got the results similar to what you described, I added another step.

Between the 2 INSERT's for each person, I added this:
SELECT @last:=LAST_INSERT_ID();

Next, I substituted @last for each of the LAST_INSERT_ID()'s in the INSERT INTO shirt clauses.

That resolved the problem.

Here's what it looks like...

INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last:=LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last:=LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

(Note: You should drop the tables & re-create them in order to get the table ID's back to zero)

I believe that LAST_INSERT_ID() is being incremented for each of the INSERT INTO shirt clauses.

Hope this helps.

Chris

Options: ReplyQuote


Subject
Views
Written By
Posted
65868
September 14, 2004 07:43AM
23699
October 18, 2004 08:50AM
17911
December 16, 2004 12:15AM
14224
March 08, 2007 10:14PM
Re: MySQL Tutorial: LAST_INSERT_ID()
15724
October 18, 2004 08:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.