[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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
0
wiredmonkey
Asked:
wiredmonkey
1 Solution
 
K VDatabase ConsultantCommented:
You may store it in a variable and use it!
0
 
john-formbyCommented:
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
0
 
wiredmonkeyAuthor Commented:
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
0
 
ee_autoCommented:
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now