Geek asks and answers

MySQLi insert_id returns 0 after transaction

I have the following codes:

<?php
    header('Content-Type: text/plain');
    $db = new mysqli('localhost', 'username', 'password', 'my_schema');
    $db->autocommit(false); // start transaction
    $sql = "INSERT INTO my_table (col1, col2) VALUES (1, 2)";
    $db->query($sql);
    // $last_id = $db->insert_id;
    // echo 'Last ID before commit: ' . $last_id . PHP_EOL;
    $db->commit();
    $db->autocommit(true); // end transaction
    $last_id = $db->insert_id;
    echo 'Last ID after commit: ' . $last_id . PHP_EOL;
    $db->close();
?>

The output of the above code:

Last ID before commit: 1 <-- if uncomment the 2 lines above, it will show this
Last ID after commit: 0

insert_iddoes not work outside autocommit(returns 0), even if it is correctly committed and inserted into the database. Is behavior expected?

+3
source share
2 answers

A few things about your code:

  • The transaction ends when the transaction is completed. After you have done this, you can: create a new transaction, set autocommit to true in order to restore the default behavior on it or close the connection.
  • insert_id . , , , , , NEED .
+1

,

$last_id = mysql_insert_id();

-3

More articles:


All Articles