How to Loop Through a MySQL Result Set
When you execute a SELECT query in PHP, the database does not return a simple array of objects or rows immediately. Instead, it returns a resource representing a result set that points to a cursor on the server side. Understanding how this resource works is the first step before writing any loop. The most direct approach relies on the mysqli_fetch_* family of functions, which pull data from the server into memory one row at a time.
To retrieve rows sequentially, you typically use mysqli_fetch_assoc(), mysqli_fetch_object(), or mysqli_fetch_array(). These functions advance the internal pointer of the result set and return an array, object, or associative array representing the current row. If there are no more rows, they return false. This pattern allows you to iterate until the cursor reaches the end of the dataset.
$result = $mysqli->query("SELECT * FROM users");
while ($row = mysqli_fetch_assoc($result)) {
// Process each row here
echo $row['name'];
}
// Always close the result set when done to free up server-side resources
mysqli_free_result($result);
The logic behind this loop is straightforward: you fetch a row, check if it exists, and if so, process it. If the function returns false, the loop terminates naturally. It is crucial to remember that these functions modify the position of the cursor within the result set. Once you have fetched all rows with a while loop, the internal pointer is at the end. Attempting to fetch another row immediately after will return false even if data was previously available.
If your application relies on PDO instead of the native MySQLi extension, the syntax shifts slightly but the underlying concept remains identical. PDO uses prepared statements as best practice, so you often see the loop embedded inside the execution context of a prepared query.
$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Process each row here
echo $row['name'];
}
In this PDO example, the fetch() method is called directly on the statement object. The second argument specifies the fetch mode, such as PDO::FETCH_ASSOC for associative arrays. Like MySQLi, you do not need to manually close the cursor in most cases because the PDO driver handles cleanup when the statement goes out of scope or is explicitly closed using $stmt->closeCursor().
Beyond fetching one row at a time, you might consider loading the entire result set into memory before looping. This can be achieved by passing MYSQLI_STORE_RESULT to the query function (in MySQLi) or using fetchAll() in PDO. While this simplifies the loop logicâallowing you to iterate over a standard array without worrying about cursor pointersâit carries a significant trade-off. Loading massive datasets into memory increases RAM usage and can lead to performance bottlenecks if the result set is larger than expected. For small to medium datasets, fetching all rows upfront might be more efficient due to reduced round-trips overhead compared to row-by-row fetching. However, for large queries, the cursor-based approach is generally safer and more scalable.
Another common pitfall involves forgetting to verify the query execution status before attempting to fetch rows. If a SQL syntax error occurs, mysqli_query() or PDO::query() might return false rather than a resource. Iterating over a non-resource result set will cause runtime errors or unexpected behavior depending on the PHP version. Always check the boolean return value of the query function before entering your loop to handle database errors gracefully.
For developers working within the Laravel framework, the Eloquent ORM abstracts these lower-level details entirely. You rarely interact with raw result sets when using Active Record patterns. Instead, you retrieve a collection of models that implements the Collection interface, allowing you to use standard PHP array functions like foreach. Under the hood, Laravel still manages the connection and cursor lifecycle, ensuring resources are cleaned up automatically. If you find yourself needing to debug database interactions or optimize query performance within your application, understanding these foundational mechanisms remains essential regardless of the framework layer.