PHP mysqli_use_result() — Unbuffered Result Sets Explained

When you run a SELECT in PHP with the mysqli extension, MySQL has to hand the matching rows back to your script. There are two ways to receive them: buffered (copy the whole result into PHP's memory at once) and unbuffered (leave the rows on the MySQL server and pull them in one at a time). The mysqli_use_result() function initiates the unbuffered mode.

This guide explains what mysqli_use_result() does, how it differs from the default buffered mode, the rules you must follow when using it, and when reaching for it is actually worth it.

What mysqli_use_result() does

mysqli_use_result() initiates the retrieval of a result set that was produced by a query, without copying the rows into PHP's memory. Instead of downloading every row up front, the server keeps the rows and your script fetches them one by one as you loop.

Compare that with the default behaviour. When you call mysqli_query(), mysqli internally calls mysqli_store_result() for you — it pulls the entire result set into client memory before your code sees a single row. For a query that returns ten million rows, that can mean hundreds of megabytes of PHP memory. mysqli_use_result() avoids that cost: memory usage stays roughly constant no matter how many rows the query returns.

Syntax

mysqli_use_result(mysqli $mysql): mysqli_result|false

It takes the connection object (or link) returned by mysqli_connect() and returns a mysqli_result object on success, or false on failure. In object-oriented style the equivalent method is $mysqli->use_result().

Buffered vs. unbuffered: why the distinction matters

The key trade-off: unbuffered mode is light on memory but ties up the connection. You cannot run another query on the same connection until you have fetched every row (or freed the result). Buffered mode is the opposite — heavier on memory, but the connection is free the moment the query returns.

How to use mysqli_use_result()

1. Connect to the MySQL server

First open a connection with mysqli_connect():

<?php

$host     = 'localhost';
$user     = 'username';
$password = 'password';
$database = 'mydatabase';

$connection = mysqli_connect($host, $user, $password, $database);

if (!$connection) {
    die('Connection failed: ' . mysqli_connect_error());
}

2. Run the query, then start unbuffered retrieval

For unbuffered mode you must run the query without asking mysqli to store the result. Use the MYSQLI_USE_RESULT flag with mysqli_query() (or call mysqli_real_query()), then call mysqli_use_result():

$sql = "SELECT id, name FROM users";

// MYSQLI_USE_RESULT tells mysqli NOT to buffer the rows.
mysqli_real_query($connection, $sql);

$result = mysqli_use_result($connection);

if ($result) {
    while ($row = mysqli_fetch_assoc($result)) {
        // Process one row at a time — only this row lives in PHP memory.
        print_r($row);
    }
    mysqli_free_result($result);
}

Each call to mysqli_fetch_assoc() pulls the next row straight off the wire. You can also use mysqli_fetch_row(), mysqli_fetch_array(), or mysqli_fetch_all() the same way.

3. Always free the result

Calling mysqli_free_result() is more important here than in buffered mode: until the result is freed (or fully read), the connection stays locked and unusable for any other query.

Rules and gotchas

• Read every row before the next query. While an unbuffered result is open, the connection is busy. Trying to run another query before finishing the loop raises a "Commands out of sync" error. Finish the loop or call mysqli_free_result() first.
• No row count up front. mysqli_num_rows() returns the correct number only after you have fetched all the rows, because the server hasn't told the client how many there are yet.
• No random access. mysqli_data_seek() does not work on unbuffered results — you can only move forward.
• One result per connection. You can hold only one unbuffered result open on a connection at a time.

When should you use it?

Reach for mysqli_use_result() when a query returns far more data than you want to hold in memory — exporting a large table to CSV, streaming a report, or feeding a long result row by row into another process. In those cases the constant memory footprint is a real win.

For everyday queries that return a handful, or even a few thousand, rows, stick with the default buffered mode via mysqli_query(). The convenience of mysqli_num_rows(), random seeking, and a free connection almost always outweighs the modest memory saving.

Conclusion

mysqli_use_result() starts an unbuffered MySQL result set, fetching rows one at a time so memory usage stays low even for huge result sets. The cost is that the connection is locked until you finish reading, you lose mysqli_num_rows() and mysqli_data_seek(), and you must free the result promptly. Use it for genuinely large datasets; prefer the buffered default for ordinary queries.

Practice