affected_rows

The mysqli_affected_rows() function tells you how many rows the last write actually changed — the rows touched by the most recent INSERT, UPDATE, DELETE, or REPLACE run on a connection. It is the standard way to confirm that a write did what you expected: did the update match any rows? did the delete remove anything? This page covers the syntax, return values, the object‑oriented and procedural styles, a runnable end‑to‑end example, and the gotchas that trip people up.

Syntax

// Procedural style
mysqli_affected_rows(mysqli $mysql): int|string

// Object-oriented style (a read-only property, not a method call)
$mysqli->affected_rows

The single argument is the MySQLi connection you ran the query on — not a result set. There are no other parameters: the function always reports on the most recent statement executed against that connection.

Return value

On 64‑bit systems the count can exceed PHP_INT_MAX, in which case the value is returned as a numeric string — that is why the return type is int|string.

Gotcha — "matched" vs "changed". For an UPDATE, MySQL counts rows whose values were actually changed, not rows that merely matched the WHERE clause. Setting a column to the value it already holds counts as 0 affected rows. To count matched rows instead, connect with the MYSQLI_CLIENT_FOUND_ROWS flag.

Object-oriented vs. procedural

Both styles read the same value; pick one and stay consistent. Note that in OOP style affected_rows is a property, with no parentheses:

<?php
// Object-oriented
$mysqli->query("DELETE FROM users WHERE active = 0");
echo $mysqli->affected_rows;        // property — no ()

// Procedural — same result
mysqli_query($link, "DELETE FROM users WHERE active = 0");
echo mysqli_affected_rows($link);   // function call

A complete example

This script connects, runs an UPDATE, and reports how many rows were changed. Replace the credentials with your own.

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$mysqli->query("UPDATE users SET name = 'John' WHERE id = 1");
echo "Rows updated: " . mysqli_affected_rows($mysqli);

$mysqli->close();
?>

If a user with id = 1 exists and its name was not already John, the output is:

Rows updated: 1

If no user has id = 1, or the name was already John, the output is Rows updated: 0. See mysqli_query() for how the query itself is executed, and mysqli_connect() for the connection details.

With prepared statements

When you use prepared statements (the recommended way to run queries with user input — see mysqli prepared statements), call affected_rows on the connection, not on the statement, after execute():

<?php
$stmt = $mysqli->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->bind_param("si", $name, $id);
$name = "John";
$id   = 1;
$stmt->execute();

echo "Rows updated: " . $mysqli->affected_rows;  // read from the connection
$stmt->close();
?>

SELECT queries and deprecated counters

mysqli_affected_rows() is meant for write queries. For a SELECT it returns -1; to count rows in a result set, call mysqli_num_rows() on that result instead. Avoid the old SQL_CALC_FOUND_ROWS / FOUND_ROWS() pair — they are deprecated as of MySQL 8.0.17 and removed in newer releases. Run a separate COUNT(*) query when you need a total.

Related functions

• mysqli_query() — run the query whose effect you are measuring.
• mysqli_insert_id() — get the auto-increment ID generated by the last INSERT.
• Insert data into MySQL, Update data, Delete data — full CRUD walkthroughs.

Summary

Use mysqli_affected_rows() (or the OOP $mysqli->affected_rows property) right after an INSERT, UPDATE, DELETE, or REPLACE to verify the write. Remember the three return values — a positive count, 0 for no change, and -1 for a failure or a SELECT — and that an UPDATE counts only rows whose values truly changed.

Practice