skip to content

With PDO, what does PDOStatement::rowCount() report after an UPDATE, and why can it return 0 for an existing row on MySQL?

level: middleimportance: should knowfreq 40%

answer

  1. affected rows of the last write
  2. MySQL counts changed, not matched
  3. same value in, 0 out
  4. Pdo\Mysql::ATTR_FOUND_ROWS at connect
  5. SELECT count is driver-defined

basics

~20 s

rowCount() returns the rows affected by the statement's last DELETE, INSERT or UPDATE. On MySQL an UPDATE that writes the values a row already holds changes nothing, so it reports 0 unless the connection sets Pdo\Mysql::ATTR_FOUND_ROWS.

solid answer

~40 s

`PDOStatement::rowCount()` returns the number of rows the statement's last `DELETE`, `INSERT` or `UPDATE` affected, and `PDO::exec()` returns the same count. MySQL counts **changed** rows by default, so an `UPDATE` that matches a row but writes the values it already has reports `0`; passing `Pdo\Mysql::ATTR_FOUND_ROWS => true` in the constructor options switches it to matched rows (the `PDO::MYSQL_ATTR_FOUND_ROWS` spelling is deprecated in PHP 8.5). I use it to check guarded writes, like a debit with `AND points >= ?`, where `rowCount() !== 1` means throw and roll back. After a `SELECT` the value is driver-defined, so I fetch the row or use `COUNT(*)` instead.

code

php · 12 lines
php
<?php
declare(strict_types=1);

$debit = $pdo->prepare(
    'UPDATE loyalty_accounts SET points = points - :n
     WHERE customer_id = :id AND points >= :n2'
);
$debit->execute(['n' => $points, 'id' => $customerId, 'n2' => $points]);

if ($debit->rowCount() !== 1) {
    throw new DomainException("Customer $customerId cannot spend $points points");
}

go deeper

for a junior

Recall that rowCount() reports how many rows an UPDATE, DELETE or INSERT affected, and that PDO::exec() returns the same number.

for a middle

Explain MySQL's changed-versus-matched rule, the connect-time Pdo\Mysql::ATTR_FOUND_ROWS option, and why rowCount() after a SELECT is not portable.

for a senior

Use rowCount() as the check for guarded writes inside a transaction, and spot the unchanged-value case that turns a valid request into a false not-found error.

for a principal

Set a team rule for write-path checks: which operations must verify affected rows, and whether found-rows mode is the connection default.

## What rowCount() is for `PDOStatement::rowCount(): int` returns the number of rows **affected** by the last `DELETE`, `INSERT` or `UPDATE` that statement object executed. `PDO::exec()` returns the same count directly for statements run without `prepare()`: its signature is `exec(string $statement): int|false`. The two most useful jobs in transaction code: - **Detecting a guarded write that did nothing.** A debit written as `UPDATE loyalty_accounts SET points = points - ? WHERE customer_id = ? AND points >= ?` is safe against overdraft, but a too-small balance makes it match zero rows without any error. `rowCount() !== 1` is the signal to throw and roll back. - **Reporting what a bulk operation did**, such as how many expired points were deleted. ## Why an existing row can give 0 on MySQL MySQL's default meaning of "affected" is **changed**, not **matched**. If the `UPDATE` finds the row but the new values equal the old ones, the server reports 0 affected rows: ```sql UPDATE loyalty_accounts SET tier = 'gold' WHERE customer_id = 7; -- already 'gold' ``` `rowCount()` returns `0` even though customer 7 exists. Code that reads 0 as "not found" then throws a false "unknown customer" error, or inserts a duplicate. The connection flag **`Pdo\Mysql::ATTR_FOUND_ROWS`** switches MySQL to reporting **matched** rows. It is a connect-time option, so it goes in the constructor's options array: ```php $pdo = new PDO($dsn, $user, $pass, [Pdo\Mysql::ATTR_FOUND_ROWS => true]); ``` In PHP 8.5 the old spelling `PDO::MYSQL_ATTR_FOUND_ROWS` is deprecated in favour of the `Pdo\Mysql` class constant, which exists since the driver subclasses arrived in PHP 8.4. ## rowCount() after a SELECT For statements that produce a result set, the manual calls the value **undefined and driver-specific**: | Situation | What `rowCount()` gives | |---|---| | `UPDATE` / `DELETE` / `INSERT` | affected rows, on every driver | | `SELECT` on MySQL, buffered mode (the default) | the number of rows in the result | | `SELECT` on MySQL, unbuffered | no reliable count before the rows are fetched | | `SELECT` on PostgreSQL with a scrollable cursor | `0`, per the manual | | `SELECT` on other drivers | whatever the driver reports — do not rely on it | So `if ($stmt->rowCount() > 0)` after a `SELECT` is a portability bug that happens to work on buffered MySQL. The portable options are: 1. Fetch the row and test the result: `fetch()` returns `false` when there is none. 2. Count in SQL: `SELECT COUNT(*) ...` and `fetchColumn()`. 3. Fetch everything with `fetchAll()` and call `count()` on the array, when the rows are needed anyway. ## A checklist for write paths - Check `rowCount()` after every guarded `UPDATE` or `DELETE` whose success the business logic depends on. - Decide whether "matched but unchanged" is success; if it is, either set `Pdo\Mysql::ATTR_FOUND_ROWS` or compare against a `SELECT` done inside the same transaction. - Keep the check **inside** the transaction's `try`, so a failed check throws and the `catch` rolls back the rest of the work. - Never use `rowCount()` to find a new row's id; it counts rows, it does not return keys. ## rowCount() and exec() side by side | API | Called on | Returns | |---|---|---| | `PDOStatement::rowCount()` | the statement, after `execute()` | `int`, affected rows of its last execution | | `PDO::exec()` | the connection, with an SQL string | `int\|false`, affected rows, or `false` on failure in silent mode | Use `exec()` only for SQL with no user data, such as a fixed `DELETE FROM expired_tokens WHERE ...` with a literal condition; anything carrying values goes through `prepare()` and `execute()`, and then `rowCount()` gives the count. ## Edge cases worth naming - An `INSERT` of several rows in one statement reports the number of rows inserted. - A statement object reused for many `execute()` calls reports only the **last** execution. - `PDO::exec()` returns `0` both for "nothing matched" and for statements that affect no rows by nature; `0` is not an error, while `false` (under silent error mode) is. - The value comes from the server after the statement ran; PDO does not count rows itself.

  • Can you set Pdo\Mysql::ATTR_FOUND_ROWS with setAttribute() after connecting?
    No. `pdo_mysql` reads it from the constructor's options array and turns it into a flag of the MySQL connection handshake, so it has to be present when the connection opens. Pass it in the options array of `new PDO(...)` or `Pdo\Mysql::connect(...)`.
  • Does the guarded debit in the example ever return 0 for a valid transfer?
    Only if `$points` is 0, because `points - 0` leaves the row unchanged and MySQL reports 0 changed rows. Validating that the amount is positive before the transaction avoids that false failure; with `ATTR_FOUND_ROWS` the matched row would be counted anyway.

saying these in an interview costs you the question

  • Reading rowCount() == 0 after an UPDATE as proof the row does not exist.
  • Using rowCount() after a SELECT as a portable way to count results.
  • Expecting rowCount() to return the new row's id after an INSERT.
  • Setting the found-rows flag with setAttribute() after the connection is open.
  • Treating exec() returning 0 as a failure.