MySQL Get Last ID

PHP MySQL: Get Last Inserted ID

Get ID of The Last Inserted Record

When you insert a new row into a database table that has an AUTO_INCREMENT primary key column, it’s often useful to retrieve the ID that was automatically generated for that new row. This ID can then be used for subsequent operations, like inserting related data into other tables.

Consider the MyGuests table with an AUTO_INCREMENT primary key id:

CREATE TABLE MyGuests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Both the mysqli and PDO extensions provide methods to get the last inserted ID for the current database connection.

Important: You must call the method immediately after the successful INSERT query on the same connection.

Example using MySQLi (Object-Oriented)

This example demonstrates connecting, inserting data using a prepared statement (best practice!), and retrieving the last inserted ID.

<?php
// Best practice: Store credentials securely, not directly in code.
// Consider using environment variables or a configuration file.
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB"; // Replace with your DB name

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Enable exceptions for errors
$conn = new mysqli($servername, $username, $password, $dbname);

// Set character set (recommended)
$conn->set_charset('utf8mb4');

$firstname = 'Peter';
$lastname = 'Jones';
$email = 'peter.jones@example.com';

try {
    // Prepare statement (prevents SQL injection)
    $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);

    // Bind parameters (s = string)
    $stmt->bind_param("sss", $firstname, $lastname, $email);

    // Execute the statement
    if ($stmt->execute()) {
        // Get the ID of the inserted record
        $last_id = $conn->insert_id;
        echo "New record created successfully. Last inserted ID is: " . $last_id;
    } else {
        // Note: With exceptions enabled, this else might not be reached for execution errors.
        echo "Error executing statement.";
    }

    // Close statement
    $stmt->close();

} catch (mysqli_sql_exception $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Always close the connection
    if (isset($conn) && $conn instanceof mysqli) {
        $conn->close();
    }
}
?>

Key part: $last_id = $conn->insert_id; retrieves the ID after $stmt->execute() succeeds.

Example using PDO (Recommended)

PDO (PHP Data Objects) provides a consistent interface for accessing various databases. It’s generally preferred for its flexibility and support for named placeholders.

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDBPDO"; // Replace with your DB name
$charset = 'utf8mb4';

// Data Source Name (DSN)
$dsn = "mysql:host=$servername;dbname=$dbname;charset=$charset";

// PDO options
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Turn on errors in the form of exceptions
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Make the default fetch be an associative array
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Disable emulation of prepared statements for security
];

$firstname = 'Mary';
$lastname = 'Moe';
$email = 'mary.moe@example.com';

$conn = null; // Initialize connection variable
try {
    // Create PDO instance
    $conn = new PDO($dsn, $username, $password, $options);

    // Prepare statement with named placeholders
    $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES (:fname, :lname, :email)";
    $stmt = $conn->prepare($sql);

    // Bind values and execute
    $stmt->execute([
        'fname' => $firstname,
        'lname' => $lastname,
        'email' => $email
    ]);

    // Get the ID of the inserted record
    $last_id = $conn->lastInsertId();
    echo "New record created successfully using PDO. Last inserted ID is: " . $last_id;

} catch (PDOException $e) {
    // Handle potential errors
    echo "Database Error: " . $e->getMessage();
    // In a real application, log the error instead of echoing details
    // die(); // Optionally terminate script on critical error
} finally {
    // PDO connection is closed automatically when the script ends or $conn is set to null.
    // $conn = null; // Explicitly close if needed earlier
}
?>

Key part: $last_id = $conn->lastInsertId(); retrieves the ID after $stmt->execute() succeeds.

Note: Both examples now incorporate prepared statements and basic error handling using exceptions, which are crucial modern practices for database interactions.