PHP mysqli_affected_rows() Function

Beginner
5 min read
Updated: Nov 2026
3 examples
MySQLi

Definition

In PHP, mysqli_affected_rows() returns how many rows were changed by the last INSERT, UPDATE, REPLACE or DELETE on a connection.

In object-oriented style you read the $mysqli->affected_rows property. Procedurally you call mysqli_affected_rows($mysql). For a successful SELECT that used a buffered result, it can also report the number of rows in the result set.

Syntax

Call mysqli_affected_rows() with the values you need. Start with this line.

PHP
$n = mysqli_affected_rows($mysqli);
CallWhat it does
mysqli_affected_rows($mysqli)Returns the affected-row count after the last query on that connection.
$mysqli->affected_rowsObject-oriented property form of the same value.
mysqli_affected_rows($mysqli) === 0True when no rows matched or no change was needed.

A return of -1 means the last query failed or you called it after an unbuffered SELECT. Very large counts may come back as a string when they exceed PHP_INT_MAX.

Library declarations
PHP
mysqli_affected_rows(mysqli $mysql): int|string

Parameters

ParameterDescription
mysqlRequired. The mysqli connection from mysqli_connect() or mysqli_init().

Return Value

Return ValueDescription
int|stringRows affected (or retrieved for SELECT). 0 means none; -1 means error or an unbuffered SELECT.

PHP Examples

Example 1 — Count Rows After an UPDATE

Run an UPDATE and print how many rows changed. Sample output when connected to a demo database.

PHP
<?php
$mysqli = mysqli_connect("localhost", "demo", "secret", "shop");

mysqli_query($mysqli, "UPDATE products SET price = 9.99 WHERE category = 'books'");
echo "Updated: ", mysqli_affected_rows($mysqli), PHP_EOL;

mysqli_close($mysqli);

How It Works

  1. mysqli_connect() opens a demo connection (placeholder host and login).
  2. The UPDATE changes matching product rows.
  3. mysqli_affected_rows() reads how many rows that statement changed.
  4. Sample output when connected to a demo database shows three rows updated.

Example 2 — Object Style Versus Procedural

The same value is available as a property or as a function.

PHP
<?php
$mysqli = new mysqli("localhost", "demo", "secret", "shop");

$mysqli->query("DELETE FROM cart_items WHERE user_id = 42");
echo "OO property: ", $mysqli->affected_rows, PHP_EOL;
echo "Procedural:  ", mysqli_affected_rows($mysqli), PHP_EOL;

$mysqli->close();

How It Works

  1. new mysqli() creates the connection object.
  2. DELETE removes two matching cart rows in the sample.
  3. $mysqli->affected_rows is the object-oriented property.
  4. mysqli_affected_rows($mysqli) returns the same number procedurally.

Example 3 — Zero Means No Change

When the WHERE clause matches nothing, the count is zero.

PHP
<?php
$mysqli = mysqli_connect("localhost", "demo", "secret", "shop");

mysqli_query($mysqli, "UPDATE products SET stock = 0 WHERE id = 99999");
$count = mysqli_affected_rows($mysqli);

if ($count === 0) {
    echo "No rows matched that id.", PHP_EOL;
} else {
    echo "Changed ", $count, " row(s).", PHP_EOL;
}

mysqli_close($mysqli);

How It Works

  1. The UPDATE targets a product id that does not exist in the demo data.
  2. mysqli_affected_rows() returns 0.
  3. The if branch prints a clear message for beginners.
  4. Sample output when connected to a demo database confirms nothing changed.

Frequently Asked Questions

It returns how many rows the last INSERT, UPDATE, REPLACE or DELETE changed on that connection. For some SELECT queries it can also report the number of rows retrieved.
They report the same value. $mysqli->affected_rows is the object-oriented property. mysqli_affected_rows($mysqli) is the procedural function.
Usually the last query failed, or you called it after an unbuffered SELECT. Check mysqli_error() or enable mysqli exception reporting to see what went wrong.
For UPDATE, MySQL often counts only rows whose values actually changed. Rows that matched the WHERE clause but already had the new values may not increase the count.
Yes. If the count is larger than PHP_INT_MAX, PHP may return it as a string so the full number is not truncated.

Next: mysqli_autocommit()

mysqli_autocommit() turns automatic commit on or off for the current MySQL connection.

mysqli_autocommit() tutorial →

About the author

Mari Selvan M P
Mari Selvan M P 🔗

Developer, cloud engineer, and technical writer

  • Experience 12 years building web and cloud systems
  • Focus Full Stack Development, AWS, and Developer Education

I write practical tutorials so students and working developers can learn by doing—from databases and APIs to deployment on AWS.

8 people found this page helpful