Introduction

Counting rows with PDO is a common source of confusion because rowCount is reliable for write statements but not consistently reliable for SELECT across drivers. For accurate query result counts, SQL COUNT(*) is usually the right approach. Clear separation between affected-row counts and result-row counts prevents subtle pagination and reporting bugs.

When rowCount Is Reliable

For INSERT, UPDATE, and DELETE, rowCount generally reports affected rows.

1<?php
2$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
3$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
4
5$stmt = $pdo->prepare("UPDATE users SET active = 0 WHERE last_login < :cutoff");
6$stmt->execute([":cutoff" => "2025-01-01"]);
7
8echo "Updated rows: " . $stmt->rowCount();

This is the intended and portable use case.

Why rowCount for SELECT Is Problematic

For SELECT, many PDO drivers do not guarantee consistent rowCount behavior. Depending on driver and buffering mode, result may be zero, unknown, or inconsistent.

Use explicit counting SQL instead.

1<?php
2$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE active = 1");
3$stmt->execute();
4$total = (int)$stmt->fetchColumn();
5
6echo "Active users: $total";

This is reliable and communicates intent clearly.

Pagination Pattern: Count Query Plus Data Query

For paginated endpoints, run two coordinated queries.

1<?php
2$countStmt = $pdo->prepare("SELECT COUNT(*) FROM orders WHERE status = :status");
3$countStmt->execute([":status" => "open"]);
4$total = (int)$countStmt->fetchColumn();
5
6$dataStmt = $pdo->prepare(
7    "SELECT id, total
8     FROM orders
9     WHERE status = :status
10     ORDER BY id DESC
11     LIMIT :limit OFFSET :offset"
12);
13$dataStmt->bindValue(":status", "open", PDO::PARAM_STR);
14$dataStmt->bindValue(":limit", 20, PDO::PARAM_INT);
15$dataStmt->bindValue(":offset", 0, PDO::PARAM_INT);
16$dataStmt->execute();
17
18$items = $dataStmt->fetchAll(PDO::FETCH_ASSOC);

This scales better than fetching full result sets only to call count in PHP.

Counting Joined and Filtered Results Correctly

Your COUNT(*) query must mirror filtering logic from data query.

1<?php
2$sql = "SELECT COUNT(*)
3        FROM orders o
4        JOIN customers c ON c.id = o.customer_id
5        WHERE o.status = :status AND c.active = 1";
6
7$stmt = $pdo->prepare($sql);
8$stmt->execute([":status" => "open"]);
9$total = (int)$stmt->fetchColumn();

If count and data filters diverge, pagination totals become incorrect.

Memory and Performance Considerations

Fetching all rows just to count them is expensive for large datasets.

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$count = count($rows);

This is acceptable only for small result sets where rows are already needed in memory.

For large data, push counting into SQL engine where indexes and query planner can optimize.

Transaction Use Cases

In transactional workflows, affected-row counts are useful for operational checks.

1<?php
2$pdo->beginTransaction();
3$cleanup = $pdo->prepare("DELETE FROM sessions WHERE expires_at < NOW()");
4$cleanup->execute();
5$deleted = $cleanup->rowCount();
6$pdo->commit();
7
8echo "Deleted sessions: $deleted";

This provides useful metrics and sanity checks during maintenance jobs.

Error Handling and Driver Consistency

Always enable exception mode so query problems are visible.

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Also document driver assumptions in code comments, especially if your project might move between MySQL, PostgreSQL, and SQLite.

Avoid Deprecated Counting Shortcuts

Some legacy MySQL examples use SQL_CALC_FOUND_ROWS, but it is deprecated and often slower than explicit COUNT(*) queries. Keeping a separate count query is clearer, easier to optimize, and more portable across database engines.

Observability for Query Counts

Track both count-query latency and data-query latency in monitoring dashboards. Seeing these metrics separately helps identify slow count operations before pagination performance degrades for users.

Common Pitfalls

• Using rowCount for SELECT and assuming portability.
• Counting results by fetching all rows unnecessarily.
• Mismatching filters between count query and data query.
• Forgetting integer binding for pagination parameters.
• Running without exception mode and missing query errors.

Summary

• Use rowCount primarily for affected rows in write statements.
• Use COUNT(*) for reliable result counts on SELECT.
• Keep count query and paged data query logic aligned.
• Avoid memory-heavy fetch-all counting on large datasets.
• Configure PDO error handling and driver-aware practices for stable behavior.