Understanding fetch_assoc()

Understanding fetch_assoc()

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

Stefan Bogdanescu

Founder & Senior Architect

How fetch_assoc() Manages Rows in a Result Set

The confusion surrounding your code stems from a misunderstanding of how MySQLi result objects store data and how the fetch_assoc() method operates within that context. The core issue is that fetch_assoc() does not simply return the data for a specific row; it returns the current row and simultaneously advances the internal cursor of the result set to the next position.

When you execute $results->fetch_assoc() outside of a loop, you are calling this method once on the entire result object. The method retrieves the first available row (index 0), converts it into an associative array where column names become keys, and then increments the internal pointer. Consequently, the second call to print_r inside the loop works because the cursor has already moved past the first row, allowing the loop to process subsequent rows in sequence until the end of the dataset is reached.

The Cursor Mechanism

To understand why the while loop behaves differently from a single function call, you must visualize the result set as having an internal pointer, often referred to as a cursor. Think of this cursor like a bookmark in a book or a needle on a tape recorder.

When you run a query like SELECT * FROM customer, the database engine prepares a result set containing three rows. However, before any data is returned to your PHP script, the cursor sits at the "start" position, pointing at the first row but not yet reading it.

$results = $connectToDb->fetch("SELECT * FROM customer");
// Cursor is now pointing at Row 1, waiting to be read.

When you invoke fetch_assoc():

  1. Read: The method reads the data currently pointed to by the cursor (Row 1).
  2. Convert: It builds an associative array using column names as keys.
  3. Advance: Crucially, it moves the cursor forward to point at Row 2.

This advancement is what happens "under the hood." If you call fetch_assoc() again immediately after the first one, without any loop logic, it will retrieve the second row and advance the cursor again. If you do this three times, you effectively consume all rows in the result set sequentially.

Why the Loop Works

The structure of your while loop relies entirely on the return value of fetch_assoc() combined with the implicit cursor advancement.

while ($row = $results->fetch_assoc()) {
    print_r($row);
}

Inside this loop, the condition $row = $results->fetch_assoc() executes on every iteration:

  1. Iteration 1: The method fetches Row 1 and advances the cursor to Row 2. It returns an array. The while condition evaluates to true (since arrays are truthy), so the loop body runs.
  2. Iteration 2: The method fetches Row 2 and advances the cursor to Row 3. It returns an array. The condition is still true, and the loop continues.
  3. Iteration 3: The method fetches Row 3 and advances the cursor past the end of the result set. It returns null because there are no more rows.

The while loop checks the truthiness of the returned value. When fetch_assoc() finally returns null, the condition fails, and the loop terminates naturally. You aren't explicitly telling it to fetch "more"; you are relying on the method's contract: "Give me the next row and move the pointer." If there is no next row, give me nothing (null).

Performance Considerations with Large Datasets

While this pattern works perfectly for small result sets, it introduces a specific trade-off regarding memory usage. The fetch() method returns an object that holds the entire result set in memory until it is explicitly closed or garbage collected.

$results = $connectToDb->fetch("SELECT * FROM customer");
// If 'customer' table has millions of rows, this single object consumes significant RAM.

If you are working with large datasets, holding the full result set in memory can lead to performance bottlenecks or Out of Memory errors. In such scenarios, it is often better to use a cursor-based approach where you fetch only one row at a time and immediately process it before discarding it from memory.

However, if your goal is simply to iterate through a manageable number of rows (like 3, as in your example), the standard fetch_assoc() loop is the idiomatic and efficient solution. It avoids the overhead of creating multiple intermediate objects for every row because the result object manages the state internally.

Practical Application

Understanding this mechanism helps when debugging logic errors where data seems to "disappear." If you notice that a variable isn't updating or rows are missing, check if you accidentally advanced the cursor too early by calling fetch_assoc() before your loop starts. Always ensure the first call happens inside the loop condition so the initial row is captured immediately.

For more complex database interactions in modern PHP frameworks like Laravel, this raw MySQLi pattern is often abstracted away into query builders that handle result set management and chaining automatically. However, understanding the underlying cursor mechanics remains essential for debugging legacy codebases or when performance tuning is required.

Our network