MySQL-Query: Refer to last inserted row from another table (just with one php-SQL String)

Question

I want to put a new row into the database AND refer to this row in a second table. Does anyone have an idea how to do it (using just one php: mysql_query?).

Starting two queries for this purpose is too risky, because the connection can be lost between them.

Idea:

$sql ="INSERT INTO `tbl_humpahumpa`(`humpa_txt`) VALUES ('Ring Ring Ring Bananafon!'); 
INSERT INTO `tbl_reference_to_humpa`(`humpa_ref_id`) VALUES (' ??? referenceid ??? ');";

Using [`mysqli_insert_id()`](http://php.net/manual/en/mysqli.insert-id.php) should help. — Jay Blanchard, Feb 02 '15 at 18:23
possible duplicate of [php/MySQL insert row then get 'id'](http://stackoverflow.com/questions/897356/php-mysql-insert-row-then-get-id) — Machavity, Feb 02 '15 at 18:23

score 0 · Answer 1 · edited Feb 02 '15 at 18:48

0

$sql = "INSERT INTO `tbl_humpahumpa`(`humpa_txt`) VALUES ('Ring Ring Ring Bananafon!');
INSERT INTO `tbl_reference_to_humpa`(`humpa_ref_id`) VALUES (LAST_INSERT_ID());"; // Done

MySQL provides a function called LAST_INSERT_ID() that returns the last auto_increment ID for the current session. http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

edited Feb 02 '15 at 18:48

John Pixel

57
1
8

answered Feb 02 '15 at 18:26

Naomi

389
4
11

kamal pal · Accepted Answer · 2015-02-02T18:42:18.247

mysql_query cannot execute multiple queries at once, You can use SQL Transactions, for example :-

try{
  mysql_query("START TRANSACTION");
  mysql_query("INSERT INTO `tbl_humpahumpa`(`humpa_txt`) VALUES ('Ring Ring Ring Bananafon!')");
  mysql_query("INSERT INTO `tbl_reference_to_humpa`(`humpa_ref_id`) VALUES ('".mysqli_insert_id()."')");
  mysql_query("COMMIT;")
} catch (Exception $e) {
  mysql_query("ROLLBACK;");
}

or mysqli_multi_query

MySQL-Query: Refer to last inserted row from another table (just with one php-SQL String)

2 Answers2