asked on

Help with MySQL last_Insert_id() on multiple insert

Hi

I have a multiquery statement like the example below where the first query inserts a record.

The second query then adds a relationship to another table using the last_insert_id() function.

The third query I wish to also put the last_insert_id() value from the first query but it's obviously taking the id of the second query.

is there any way to save or reuse the last_insert_id() from the first query? (for use in the 3rd query)

INSERT INTO tbl_contacts (name)
VALUES ("Shaun Perry");
INSERT INTO lnk_contacts (contactId)
VALUES (LAST_INSERT_ID());
INSERT INTO lnk_projects (projectId, contactId)
VALUES (1, LAST_INSERT_ID());

Kind regards

Shaun

theGhost_k8

You may store it in a variable and use it!

john-formby

I guess you are using something like PHP to populate?

You can do like theGhost_k8 said and store as a variable:

$lastinsert = mysql_insert_id;

Then use $lastinsert in your queries.

Hope this helps,

John

wiredmonkey

ASKER

Hi

I managed to work this out myself using MySQL temporary variables:

INSERT INTO tbl_contacts (name)
VALUES ("Shaun Perry");

SELECT @myname:=LAST_INSERT_ID();

INSERT INTO lnk_contacts (contactId)
VALUES (@myname);

INSERT INTO lnk_projects (projectId, contactId)
VALUES (1, @myname);

Thanks

Shaun

ASKER CERTIFIED SOLUTION

ee_auto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial