Get last id from multiple insert

Question

I have this multiple record insert. I need to get last id from product and loan table insert to table import. How can I doing that?

This is my code:

$sql = "INSERT INTO `product` (`product_id`, `barcode`, `delete`,   `id_list_name`) VALUES (NULL, '".$barcode."', '0', '".$listnameid."');
        INSERT INTO `loan` (`loan_id`, `date`, `Quantity`, `delete`, `id_user`) VALUES (NULL, '".date("Y-m-d")."', '1', '0', '".$userid."');
        INSERT INTO `import` (`import_id`, `id_product`, `date`, `delete`, `date delete`, `id_user`, `id_loan`) VALUES (NULL, '4', '".date("Y-m-d")."', '0', '', '".$userid."', '"4"');
       ";
        mysqli_query($conn, $sql);

For one thing, only the last INSERT is going to execute since you didn't use a multi query. — Funk Forty Niner, Dec 12 '19 at 14:32
the '"4"'); is just manual typing I need to replace it with last id from loan table — Ab Le, Dec 12 '19 at 14:37
Does this answer your question? [How to get last inserted id from table MySQL](https://stackoverflow.com/questions/20891875/how-to-get-last-inserted-id-from-table-mysql) — Kamran Allana, Dec 12 '19 at 14:39
You can use PDO to handle easily with columns or if you has some criteria to generate your inserts you can order by date (desc) and query for last Id inserted at database. — Leandro Arruda, Dec 12 '19 at 14:42
Do all of your tables contain an auto_increment'ed column? That will be required in order to make this work. — Funk Forty Niner, Dec 12 '19 at 15:15

score 2 · Accepted Answer · edited Dec 12 '19 at 14:53

There is no such thing as a "multiple insert". Each insert have to be performed separately. Especially if you need an insert id from each query.
All insert queries must be executed using prepared statements.

Here is your code written properly

$sql = "INSERT INTO `product` (`product_id`, `barcode`, `delete`,   `id_list_name`) VALUES (NULL, ?, '0', ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $barcode,$listnameid);
$product_id = $conn->insert_id;

$sql = "INSERT INTO `loan` (`loan_id`, `date`, `Quantity`, `delete`, `id_user`) VALUES (NULL, CURDATE(), '1', '0', ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $userid);
$loan_id = $conn->insert_id;

$sql = "INSERT INTO `import` (`import_id`, `id_product`, `date`, `delete`, `date delete`, `id_user`, `id_loan`) VALUES (NULL, ?, CURDATE(), '0', '', ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("sss", $product_id, $userid, $loan_id;);
$import_id = $conn->insert_id;

It should be noted that in order for [`last_insert_id()`](https://www.php.net/manual/en/mysqli.insert-id.php) to work, that each of the tables must contain an auto_increment'ed column. — Funk Forty Niner, Dec 12 '19 at 15:13

Get last id from multiple insert

1 Answers1