PHP MySQL Get Last ID

Why Get Last ID?

$user_id = $conn->insert_id;

echo "Order #" . $last_id;

header("Location: post.php?id=$id");

log("Created ID: " . $id);

🔹 MySQLi Object-Oriented Method

The insert_id property returns the ID generated by the last INSERT query. This is the most common and straightforward approach.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "blog_website";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert a new blog post
$title = "Getting Started with PHP";
$content = "PHP is a powerful server-side language...";
$author_id = 1;

$sql = "INSERT INTO posts (author_id, title, content) 
        VALUES ($author_id, '$title', '$content')";

if ($conn->query($sql) === TRUE) {
    $post_id = $conn->insert_id;
    echo "Post created successfully!<br>";
    echo "Post ID: " . $post_id . "<br>";
    echo "View at: post.php?id=" . $post_id;
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>
                            

🔹 MySQLi Procedural Method

Use the mysqli_insert_id() function to get the last ID when using procedural style code.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "online_store";

$conn = mysqli_connect($servername, $username, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Insert a new order
$customer_id = 5;
$total = 149.99;

$sql = "INSERT INTO orders (customer_id, total) 
        VALUES ($customer_id, $total)";

if (mysqli_query($conn, $sql)) {
    $order_id = mysqli_insert_id($conn);
    echo "Order placed successfully!<br>";
    echo "Your order number is: #" . $order_id;
} else {
    echo "Error: " . mysqli_error($conn);
}

mysqli_close($conn);
?>
                            

🔹 PDO Method

PDO uses the lastInsertId() method to retrieve the auto-generated ID from the most recent INSERT operation.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "user_management";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Insert new user
    $sql = "INSERT INTO users (username, email, password) 
            VALUES ('alex_brown', '[email protected]', 'hashed_password')";
    
    $conn->exec($sql);
    
    // Get last inserted ID
    $user_id = $conn->lastInsertId();
    echo "User registered successfully!<br>";
    echo "User ID: " . $user_id;
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>
                            

🔹 Using Last ID with Prepared Statements

Combine prepared statements for security with getting the last ID for functionality. This is the recommended approach for production code.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "social_media";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// User data from form
$user_id = 3;
$post_title = "My Vacation Photos";
$post_content = "Had an amazing time at the beach!";

// Prepare statement
$stmt = $conn->prepare("INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $user_id, $post_title, $post_content);

// Execute and get ID
if ($stmt->execute()) {
    $post_id = $conn->insert_id;
    echo "Post created!<br>";
    echo "Post ID: " . $post_id . "<br>";
    echo "<a href='view_post.php?id=$post_id'>View your post</a>";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>
                            

🔹 Creating Related Records

A practical example showing how to use the last ID to create related records in different tables, like a user and their profile.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "user_management";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Step 1: Insert user
$username = "emma_wilson";
$email = "[email protected]";
$password = password_hash("secure123", PASSWORD_DEFAULT);

$stmt1 = $conn->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$stmt1->bind_param("sss", $username, $email, $password);

if ($stmt1->execute()) {
    // Get the new user's ID
    $user_id = $conn->insert_id;
    echo "✓ User created (ID: $user_id)<br>";
    
    // Step 2: Create profile using the user ID
    $full_name = "Emma Wilson";
    $bio = "Web developer and designer";
    
    $stmt2 = $conn->prepare("INSERT INTO profiles (user_id, full_name, bio) VALUES (?, ?, ?)");
    $stmt2->bind_param("iss", $user_id, $full_name, $bio);
    
    if ($stmt2->execute()) {
        echo "✓ Profile created<br>";
        echo "✓ Registration complete!";
    }
    
    $stmt2->close();
}

$stmt1->close();
$conn->close();
?>
                            

🔹 Complete Registration Example

A full example showing user registration with email confirmation using the last inserted ID:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "ecommerce_site";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Registration data
$customer_name = "David Lee";
$customer_email = "[email protected]";
$customer_password = password_hash("mypassword", PASSWORD_DEFAULT);

// Insert customer
$stmt = $conn->prepare("INSERT INTO customers (name, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $customer_name, $customer_email, $customer_password);

if ($stmt->execute()) {
    $customer_id = $conn->insert_id;
    
    echo "✓ Account created successfully!<br>";
    echo "✓ Customer ID: " . $customer_id . "<br>";
    echo "✓ Welcome email sent to: " . $customer_email . "<br>";
    echo "<br>";
    echo "<a href='login.php'>Click here to login</a>";
    
    // In real application, you would:
    // - Send confirmation email
    // - Create session
    // - Redirect to dashboard
    
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>
                            

🔹 Important Notes