How can I retrieve a last_insert_id from a specific query in a transaction?

Question

I have a mysql transaction with three queries

In the third query I am trying to pull last_insert_id values from the two previous queries.

VALUES ('".$result1[last_id]."','".$result2[last_id]."')";

But it doesn't work. Any suggestions?

When used in transactions, last_insert_id must be called before committing — Federkun, Jul 18 '12 at 17:41
Array keys should be quoted. Unless they're in double quote string context. The quote and concatenation context switching in your example is redundant. — mario, Jul 18 '12 at 17:42
@laxus: no, you can insert in the transaction, and still pull the id - you HAVE to be able to do that. But you can't do multiple inserts and then pull up the id of the n-2 or earlier inserts. — Marc B, Jul 18 '12 at 17:44

score 3 · Accepted Answer · answered Jul 18 '12 at 17:37

3

You should get the last insert ID first, and then inject it into the query. Assuming you're using the mysql_ functions:

mysql_query($query1);
$id1 = mysql_insert_id();

mysql_query($query2);
$id2 = mysql_insert_id();

$query3 = "INSERT INTO ... VALUES (".$id1.", ".$id2.");";
mysql_query($query3);

answered Jul 18 '12 at 17:37

Waleed Khan

11,426
6
39
70

I assumed due to the reckless inline insertion of parameters that it was `mysql_`. – Waleed Khan Jul 18 '12 at 17:42
Yeah, OP is probably using them, but a note to remind that these functions are bad never hurts. – Mahn Jul 18 '12 at 17:42

score 1 · Answer 2 · answered Jul 18 '12 at 17:40

Assuming that you are using MySQL database. You can get id like:

mysql_query($first_query);
$first_result_id = mysql_insert_id();

mysql_query($second_query);
$second_result_id = mysql_insert_id();

$third_query = "INSERT INTO table  (column1, column2) VALUES (".$first_result_id .", ".$second_result_id .");";
mysql_query($third_query);

Hope this helps

score 1 · Answer 3 · answered Jul 18 '12 at 17:42

You can use MySQL to

SELECT LAST_INSERT_ID()

which will retrieve the last insert ID in the given transaction. You can also do this in other DBMS, but you are obviously using MySQL.

Since you are, the MySQL APIs in php have shortcuts for this too:

//mysql_*
$id = mysql_insert_id();

//PDO
$pdo = PDO_OBJECT;
$pdo->query();
$id = $pdo->lastInsertId();

//mysqli
$my = MYSQLI_OBJECT;
$my->query();
$id = $my->insert_id;

NOTE: @HopeIHelped's answer is misleading. There is no race condition as long as you are within the same transaction. LAST_INSERT_ID() retrieves the last ID in the given transactions (and the APIs above do as well), so even if you have this file running a ton of transactions and interlocking queries at once, you can use them safely.

How can I retrieve a last_insert_id from a specific query in a transaction?

3 Answers3