Alternative for mysql_num_rows using PDO

Alternative for mysql_num_rows using PDO

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Replacing mysql_num_rows with PDO: A Shift in Logic

When migrating legacy PHP applications from the deprecated mysql_* extension to PDO, you will quickly encounter a specific friction point: the absence of mysql_num_rows(). In the old API, this function was a convenient shortcut that took a result resource and returned an integer count. However, PDO operates on a fundamentally different principle regarding data retrieval and memory management. You cannot simply drop in a function call to get the row count; you must change how your application requests data from the database server.

The primary reason mysql_num_rows() does not exist in PDO is that it relied on fetching every single row into memory to determine the total count, even if you only needed one. With PDO, we have more control over this process, but it requires a deliberate architectural choice. You cannot query the row count using a standard SELECT statement followed by a cursor-based fetch loop and expect the behavior of the old API without understanding the underlying mechanics.

The Fetch-All Approach

If your original code used mysql_num_rows() to check if a record exists before processing it, you are essentially asking for a boolean result: "Is there one or more rows?"

The most direct translation in PDO is to fetch all results into an array and check the size of that array. While this mimics the behavior of the old function, it carries a significant trade-off regarding memory usage. If your SELECT query returns millions of rows, loading them all into memory just to count them could cause a PHP memory limit error or crash the script.

$stmt = $pdo->query("SELECT id FROM users WHERE email = :email", [':email' => $email]);

// Fetch all rows into an array
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (count($rows) == 1) {
    $message = ['status' => 'ok'];
} else {
    $message = ['status' => 'error'];
}

This approach works perfectly for small result sets, such as looking up a user by email or checking a specific condition. However, it is not scalable for large datasets. The fetchAll() method forces the database driver to retrieve every row from the server and construct an array object in PHP memory. If your legacy application had queries that returned hundreds of thousands of rows, this migration path introduces a performance regression unless you adjust your query logic or increase memory limits.

The Limit Approach for Single Records

In many cases where mysql_num_rows() was used to verify existence, the underlying SQL logic often involved a LIMIT 1 clause or was checking a unique constraint. If you know logically that the query returns at most one row, you do not need to fetch anything to determine if it exists.

By using PDO's fetchColumn() with a limit of 0, you can check the result status without retrieving any data. This method leverages the fact that PDO::FETCH_BOUND or simply checking the return value of fetch() allows us to distinguish between "found" and "not found" efficiently.

$stmt = $pdo->prepare("SELECT id FROM users WHERE email = :email LIMIT 1");
$stmt->execute([':email' => $email]);

$row = $stmt->fetch(PDO::FETCH_ASSOC);

if ($row !== false) {
    // At least one row exists
    $message = ['status' => 'ok'];
} else {
    // Zero rows exist (or error occurred, check errorCode if needed)
    $message = ['status' => 'error'];
}

This is often the preferred pattern in modern PHP development. It eliminates memory overhead entirely for existence checks and explicitly signals that you are only interested in the presence of data, not the full dataset. It aligns with the principle of fetching only what is necessary.

Avoiding Cursors for Counting

A common pitfall when migrating from mysql_num_rows() to PDO is attempting to use a cursor or iterator loop to count rows:

$count = 0;
while ($row = $stmt->fetch()) {
    $count++;
}

While this syntax works, it is semantically inefficient if you do not actually need to iterate over the rows for subsequent processing. If your only goal was to get a count, PDO offers a dedicated method that interacts directly with the database server's row count metadata without pulling data into PHP memory: $stmt->rowCount().

However, there is a critical distinction in how different drivers handle rowCount(). For MySQL and PostgreSQL, rowCount() returns the number of rows affected by an INSERT, UPDATE, or DELETE statement. For SELECT statements, the behavior varies. In some PDO driver versions and configurations (particularly with MySQL), rowCount() for a SELECT query might return -1, indicating that the number of rows is not known until fetching them all. This makes relying on $stmt->rowCount() for counting SELECT results unreliable in many standard PHP/MySQL setups.

Therefore, unless you are using a database driver where rowCount() is guaranteed to return the select count (like PostgreSQL with specific configurations), you must stick to either fetchAll() for small sets or the existence check via fetch() for single-row scenarios. The "count while fetching" pattern remains the most robust universal solution for generic SQL counts in PDO when rowCount() cannot be trusted.

Our network