The mysqli_affected_rows() function in PHP is used to get the number of rows affected by the last query executed on the database. It is mainly used for queries like INSERT, UPDATE, and DELETE, where the number of rows affected by the query is important.

Syntax:

int mysqli_affected_rows(mysqli $link);

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

Return Value:

• It returns the number of rows affected by the last query.
• If the last query did not affect any rows, it returns 0.
• If an error occurs or if the query does not support row counting, it returns -1.

Example 1: Using mysqli_affected_rows() after an INSERT Query

In this example, we will insert a new row into a table and check how many rows were affected.

<?php
// Create connection
$conn = mysqli_connect("localhost", "username", "password", "database");

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

// Insert query
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
    // Get the number of affected rows
    echo "New record created successfully. Affected rows: " . mysqli_affected_rows($conn);
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Output:

New record created successfully. Affected rows: 1

Example 2: Using mysqli_affected_rows() after an UPDATE Query

Here, we will update a user's email address and check how many rows were affected.

<?php
// Create connection
$conn = mysqli_connect("localhost", "username", "password", "database");

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

// Update query
$sql = "UPDATE users SET email = 'new_email@example.com' WHERE username = 'john_doe'";
if (mysqli_query($conn, $sql)) {
    // Get the number of affected rows
    echo "Record updated successfully. Affected rows: " . mysqli_affected_rows($conn);
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Output:

Record updated successfully. Affected rows: 1

Example 3: Using mysqli_affected_rows() after a DELETE Query

This example shows how to delete a user and check how many rows were deleted.

<?php
// Create connection
$conn = mysqli_connect("localhost", "username", "password", "database");

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

// Delete query
$sql = "DELETE FROM users WHERE username = 'john_doe'";
if (mysqli_query($conn, $sql)) {
    // Get the number of affected rows
    echo "Record deleted successfully. Affected rows: " . mysqli_affected_rows($conn);
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Output:

Record deleted successfully. Affected rows: 1

Example 4: When No Rows Are Affected

If no rows are affected by a query (for example, no rows match the WHERE condition in an UPDATE or DELETE query), mysqli_affected_rows() will return 0.

<?php
// Create connection
$conn = mysqli_connect("localhost", "username", "password", "database");

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

// Attempt to update a user that does not exist
$sql = "UPDATE users SET email = 'nonexistent@example.com' WHERE username = 'nonexistent_user'";
if (mysqli_query($conn, $sql)) {
    // Get the number of affected rows
    echo "Record updated. Affected rows: " . mysqli_affected_rows($conn);
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Output:

Record updated. Affected rows: 0

Example 5: Handling Errors

If an error occurs or if the query doesn't support row counting, mysqli_affected_rows() returns -1.

<?php
// Create connection
$conn = mysqli_connect("localhost", "username", "password", "database");

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

// Query that does not affect rows (e.g., SELECT query)
$sql = "SELECT * FROM users";
mysqli_query($conn, $sql);

// Get the affected rows (will return -1 for SELECT queries)
echo "Affected rows: " . mysqli_affected_rows($conn);

// Close connection
mysqli_close($conn);
?>

Output:

Affected rows: -1

Summary:

• mysqli_affected_rows() helps you determine how many rows were affected by an INSERT, UPDATE, or DELETE query.
• It returns the number of affected rows or 0 if no rows were affected.
• It returns -1 for queries that don't support row counting (like SELECT).