Get the Last Inserted ID With PHP PDO

Get the last inserted ID with PHP PDO by calling lastInsertId() right after an INSERT. MySQL assigns an AUTO_INCREMENT value to each new row, and this method hands that value back. You need it to link records: insert a parent row, read its new id, then store that id on the child rows. This tutorial inserts an author, captures the id MySQL generates, and reuses it to attach two books to that author. A final count then confirms both tables link up.

Requirements to get the last inserted ID:

• PHP 7.0 or newer (tested on PHP 8.5.7) with the pdo_mysql driver, which PHP enables by default.
• MySQL 8.0 or newer (tested on MySQL 8.4).
• A table with an AUTO_INCREMENT primary key, plus a command line to run the script.

How To Get the Last Inserted ID.

The objective is to insert one author, capture the id the database generated, and reuse it as the foreign key on related book rows. That id is the only link between the two tables.

Step 1.

First, create the two tables. Save this as setup.sql and load it with mysql -u root < setup.sql. Both ids are AUTO_INCREMENT, and books.author_id points back at authors.id.

DROP DATABASE IF EXISTS library;
CREATE DATABASE library CHARACTER SET utf8mb4;
USE library;

CREATE TABLE authors (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title     VARCHAR(200) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

Step 2.

Next, create the script, name it link.php, and copy in the following. Insert the author first. Then call lastInsertId() immediately, while it still reflects that insert. Finally, pass that id as author_id on each book so the child rows point at the right parent.

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=library;charset=utf8mb4',
    'root',
    '',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

// Insert the parent row.
$author = $pdo->prepare('INSERT INTO authors (name) VALUES (?)');
$author->execute(['Ada Lovelace']);

// Grab the id MySQL just assigned to that row.
$authorId = $pdo->lastInsertId();
echo "Inserted author \"Ada Lovelace\" with id $authorId.\n";

// Use it to link child rows to the author.
$book = $pdo->prepare('INSERT INTO books (author_id, title) VALUES (?, ?)');
foreach (['Notes on the Analytical Engine', 'On Bernoulli Numbers'] as $title) {
    $book->execute([$authorId, $title]);
    echo "Linked book \"$title\" to author $authorId.\n";
}

// Confirm the child rows really point at the author.
$count = $pdo->prepare('SELECT COUNT(*) FROM books WHERE author_id = ?');
$count->execute([$authorId]);
echo "Author $authorId now has {$count->fetchColumn()} book(s).\n";

One detail matters here. Call lastInsertId() right after the author insert, before any other write on the same connection. Each new insert overwrites the value, so reading it late gives you the wrong id.

Step 3.

Finally, run the script from the command line.

php link.php

Result of getting the last inserted ID.

The author insert returns id 1, and both books store that 1 as their author_id. As a result, the final count reads two, which proves the child rows point back to the parent through the id you captured:

Inserted author "Ada Lovelace" with id 1.
Linked book "Notes on the Analytical Engine" to author 1.
Linked book "On Bernoulli Numbers" to author 1.
Author 1 now has 2 book(s).

Notes on getting the last inserted ID:

• It returns a string. lastInsertId() gives you the id as a string like "1", not an int. That is fine for binding it straight back into another query, which is the common case.
• It reads the last insert on this connection only. The value is per-connection, so a concurrent request on another connection cannot corrupt yours. However, a second insert on the same $pdo replaces it, so capture it before you move on.
• Only works with AUTO_INCREMENT. If your primary key is a UUID or another value you supply yourself, MySQL generates nothing, so lastInsertId() has no number to return. Use the value you already have instead.
• A multi-row insert returns the first id. If one INSERT adds several rows at once, MySQL reports the id of the first new row, and the rest follow in sequence.
• When several inserts must all succeed together, wrap them so a failure cannot leave an orphaned child row. See how to insert MySQL data with PHP PDO for the base insert this tutorial builds on.

References:

• PDO::lastInsertId() — PHP Manual
• PDOStatement::execute() — PHP Manual
• LAST_INSERT_ID() — MySQL Manual