CODEWITHSUNDEEP
phpmysqlsqllast-insert-id
Richard
Richard

Reputation: 209

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

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?

Upvotes: 1

Views: 355

Answers (3)

Explosion Pills
Explosion Pills

Reputation: 191779

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.

Upvotes: 1

Sabari
Sabari

Reputation: 6345

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

Upvotes: 1

Waleed Khan
Waleed Khan

Reputation: 11467

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);

Upvotes: 3

Related Questions