The mysqli_insert_id() function in PHP is used to retrieve the ID of the last inserted record in a MySQL database table. This is particularly useful when you have an AUTO_INCREMENT field in your table, and you want to retrieve the value that was automatically generated for the last insert operation.

Syntax:

mysqli_insert_id($link);

• $link: The MySQL connection resource returned by mysqli_connect().

Return Value:

• This function returns the ID of the last inserted row or 0 if no rows were inserted or if the last insert did not involve an AUTO_INCREMENT column.

Example 1: Basic Usage

<?php
// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database_name");

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

// Insert a new record into the 'users' table
$query = "INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com')";
if ($mysqli->query($query) === TRUE) {
    // Get the last inserted ID
    $last_id = $mysqli->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $mysqli->error;
}

// Close the connection
$mysqli->close();
?>

Explanation:

• A new user is inserted into the users table.
• After the insert query is executed, mysqli_insert_id() fetches the AUTO_INCREMENT ID of the newly inserted row.
• This ID is displayed.

Example 2: Using Insert ID with Foreign Key

If you're inserting into a table with a foreign key reference and need to use the last inserted ID to link other data, you can do something like this:

<?php
// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database_name");

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

// Insert a new user into 'users' table
$query1 = "INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane.doe@example.com')";
if ($mysqli->query($query1) === TRUE) {
    $user_id = $mysqli->insert_id;  // Get the last inserted ID (user ID)
    
    // Insert a new order and reference the newly inserted user ID
    $query2 = "INSERT INTO orders (user_id, product_name, quantity) VALUES ($user_id, 'Product A', 2)";
    if ($mysqli->query($query2) === TRUE) {
        echo "New order created successfully for user with ID: " . $user_id;
    } else {
        echo "Error in order insertion: " . $mysqli->error;
    }
} else {
    echo "Error in user insertion: " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

Explanation:

• First, a new user is inserted into the users table.
• The user_id is fetched using mysqli_insert_id() and used to insert a new order into the orders table, establishing a relationship between the user and the order.

Important Notes:

• The function works only if the last query performed on the connection was an INSERT operation.
• If no AUTO_INCREMENT field was involved in the insert, mysqli_insert_id() will return 0.
• If multiple insert operations are made in a single transaction or script, mysqli_insert_id() will always return the ID of the last insert.

This function is essential when you need to know the ID of the inserted row for use in subsequent queries or operations.