Is it mandatory to insert a new record just before calling lastInsertId() to get the last inserted ID in PDO?

Question

I'm trying to learn PDO.

First, I inserted a record in a table successfully using below code.

<?php
  $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "myDBPDO";

  try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', '[email protected]')";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "New record created successfully";
  }
  catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
  }

  $conn = null;
?>

Then, I tried to get the ID of last inserted record. I wrote following code for it :

<?php
  $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "myDBPDO";

  try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $last_id = $conn->lastInsertId();
    echo "Last inserted ID is: " . $last_id;
  }
  catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
  }

  $conn = null;
?>

Output of above(second code snippet) is as follows :

Last inserted ID is: 0

Actually, I expected to get the below output :

Last inserted ID is: 1

1 is the value in ID field of the record I inserted in first program.

Why it's not returning the expected output as specified above?

Is it mandatory to insert a new record just before calling lastInsertId() to get the last inserted ID in PDO?

If yes, then how should I get the last inserted id without inserting a new record just before calling lastInsertId() using the second code snippet I've written? If no, what's the reason for it?

Answer 1

The underlying database call LAST_INSERT_ID() operates per-connection. If you sever the connection, the value is lost

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client

So to answer your question...

Is it mandatory to insert a new record just before calling lastInsertId() to get the last inserted ID in PDO?

Yes, that is correct.

---

how should I get the last inserted id without inserting a new record

You're pretty much limited to fetching the maximum ID value

$maxId = $conn->query('SELECT MAX(id) FROM MyGuests')->fetchColumn();

Alternately, save the lastInsertId() value from the first script somewhere that can be retrieved by the second script.