mysqli query results to show all rows

mysqli query results to show all rows

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

Stefan Bogdanescu

Founder & Senior Architect

Iterating Over Multi-Row mysqli Query Results

The code snippet you provided retrieves a single row from the news_event table using fetch_array(). This approach is perfectly valid when you expect exactly one record or are processing a singleton value. However, attempting to treat a multi-row result set as a scalar variable creates an immediate logical gap: once you fetch the first row, the cursor moves past it, and any subsequent call to fetch_array() without a preceding loop will return false or an empty array.

To handle multiple rows, you must shift your mindset from retrieving a single value to iterating over a cursor. The pattern you are looking for is identical in both legacy mysql_* functions and modern mysqli. It relies on the fact that query() returns a result object where internal pointers track your position in the dataset.

The Iteration Pattern

In procedural PHP, you would replace the single fetch call with a while loop. Inside this loop, fetch_array() is called repeatedly. As long as there are rows left in the buffer, the function returns an associative or indexed array representing the current row; when the cursor reaches the end of the result set, it returns false, breaking the loop.

Here is how that translates directly to your mysqli implementation:

include $_SERVER['DOCUMENT_ROOT'].'/include/conn.php'; 

$query = "SELECT title FROM news_event";
$result = $mysqli->query($query);

// Check if the query actually returned results to avoid errors on empty sets
if ($result) {
    while ($row = $result->fetch_array(MYSQLI_BOTH)) {
        echo htmlspecialchars($row['title']) . "<br>";
    }
    
    // Free memory associated with this result set
    $result->free();
}

$mysqli->close();

Understanding MYSQLI_BOTH

One specific detail in your original code is the use of MYSQLI_BOTH. This flag instructs fetch_array() to return an array containing both keys and values. This is often useful for debugging or when you might need numeric indexing later, but it consumes roughly twice the memory of fetching only associative arrays (MYSQLI_ASSOC). If you know your column names won't conflict with numeric indices, consider using MYSQLI_ASSOC in production environments to optimize memory usage, especially when looping through thousands of rows.

Resource Management and Best Practices

A common pitfall in PHP database programming is forgetting to free resources. The $result->free() call you included is excellent practice. If you leave a large result set open while processing other logic, it can exhaust server memory or cause timeouts in long-running scripts. Always ensure the result object is freed once the iteration is complete.

Similarly, closing the connection ($mysqli->close()) should happen after all database interactions are finished. While PHP's garbage collector will eventually reclaim resources, explicitly managing them ensures predictable behavior in high-load scenarios.

Modern Alternatives: Cursor-Based Iteration

While the while ($row = $result->fetch_array()) loop is standard and efficient, modern PHP development often favors cursor-based iteration for readability when dealing with very large datasets or complex logic inside the loop. The syntax looks like this:

$result = $mysqli->query($query);

if (!$result) {
    die('Query failed: ' . $mysqli->error);
}

while ($result->next_result()) { // Not applicable here, but conceptually similar to cursors in other drivers
    // In standard mysqli loops, we stick to fetch_array as shown below
}

Actually, a more robust pattern for cursor-like behavior without explicitly managing the loop manually is simply relying on the fetch method's return value, which you are already doing. However, if you need to process the data asynchronously or handle massive datasets where holding the entire result set in memory is prohibitive, ensure your server configuration supports streaming results appropriately.

For most standard applications, the while loop combined with fetch_array() remains the most performant and readable solution. It avoids loading all rows into a temporary PHP array before processing, keeping memory footprint low.

If you are working within the Laravel ecosystem, remember that raw SQL queries still require manual resource management unless wrapped in Eloquent or stored procedures. For deeper dives into optimizing database interactions within Laravel applications, resources like https://laravelcompany.com can provide additional context on best practices for scaling data-heavy operations.

Our network