MySQLi fetch_all Method

PHP

MySQLi fetch_all - Fetch All Results

In PHP, when working with MySQL databases via the MySQLi extension, handling result sets efficiently is key to building smooth applications. The fetch_all() method provides a straightforward way to fetch all rows from a query result in one go, returning an array that is easy to process. This tutorial will walk you through using mysqli_fetch_all() step-by-step with practical examples, best practices, and insightful interview questions related to this method.

Prerequisites

  • Basic knowledge of PHP programming.
  • Access to a PHP environment with MySQLi extension enabled.
  • A MySQL database server with credentials.
  • Some familiarity with SQL queries.

Setup Steps

Before using the fetch_all() method, you need a working MySQLi connection and a sample database/query. Assume we have a database sample_db and a table users with columns id, name, and email.

<?php
// Database connection parameters
$host = "localhost";
$user = "your_username";
$password = "your_password";
$database = "sample_db";

// Create connection
$mysqli = new mysqli($host, $user, $password, $database);

// Check connection
if ($mysqli->connect_errno) {
    die("Failed to connect to MySQL: " . $mysqli->connect_error);
}
?>

Using fetch_all() Method

The fetch_all() method retrieves all rows from a MySQLi result object into a numeric or associative array format with one call. This is useful when you want to get all data immediately instead of iterating row-by-row.

Syntax

array mysqli_result::fetch_all ([ int $mode = MYSQLI_NUM ] )

The $mode optional parameter defines the format of each row in the array:

  • MYSQLI_NUM — Returns a numeric array (default).
  • MYSQLI_ASSOC — Returns an associative array with column names as keys.
  • MYSQLI_BOTH — Returns both numeric and associative keys.

Example 1: Fetch All Rows as Numeric Array

<?php
$query = "SELECT id, name, email FROM users";
$result = $mysqli->query($query);

if ($result) {
    // Fetch all rows as numeric array
    $rows = $result->fetch_all(MYSQLI_NUM);
    print_r($rows);
} else {
    echo "Query failed: " . $mysqli->error;
}
?>

Output:

Array
(
    [0] => Array
        (
            [0] => 1
            [1] => John Doe
            [2] => john@example.com
        )
    [1] => Array
        (
            [0] => 2
            [1] => Jane Smith
            [2] => jane@example.com
        )
    // more rows...
)

Example 2: Fetch All Rows as Associative Array

<?php
$result = $mysqli->query($query);

if ($result) {
    // Fetch all rows as associative array
    $rows = $result->fetch_all(MYSQLI_ASSOC);
    foreach ($rows as $row) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "<br>";
    }
} else {
    echo "Query error: " . $mysqli->error;
}
?>

Output:

ID: 1, Name: John Doe, Email: john@example.com
ID: 2, Name: Jane Smith, Email: jane@example.com

Best Practices

  • Use fetch_all() for small to medium result sets. Since it loads all results at once into memory, large datasets may consume too much memory.
  • Always check for query errors before calling fetch_all(). This prevents runtime issues.
  • Free the result set using $result->free() after fetching to release memory.
  • Specify the fetch mode explicitly (e.g., MYSQLI_ASSOC) for clarity and maintainability.

Common Mistakes

  • Calling fetch_all() on a non-result object. Make sure the query was successful before calling it.
  • Using fetch_all() with large tables without pagination. This can cause memory exhaustion.
  • Not freeing the result set after fetching. Though PHP frees results at script termination, explicitly freeing resources is good practice.
  • Assuming the fetch mode when using fetch_all() without specifying it. The default is MYSQLI_NUM, so unexpected data structures can occur.

Interview Questions

Junior Level

  • Q1: What is the purpose of the PHP MySQLi fetch_all() method?
    A1: It fetches all result rows from a MySQLi query result at once as an array.
  • Q2: What type of data structure does fetch_all() return?
    A2: It returns a multidimensional array containing all rows.
  • Q3: How do you specify fetching rows as an associative array using fetch_all()?
    A3: Use the constant MYSQLI_ASSOC as the parameter.
  • Q4: Before calling fetch_all(), what should you always check?
    A4: You should check that the query executed successfully and returned a valid result object.
  • Q5: Can fetch_all() be used with prepared statements?
    A5: No, fetch_all() works with result objects from query(). Prepared statements require a different fetch approach.

Mid Level

  • Q1: What is the difference between fetch_all(MYSQLI_NUM) and fetch_all(MYSQLI_ASSOC)?
    A1: MYSQLI_NUM returns rows as numeric index arrays, MYSQLI_ASSOC returns rows as associative arrays keyed by column names.
  • Q2: Why might using fetch_all() on large result sets be problematic?
    A2: Because it loads all data into memory at once, potentially causing high memory usage or exhaustion.
  • Q3: What function should you call on a result set after using fetch_all()?
    A3: You should call free() on the result set to free memory.
  • Q4: Could you explain when to prefer fetch_assoc() over fetch_all(MYSQLI_ASSOC)?
    A4: fetch_assoc() is used to fetch one row at a time, preferable for processing large results row-by-row to reduce memory use.
  • Q5: How do you handle errors if fetch_all() returns false?
    A5: Check mysqli error info and ensure the query result is valid before calling fetch_all().

Senior Level

  • Q1: Internally, how does fetch_all() improve performance over repeatedly calling fetch_assoc()?
    A1: It retrieves all rows in a single call, minimizing function call overhead and client-server communication, improving speed for small-medium datasets.
  • Q2: What are alternatives to fetch_all() for handling large datasets efficiently?
    A2: Using server-side cursors or iterating with fetch_assoc() to process row-by-row, or implementing pagination queries.
  • Q3: How does fetch_all() behave with unbuffered MySQLi queries?
    A3: fetch_all() requires buffered results; it cannot be used with unbuffered queries, which fetch data lazily and one row at a time.
  • Q4: Can you explain how to combine mysqli_store_result() with fetch_all() for multi-query handling?
    A4: After executing multi queries, use mysqli_store_result() to get each result set, then call fetch_all() on each to fetch all rows before moving to the next result.
  • Q5: Discuss memory consumption trade-offs when using fetch_all() with very large datasets in PHP.
    A5: fetch_all() loads the entire result set into memory, potentially exhausting PHP memory limits for very large datasets, so streaming rows with fetch_assoc() or paginating queries is preferred.

FAQ

Q: Does the fetch_all() method work with procedural MySQLi style?
A: Yes, use mysqli_fetch_all($result, MYSQLI_ASSOC) in procedural style.
Q: What happens if I call fetch_all() multiple times on the same result?
A: After the first call, the internal pointer is at the end of the result set, so subsequent calls return an empty array.
Q: Is fetch_all() supported in all PHP MySQLi versions?
A: It was introduced in PHP 5.3.0, so check your PHP version if using an older environment.
Q: Can fetch_all() be used to fetch BLOB data?
A: Yes, but be cautious with large binary data, as fetching all BLOBs at once can consume significant memory.
Q: How do I convert the array returned by fetch_all() to JSON?
A: Use json_encode($result->fetch_all(MYSQLI_ASSOC)) to get the JSON representation.

Conclusion

The MySQLi fetch_all() method in PHP offers an efficient and convenient way to fetch all rows of a result set in a single call. It simplifies data handling for small to medium-sized datasets with flexible fetch modes like numeric or associative arrays. However, mindful use is critical to avoid memory issues with large result sets. By following best practices and understanding its behavior, fetch_all() can greatly enhance your PHP database interaction workflows.