Current Location: Home> Latest Articles> How to use with PDOStatement::fetchAll to improve query efficiency

How to use with PDOStatement::fetchAll to improve query efficiency

gitbox 2025-05-28

When using PHP for database development, PDO (PHP Data Objects) is a very popular way of database access, which provides a unified interface to operate various databases. This article will explore two commonly used PDO methods: PDOStatement::fetchAll and PDOStatement::rowCount , and analyze how to use them in actual development to improve query efficiency and code robustness.

1. Understand fetchAll and rowCount

When executing SELECT queries using PDO, we usually use fetchAll to get all the result rows. This is a very direct way to return the query results in an array, which is easy to traverse.

 $stmt = $pdo->query("SELECT * FROM users");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

On the other hand, rowCount is to return the number of rows affected by the previous SQL statement. When executing SELECT statements, rowCount 's behavior depends on the database driver. In some drivers (such as MySQL's PDO_MYSQL), calling rowCount to a SELECT query does not always return the exact number of rows.

 $count = $stmt->rowCount();

2. Common misunderstandings: Over-reliance on rowCount for SELECT results

Many developers habitually write code like this when judging whether the query result is empty:

 $stmt = $pdo->query("SELECT * FROM users WHERE status = 'active'");
if ($stmt->rowCount() > 0) {
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

This approach does not return the expected results under certain database drivers (especially MySQL). The reason is: For SELECT statements, PDO's rowCount returns "0" in some drivers, even if the result is actually non-empty.

3. Recommended practices: use fetchAll first and then determine the quantity

In order to obtain reliable results and improve efficiency, it is recommended to use fetchAll to obtain all data, and then use count to determine whether there is data:

 $stmt = $pdo->prepare("SELECT * FROM users WHERE status = :status");
$stmt->execute(['status' => 'active']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (count($users) > 0) {
    // Have data，Processing logic
} else {
    // No data prompt
}

The advantages of this approach are:

  • Avoid the problem of rowCount performing inconsistently under different drivers;

  • After fetchAll is executed, the data has been loaded into memory, and the count($users) operation overhead is extremely small;

  • More readability and clearer logic.

4. Practical skills to improve efficiency

While fetchAll is convenient, it can consume a lot of memory when processing large amounts of data. The following strategies can be considered at this time:

1. Use fetch loop instead of fetchAll

 $stmt = $pdo->prepare("SELECT * FROM logs WHERE created_at > :date");
$stmt->execute(['date' => '2025-01-01']);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Process each row of data in real time
}

This method is suitable for scenarios where the result set is large but does not require one-time loading.

2. LIMIT restricts query data

Adding LIMIT is an important way to improve efficiency when only part of the data is needed:

 $stmt = $pdo->prepare("SELECT * FROM users WHERE status = :status LIMIT 100");
$stmt->execute(['status' => 'active']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

3. Use COUNT(*) to quickly determine whether the data exists

If you just want to know if there is data and don't need to actually get the result, you can use SELECT COUNT(*) :

 $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE status = :status");
$stmt->execute(['status' => 'active']);
$count = $stmt->fetchColumn();

if ($count > 0) {
    // There is matching data
}

5. Examples of practical application scenarios

Suppose you need to list all activated users in the background system of gitbox.net :

 $url = 'https://gitbox.net/api/active-users';

$stmt = $pdo->prepare("SELECT id, username, email FROM users WHERE status = :status ORDER BY created_at DESC LIMIT 50");
$stmt->execute(['status' => 'active']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (count($users) > 0) {
    foreach ($users as $user) {
        echo "username：{$user['username']}，Mail：{$user['email']}<br>";
    }
} else {
    echo "No activated user was found。";
}

6. Summary

  • Avoid relying on rowCount to judge the number of result rows in SELECT queries;

  • It is recommended to use fetchAll plus count to determine whether the data exists;

  • For large data queries, using fetch iteration and LIMIT are key optimization methods;

  • If you only need to count the number of rows, you can use SELECT COUNT(*) to improve efficiency.

The rational use of the methods provided by PDO can not only improve system performance, but also avoid inconsistent behavior in cross-database development and improve the robustness and maintainability of the code.

Related