PHP PDO INSERT: Save New Rows and Get the Last Insert ID

INSERT adds a new row.

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("INSERT INTO contacts (name, email, message) VALUES (:name, :email, :message)");

$statement->execute([
    "name" => "Isha",
    "email" => "isha@example.com",
    "message" => "Hello! I'd like to learn more.",
]);

echo "Saved. New id: " . $pdo->lastInsertId();

Output

OUTPUT

Saved. New id: 1

lastInsertId() returns the AUTO_INCREMENT id of the row that was just inserted. It is the way to find the new record.

Values with quotes are fine

Notice the apostrophe in I'd. A prepared statement handles it. You never escape values yourself.

Save validated form data

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$_POST = ["name" => "  Ravi  ", "email" => "ravi@example.com", "message" => "Please call me."];

$name = trim($_POST["name"] ?? "");
$email = trim($_POST["email"] ?? "");
$message = trim($_POST["message"] ?? "");

$errors = [];

if ($name === "") {
    $errors[] = "Name is required.";
}

if (filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
    $errors[] = "Invalid email.";
}

if ($message === "") {
    $errors[] = "Message is required.";
}

if ($errors === []) {
    $pdo->prepare("INSERT INTO contacts (name, email, message) VALUES (?, ?, ?)")
        ->execute([$name, $email, $message]);

    echo "Saved as id " . $pdo->lastInsertId();
} else {
    echo implode(" ", $errors);
}

Output

OUTPUT

Saved as id 1

The order is always: validate, then insert with placeholders. Also store the original text, and escape it when you print it (see Output Escaping).

Insert several rows in a loop

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("INSERT INTO notes (title, body) VALUES (:title, :body)");

$notes = [["Shopping", "Milk, eggs"], ["Ideas", "Learn PDO"]];

foreach ($notes as [$title, $body]) {
    $statement->execute(["title" => $title, "body" => $body]);
}

echo $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn() . " notes saved";

Output

OUTPUT

2 notes saved

For many rows, wrap the loop in a transaction, so they are saved together and much faster:

index.php

PHP

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("INSERT INTO notes (title) VALUES (?)");

$pdo->beginTransaction();

try {
    foreach (["a", "b", "c"] as $title) {
        $statement->execute([$title]);
    }
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

echo $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn() . " notes";

Output

OUTPUT

3 notes

If anything fails, rollBack() undoes all the inserts, and nothing is half-saved.

Duplicate values

If a column has a UNIQUE rule (like users.email) and you insert a duplicate, the database refuses, and PDO throws an exception.

index.php

PHP

<?php
try {
    $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)")->execute(["Ana 2", "ana@example.com"]);
} catch (PDOException $e) {
    if ($e->getCode() === "23000") {
        echo "That email is already registered.";
    } else {
        throw $e;
    }
}

Output

OUTPUT

That email is already registered.

Code 23000 is the SQL standard code for “integrity constraint violation”. Checking for it is more reliable than checking first with SELECT, because two visitors can register the same email at the same moment. See Handling Database Errors.

Timestamps

Let the database fill created_at with DEFAULT CURRENT_TIMESTAMP rather than passing the time from PHP. One less thing to get wrong.

warning

Echoing lastInsertId() before inserting

lastInsertId() belongs to the last insert on that connection. Call it straight after the execute(), not after other queries.