fetch_all

The mysqli_fetch_all() function fetches all rows of a MySQLi result set at once and returns them as a single two-dimensional array. Instead of looping with mysqli_fetch_assoc() row by row, you get the whole result set in one call — which is convenient when you want to pass the data to a template, encode it as JSON, or process it in bulk.

This article covers the syntax, the result-type constants, the return value, working examples, and the common pitfalls.

Syntax and parameters

mysqli_fetch_all(mysqli_result $result, int $mode = MYSQLI_NUM): array

Return value: a two-dimensional array of all rows. If the result set is empty, it returns an empty array [].

Note: mysqli_fetch_all() requires the mysqlnd driver. It is available since PHP 5.3 and works with both the procedural style shown here and the object-oriented $result->fetch_all() style.

How to use mysqli_fetch_all()

Call the function on a valid result set after running a query. Here we fetch every row as an associative array:

<?php
$mysqli = mysqli_connect("localhost", "username", "password", "database");

if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = "SELECT * FROM my_table";
$result = mysqli_query($mysqli, $query);

if ($result) {
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
    foreach ($rows as $row) {
        echo $row['column1'] . " - " . $row['column2'] . "\n";
    }
}

mysqli_close($mysqli);
?>

Step by step: mysqli_connect() opens the connection, and we verify it succeeded to avoid a later fatal error. mysqli_query() runs the SELECT and returns a mysqli_result. We then call mysqli_fetch_all($result, MYSQLI_ASSOC), which returns an array like this:

[
    ['column1' => 'Anna',  'column2' => 'NYC'],
    ['column1' => 'Bob',   'column2' => 'LA'],
]

Because each row is keyed by column name, the foreach loop reads $row['column1'] and $row['column2'] directly.

Fetching as a numeric or combined array

The $mode argument changes how rows are keyed. Pass MYSQLI_NUM to get numeric indexes, or MYSQLI_BOTH to get both names and indexes in every row. Here we fetch a numeric array:

<?php
$mysqli = mysqli_connect("localhost", "username", "password", "database");

if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = "SELECT * FROM my_table";
$result = mysqli_query($mysqli, $query);

if ($result) {
    $rows = mysqli_fetch_all($result, MYSQLI_NUM);
    foreach ($rows as $row) {
        echo $row[0] . " - " . $row[1] . "\n";
    }
}

mysqli_close($mysqli);
?>

Here the rows come back keyed by position, so we read the first two columns as $row[0] and $row[1]. With MYSQLI_BOTH both $row[0] and $row['column1'] would point to the same value — handy when you are migrating code but doubles the memory each row uses.

fetch_all() vs. fetch_assoc() in a loop

mysqli_fetch_all() loads the entire result set into PHP memory at once, then frees you from looping. By contrast, mysqli_fetch_assoc() pulls one row per call, so a while loop processes rows one at a time:

// Equivalent output, but only one row in memory at a time:
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['column1'] . " - " . $row['column2'] . "\n";
}

Use mysqli_fetch_all() when the result set is small-to-moderate and you want all rows together (e.g. to json_encode() them). Prefer the while loop with mysqli_fetch_assoc() for very large result sets, where loading everything at once could exhaust memory.

Common pitfalls

• $result must be valid. If the query failed, mysqli_query() returns false, not a result object — always check before fetching, as the examples do.
• Empty result sets are not an error. When no rows match, you get [], and the foreach simply does nothing.
• Always use prepared statements with untrusted input. The literal SELECT * FROM my_table above takes no user input; for anything dynamic, bind parameters to prevent SQL injection.

Related functions

• mysqli_fetch_assoc() — fetch one row at a time as an associative array.
• mysqli_fetch_array() — fetch one row as associative, numeric, or both.
• mysqli_fetch_row() — fetch one row as a numeric array.
• mysqli_fetch_object() — fetch one row as an object.
• mysqli_query() — run the query that produces the result set.

Conclusion

mysqli_fetch_all() is the fastest way to pull an entire MySQLi result set into a PHP array. Choose MYSQLI_ASSOC for readable column-name keys, MYSQLI_NUM for compact numeric keys, or MYSQLI_BOTH when you need both. Remember that it loads every row into memory, so for large data sets a row-by-row loop with mysqli_fetch_assoc() is the safer choice.

Practice