php Question posted in
Official manual can be found here.

Php – Why there is a difference between PDO::lastInsertId and Mysql's LAST_INSERT_ID() behavior?

My current development environment is PHP 7.2.
This is MariaDB 10.3.11.

When using PDO’s lastInsertId function, if you run another query after the insert query, the lastInsertId result value will always be 0.

example table is

create table test
(
    id    int unsigned auto_increment comment 'PK' primary key,
    title varchar(128) charset utf8mb4 not null comment 'title'
)
comment 'test';

create table test2
(
    id    int unsigned auto_increment comment 'PK' primary key,
    title varchar(128) charset utf8mb4 not null comment 'title'
)
comment 'test2';

An example code is

public function pdoTest()
{
    $title = "test";
    $id = 1;

    $db = new PDO('mysql:host=<your-host>;port=<your-port>;dbname=<your-dbname>;charset=utf8', '<your-username>', '<your-password>');

    $db->beginTransaction();

    $query = "INSERT INTO test (title) VALUES (:title)";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':title', $title);
    $stmt->execute();

    $updateQuery = "UPDATE test2 SET title = :title WHERE id = :id";
    $stmt = $db->prepare($updateQuery);
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':title', $title);
    $stmt->execute();

    echo $db->lastInsertId();
    $db->commit();
}

In the above situation, the return value of lastInsertId was 0. I searched the PHP site. Does anyone know why lastInsertId is not working properly?

I am wondering if only the insert query must be performed before executing the lastInsertId function.

Tags:

2

Answers


  1. 0

    Your code seems mostly correct, but the issue lies in how PHP PDO works with lastInsertId(). This method only returns the ID of the last inserted record and not after an UPDATE query. Since your logic includes an UPDATE query right after the INSERT, calling $db->lastInsertId() at that point will not reflect the inserted ID anymore.

    To fix this, simply move the call to $db->lastInsertId() directly after the INSERT query, and it should work as expected.

    Login or Signup to reply.
  2. 0

    When working with MySQL, PHP is using Mysql C API.

    In order to retrieve the auto-increment value, PHP is calling mysql_insert_id() function from this API. And, according to MySQL documentation,

    The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.

    So, it seems that things are the other way round: it’s LAST_INSERT_ID()’s behavior is closer to "incorrect", as it’s still returning a value after executing queries that didn’t change it.

    While PDO::lastInsertId() gives you more exact result, returning a value only if the last query affected the AUTO_INCREMENT column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search