how to properly use while loop in PDO fetchAll

how to properly use while loop in PDO fetchAll

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

Stefan Bogdanescu

Founder & Senior Architect

Mixing Fetch Modes and Return Values in PDO

Converting from MySQLi to PDO often triggers a specific confusion regarding how data retrieval works inside versus outside your function scope. In MySQLi, you typically execute a query and then iterate over results using fetch_assoc() repeatedly within a while loop. When moving to PDO, it is common to see developers attempt to replicate this pattern by calling fetchAll() inside the loop condition or trying to return the statement object from a function while simultaneously fetching all rows as an array.

The fundamental issue in your updated code stems from a conflict between the method used to retrieve data and where that retrieval happens. In your getContent function, you are currently returning $row, which is the result of calling fetchAll(). However, fetchAll() consumes the entire result set immediately and returns it as an array. Once that array is returned, the underlying database cursor is effectively exhausted.

When you call this function in your main script and assign the return value to $sql, $sql is no longer a PDOStatement object; it is simply an indexed array of associative arrays. Consequently, attempting to run $sql->fetchAll(PDO::FETCH_ASSOC) inside the while loop fails because arrays do not have a fetchAll() method, nor do they hold a cursor that needs iterating. Furthermore, your original function returns the data directly, making the idea of iterating over it after fetching all rows redundant if you just need to process that single batch of three posts.

If your goal is to return a list of posts from a function and then iterate over them in the calling scope, you should return the fetched array directly. This approach treats the function as a data provider. The iteration happens entirely outside the function using a standard foreach loop or a while loop that checks for existence on the returned array.

function getContent() {
    $db = PDOconn();
    $query = "SELECT * FROM posts ORDER BY id DESC LIMIT 3";
    
    // Execute and fetch all rows in one call
    $sql = $db->prepare($query);
    $sql->execute();
    
    // Return the resulting array directly
    return $sql->fetchAll(PDO::FETCH_ASSOC);
}

// Usage outside the function
$posts = getContent();

if (!empty($posts)) {
    foreach ($posts as $row) {
        $id = $row['id'];
        $content = $row['content'];
        // Process or print data here
        echo "ID: {$id}, Content: {$content}<br>";
    }
}

Alternatively, if you strictly require a while loop structure outside the function, you can iterate directly over the returned array using foreach. However, if your intention is to fetch rows lazily (one by one) to save memory on very large datasets, you should not use fetchAll() in the function. Instead, return the PDOStatement object itself and execute the fetch() method inside the loop in the calling scope. This preserves the cursor state so that each call to fetch() retrieves the next row without loading the entire dataset into memory at once.

function getPostIterator() {
    $db = PDOconn();
    $query = "SELECT * FROM posts ORDER BY id DESC LIMIT 3";
    $sql = $db->prepare($query);
    
    // Execute but do not fetch all rows here
    $sql->execute();
    
    return $sql;
}

// Usage outside the function
$stmt = getPostIterator();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $id = $row['id'];
    $content = $row['content'];
    
    // Process data here
}

The first approach (returning the array) is generally preferred for small result sets like your LIMIT 3 query because it separates concerns: the function handles data retrieval, and the caller handles data iteration. The second approach (returning the statement) is necessary only when dealing with large datasets where memory efficiency matters.

Be mindful that mixing these patterns leads to errors. If you fetch all rows inside a function expecting to iterate later, you must treat the return value as an array, not a statement object. Consistency in how you handle cursors and result sets is key to maintaining clean, maintainable code when migrating between MySQLi and PDO architectures.

Our network