I have a MySQL question that I think must be quite easy. I need to return the LAST INSERTED ID from table1 when I run the following MySql query:
INSERT INTO table1 (title,userid) VALUES ('test',1);
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(),4,1);
SELECT LAST_INSERT_ID();
As you can understand the current code will just return the LAST INSERT ID of table2 instead of table1, how can I get the id from table1 even if I insert into table2 between?
You could store the last insert id in a variable :
INSERT INTO table1 (title,userid) VALUES ('test', 1);
SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO table2 (parentid,otherid,userid) VALUES (@last_id_in_table1, 4, 1);
Or get the max id from table1 (EDIT: Warning. See note in comments from Rob Starling about possible errors from race conditions when using the max id)
INSERT INTO table1 (title,userid) VALUES ('test', 1);
INSERT INTO table2 (parentid,otherid,userid) VALUES (LAST_INSERT_ID(), 4, 1);
SELECT MAX(id) FROM table1;
(Warning: as Rob Starling points out in the comments)