PHP : MySQL Get Last Inserted ID

To get the last inserted ID (auto-increment value) after inserting a new row into a MySQL database table using PHP, you can use the mysqli_insert_id() function with mysqli or lastInsertId() method with PDO. These functions/methods return the last generated auto-increment ID from the last INSERT query.

Using mysqli :

Here's an example using mysqli to insert a new row into the users table and get the last inserted ID:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// Data to be inserted
$name = "John Doe";
$email = "john@example.com";

// SQL query to insert data
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

if ($conn->query($sql) === TRUE) {
    $last_id = $conn->insert_id; // Get last inserted ID
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close connection
$conn->close();
 

Using PDO :

Here's the same example using PDO :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Data to be inserted
    $name = "John Doe";
    $email = "john@example.com";

    // SQL query to insert data
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);

    // Execute the query
    $stmt->execute();

    $last_id = $conn->lastInsertId(); // Get last inserted ID
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• The insert_id property with mysqli and lastInsertId() method with PDO retrieves the last auto-generated ID from an INSERT query.
• It's important to get the last inserted ID immediately after executing the INSERT query, as it may change if other queries are executed.
• The retrieved ID is typically used for further operations or to display to the user.

Important Notes:

• Ensure that the table has an auto-increment primary key column.
• Always sanitize user input and use prepared statements to prevent SQL injection attacks.
• Verify that the connection is successful before executing queries.

Running these scripts will insert a new row into the users table, and then it will display the last inserted ID. This ID is useful for scenarios where you need to reference the newly inserted row in subsequent database operations.