LAST_INSERT_ID() MySQL

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?

 Answers

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 frm table1

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;   

---

This enables you to insert a row into 2 different tables and creates
 a reference to both tables too.

START TRANSACTION;

 INSERT INTO accounttable(account_username) VALUES('AnAccountName'); 

INSERT INTO profiletable(profile_account_id) 

VALUES ((SELECT account_id FROM accounttable 

WHERE account_username='AnAccountName')); 

SET @profile_id = LAST_INSERT_ID(); 

UPDATE accounttable SET `account_profile_id` = @profile_id; COMMIT;

---

I had the same problem in bash and i'm doing something like this:

mysql -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');"

which works fine:-) But

mysql -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');

set @last_insert_id = LAST_INSERT_ID();" mysql -D "dbname" 

-e "insert into table2 (id_tab1) values (@last_insert_id);"

don't work. Because after the first command, the shell will be logged out
from mysql and logged in again for the second command, and then the
variable @last_insert_id isn't set anymore. My solution is:

lastinsertid=$(mysql -B -N -D "dbname" -e "insert into table1 (myvalue) values ('${foo}');

select LAST_INSERT_ID();") mysql -D "dbname" -e "insert into table2 (id_tab1) 

values (${lastinsertid});"

Maybe someone is searching for a solution an bash :-)

---

Just to add for Rodrigo post, instead of LAST_INSERT_ID() in query
you can use SELECT MAX(id) FROM table1;, but you must use (),

INSERT INTO table1 (title,userid) VALUES ('test', 1) 

INSERT INTO table2 (parentid,otherid,userid)

 VALUES ( (SELECT MAX(id) FROM table1), 4, 1)

---

If you need to have from mysql, after your query, the last auto-incremental id
without another query, put in your code:

mysql_insert_id();