While Loop using PHP with a MySQL server

While Loop using PHP with a MySQL server

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

Stefan Bogdanescu

Founder & Senior Architect

Fixing Logic Errors in PHP While Loops for MySQL Data Iteration

The code snippet provided attempts to iterate through a database result set, but it contains several fundamental logic errors that prevent it from displaying multiple records correctly. The primary issue lies in how the loop's termination condition is calculated and managed. Specifically, the line $i = $row.length relies on a property that does not exist in standard PHP MySQL functions, and the loop structure fails to handle the dynamic nature of iterating over a result resource.

In older PHP environments utilizing the mysql_* extension (which is now deprecated), there is no length property on a row fetched via mysql_fetch_array. Furthermore, even if you were using mysql_num_rows($result) to get the total count, calculating that number before entering the loop and then iterating based on an index does not align with how fetch_array works. The mysql_fetch_array() function advances the internal pointer of the result resource each time it is called. It does not return a static array you can index like $result[0], $result[1]. If you attempt to access properties of the fetched row outside of the loop or use an index-based counter that doesn't match the fetch mechanism, the variable $row will be empty or undefined in subsequent iterations because the pointer has moved past the current record.

To correctly display all records from the "Staff" table, you must rely on the return value of mysql_fetch_array(). This function returns a false boolean when there are no more rows to fetch. A while loop should check for this specific condition rather than comparing an arbitrary counter against a non-existent length property. The corrected logic looks like this:

while ($row = mysql_fetch_array($result)) { 
    echo $row["Name"].", Room ".$row["Room"].", Tel ".$row["Telephone"] . "<br>"; 
}

In this implementation, the condition mysql_fetch_array($result) is evaluated in every iteration. When it successfully retrieves a row, it returns that array, which evaluates to true, allowing the loop body to execute. When there are no more rows, it returns false, terminating the loop immediately. This approach eliminates the need for manual counters or length calculations entirely, ensuring that every record in the result set is processed exactly once.

It is important to note that the mysql_* extension used in your example was removed from core PHP versions 7 and later due to security vulnerabilities and lack of support for prepared statements. If you are maintaining legacy code or working in an environment still using PHP 5, this logic fixes the immediate functional issue. However, for any new development, you should migrate to mysqli or PDO. With mysqli, the syntax shifts slightly but remains conceptually similar regarding loop termination:

while ($row = mysqli_fetch_assoc($result)) { 
    echo $row["Name"].", Room ".$row["Room"].", Tel ".$row["Telephone"] . "<br>"; 
}

Using associative arrays (fetch_assoc) makes the code more readable and less error-prone compared to numeric indexing, especially when column names are known. This pattern of iterating until a false return value is encountered is the standard, robust way to handle database result sets in PHP. It avoids off-by-one errors common in index-based loops and ensures that you don't accidentally try to access properties on a non-existent row.

Our network