Looping Through SQL Results in PHP - Not getting Entire Array

Looping Through SQL Results in PHP - Not getting Entire Array

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

Stefan Bogdanescu

Founder & Senior Architect

Understanding mysql_fetch_array() Behavior in PHP

The core issue preventing your loop from displaying all rows isn't a bug in your database query or a limitation of array counting; it is a fundamental misunderstanding of how the mysql_* extension handles result sets. The code snippet you provided uses $row = mysql_fetch_array($result) only once. This function call executes immediately and returns exactly one row from the result set, then frees that specific row from memory.

When you run your diagnostic loop:

for ($i = 0; $i < count($row); $i++) {
    echo $row[$i] . " ";
}

You are correctly inspecting the contents of $row, which contains only the single record retrieved by that one function call. Consequently, count($row) will return the number of columns in the table (e.g., 3 for name, email, and phone), not the total number of rows in the database. The other three rows sitting in your MySQL database are still residing in the server-side result buffer managed by the mysql_query handle, completely untouched by your PHP script at this stage.

To iterate through a full result set and display every row in a table, you must loop over the query handle itself rather than trying to dump a single fetched array. The standard pattern involves wrapping the fetch call inside a while loop that continues until mysql_fetch_array() returns false, indicating that no more rows exist.

Here is how the corrected logic should look:

$query = "SELECT name, email, phone FROM users";
$result = mysql_query($query); // This returns a resource handle

// Initialize an empty row variable for the loop
while ($row = mysql_fetch_array($result)) {
    // At this point, $row contains one complete record.
    // You can pass this directly to your display function.
    displayRow($row); 
}

mysql_free_result($result);

In this structure, mysql_fetch_array is called repeatedly within the loop condition. On the first iteration, it grabs the first row. On the second, it grabs the next. This continues until the database cursor reaches the end of the data, at which point the function returns false, and the loop terminates naturally.

It is crucial to understand that mysql_fetch_array does not load all rows into a PHP array simultaneously. It acts as a cursor, pulling one row into memory at a time. If you attempted to store these in a single PHP array without looping (e.g., $allRows = mysql_fetch_array($result)), you would lose the connection to subsequent data immediately after that single assignment.

The Modern Context: Legacy vs. Current Standards

The code patterns shown above rely on the mysql_* extension, which is deprecated and removed in PHP 7.0+. While understanding this behavior is vital for maintaining legacy systems or reading older documentation, relying on these functions in new development projects introduces security risks and compatibility issues. Modern PHP applications should utilize either PDO or MySQLi, both of which support prepared statements to prevent SQL injection attacks.

For instance, using PDO, the iteration logic remains conceptually identical but offers better safety:

$stmt = $pdo->query("SELECT name, email, phone FROM users");
while ($row = $stmt->fetch()) {
    displayRow($row);
}

The underlying mechanism is the same: you iterate over a cursor until exhaustion. However, PDO and MySQLi provide stronger type safety and transaction management capabilities that the legacy mysql_* functions lack. If you are building new features or refactoring existing applications, migrating to these modern interfaces is not just an option; it is a requirement for secure, maintainable code.

If you are looking for comprehensive resources on database integration within the Laravel framework, which handles these cursor operations and prepared statements elegantly, you might find the documentation at https://laravelcompany.com useful for understanding best practices in modern PHP development.

Our network