PHP mysqli_fetch_all() Function

Beginner
5 min read
Updated: Nov 2026
3 examples
MySQLi

Definition

In PHP, mysqli_fetch_all() returns every remaining row from a mysqli_result as a nested array in one call.

Object-oriented style uses $result->fetch_all($mode). You need a result object from a SELECT-style query. Modes include MYSQLI_NUM, MYSQLI_ASSOC and MYSQLI_BOTH. Large results use more memory than fetching row by row.

Syntax

Call mysqli_fetch_all() with the values you need. Start with this line.

PHP
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
CallWhat it does
mysqli_fetch_all($result, MYSQLI_ASSOC)Returns all rows as associative arrays.
mysqli_fetch_all($result, MYSQLI_NUM)Returns all rows as numeric arrays (default mode).
$result->fetch_all(MYSQLI_ASSOC)Object-oriented form.

Requires the mysqlnd driver (the default in modern PHP). After fetch_all(), the result cursor is at the end; use data_seek(0) if you need to read again.

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

Parameters

ParameterDescription
resultRequired. A mysqli_result from query() / store_result() / get_result().
modeOptional. MYSQLI_NUM (default), MYSQLI_ASSOC or MYSQLI_BOTH.

Return Value

Return ValueDescription
arrayA list of rows. Each row is an array shaped by $mode. Empty result yields an empty array.

PHP Examples

Example 1 — Fetch All Rows as Associative Arrays

Load a small product list at once. Sample output when connected to a demo database.

PHP
<?php
$mysqli = mysqli_connect("localhost", "demo", "secret", "shop");
$result = mysqli_query($mysqli, "SELECT id, name FROM products ORDER BY id LIMIT 2");

$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
foreach ($rows as $row) {
    echo $row["id"], ":", $row["name"], PHP_EOL;
}

mysqli_free_result($result);
mysqli_close($mysqli);

How It Works

  1. query() returns a mysqli_result (not a connection).
  2. fetch_all(. .., MYSQLI_ASSOC) builds an array of all rows.
  3. The foreach loop prints each id and name.
  4. Sample output shows two demo products.

Example 2 — Numeric Mode Default

Call fetch_all without MYSQLI_ASSOC and read column 0.

PHP
<?php
$mysqli = new mysqli("localhost", "demo", "secret", "shop");
$result = $mysqli->query("SELECT name FROM products ORDER BY id LIMIT 2");

$rows = $result->fetch_all(); // MYSQLI_NUM
foreach ($rows as $row) {
    echo $row[0], PHP_EOL;
}

$result->free();
$mysqli->close();

How It Works

  1. OO fetch_all() defaults to MYSQLI_NUM.
  2. Each row is a numeric array.
  3. Index 0 holds the name column.
  4. Sample names match the demo table.

Example 3 — Empty Result Is an Empty Array

When no rows match, fetch_all still succeeds with [].

PHP
<?php
$mysqli = mysqli_connect("localhost", "demo", "secret", "shop");
$result = mysqli_query($mysqli, "SELECT id FROM products WHERE id = -1");
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
echo "count=", count($rows), PHP_EOL;
mysqli_free_result($result);
mysqli_close($mysqli);

How It Works

  1. The WHERE clause matches nothing.
  2. fetch_all returns an empty array, not false.
  3. count() prints 0.
  4. This makes empty-result handling easy in beginners' code.

Frequently Asked Questions

It returns all remaining rows from a mysqli_result as a nested PHP array in a single call.
Call $result->fetch_all($mode) on a mysqli_result object.
MYSQLI_ASSOC is usually easiest because columns are named. Remember the default is MYSQLI_NUM if you omit the mode.
No. It belongs to mysqli_result. Run a SELECT first, then fetch_all on that result.
For small results it is simpler. For large results, looping with fetch_assoc() uses less peak memory.

Next: mysqli_fetch_array()

mysqli_fetch_array() reads the next row from a mysqli_result and returns it as an array. B.

mysqli_fetch_array() tutorial →

About the author

Mari Selvan M P
Mari Selvan M P 🔗

Developer, cloud engineer, and technical writer

  • Experience 12 years building web and cloud systems
  • Focus Full Stack Development, AWS, and Developer Education

I write practical tutorials so students and working developers can learn by doing—from databases and APIs to deployment on AWS.

8 people found this page helpful