MySQLi fetch_array Method

PHP

MySQLi fetch_array - Fetch Row as Array

In PHP, when working with MySQL databases using MySQLi, fetching data efficiently and accurately is crucial. The mysqli_fetch_array() method offers a flexible way to retrieve query result rows as arrays. This tutorial will cover everything you need to know about the mysqli_fetch_array() method, including step-by-step examples, best practices, common mistakes, and interview questions focused on this function.

Prerequisites

  • Basic knowledge of PHP and MySQL
  • MySQL server setup with accessible database
  • PHP installed with MySQLi extension enabled
  • A sample database and table to run queries (e.g., users table)

Setup Steps

Before using the mysqli_fetch_array() method you need to establish a connection and run a query. Follow these steps:

  1. Connect to MySQL Database: 
    $mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
  2. Perform a Query: 
    $query = "SELECT id, name, email FROM users";
$result = $mysqli->query($query);

if (!$result) {
    die('Query Error: ' . $mysqli->error);
}
  3. Fetch Data Using mysqli_fetch_array(): This is the focus of the tutorial, explained below.

Understanding mysqli_fetch_array()

The mysqli_fetch_array() function fetches one row from a result set as an array. By default, it returns an array with both associative and numeric indices. You can customize its behavior using a second optional parameter called resulttype.

Syntax

mysqli_fetch_array(mysqli_result $result, int $resulttype = MYSQLI_BOTH): array|null|false

$resulttype options:

  • MYSQLI_ASSOC - Returns associative array with column names as keys
  • MYSQLI_NUM - Returns numeric array with column offsets as keys
  • MYSQLI_BOTH - Returns both associative and numeric arrays (default)

Step-by-Step Examples

1. Fetch Row as Both Numeric and Associative Array (Default)

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

if ($result) {
    while ($row = mysqli_fetch_array($result)) {
        // Access by column name
        echo "ID (assoc): " . $row['id'] . "<br>";
        // Access by column number
        echo "ID (numeric): " . $row[0] . "<br>";
        echo "Name: " . $row['name'] . " / " . $row[1] . "<br>";
        echo "Email: " . $row['email'] . " / " . $row[2] . "<br><hr>";
    }
} else {
    echo "Query failed: " . $mysqli->error;
}
?>

2. Fetch Row as Associative Array Only

<?php
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    echo "User: " . $row['name'] . " (" . $row['email'] . ")<br>";
}
?>

3. Fetch Row as Numeric Array Only

<?php
while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
    echo "User: " . $row[1] . " (" . $row[2] . ")<br>";
}
?>

Best Practices

  • Close Result Sets: Always free up memory by calling $result->free(); after fetching all rows.
  • Use MYSQLI_ASSOC for clarity: Fetching only associative arrays improves code readability and reduces redundancy.
  • Validate Query Results: Check that the result set is valid before fetching rows.
  • Use Prepared Statements: To prevent SQL injection, prepare your queries before executing, especially when using user input.

Common Mistakes

  • Not Checking if Result is Valid: Not verifying if $result is false leads to errors when fetching.
  • Mismatched Indexing: Using numeric keys for associative arrays or vice versa causes undefined index errors.
  • Confusing fetch_array() with fetch_assoc() or fetch_row(): Know the differences:
    • fetch_assoc() returns associative array only
    • fetch_row() returns numeric array only
    • fetch_array() can return both or either
  • Failing to Free Result Sets: Large result sets can consume excessive memory if not freed.

Interview Questions

Junior-Level Questions

  • What does mysqli_fetch_array() do?
    It fetches a row from the result set as an array, either numeric, associative, or both.
  • What are the possible second parameters for mysqli_fetch_array() and their purpose?
    MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH control the type of array returned.
  • How do you access the first column using mysqli_fetch_array()?
    Use $row[0] for numeric or $row['column_name'] for associative array.
  • What is returned by mysqli_fetch_array() when no rows remain?
    It returns false.
  • Why might you prefer MYSQLI_ASSOC over MYSQLI_BOTH?
    To avoid duplicate data and improve code clarity.

Mid-Level Questions

  • How does mysqli_fetch_array() differ from fetch_assoc() and fetch_row()?
    It can return both numeric and associative indices; the others return only one type.
  • Can you fetch multiple rows at once using mysqli_fetch_array()?
    No, you fetch rows one at a time, typically in a loop.
  • What happens if you do not specify the resulttype argument in mysqli_fetch_array()?
    It defaults to MYSQLI_BOTH, giving both numeric and associative indices.
  • Is mysqli_fetch_array() vulnerable to SQL injections?
    No, but unsafe queries executed before fetching can be vulnerable. Use prepared statements.
  • How would you free memory after fetching rows?
    Call $result->free() after finishing fetching.

Senior-Level Questions

  • How does using MYSQLI_NUM affect performance compared to MYSQLI_BOTH?
    Fetching only numeric arrays reduces memory usage and improves performance by avoiding redundant data.
  • When iterating large datasets, what are implications of using mysqli_fetch_array() with MYSQLI_BOTH?
    It doubles the stored data per row in memory, which increases resource usage.
  • How can you handle fetching rows efficiently if the query returns millions of results?
    Use pagination or streaming, fetch associative arrays only, and free results promptly.
  • Can mysqli_fetch_array() be used with prepared statements?
    Not directly. You must bind result variables and fetch via mysqli_stmt_fetch().
  • Explain a scenario where numeric keys from mysqli_fetch_array() are preferred over associative keys.
    When you only need index-based iteration and want to save memory by avoiding the overhead of associative keys.

Frequently Asked Questions (FAQ)

Q: What is the default array type returned by mysqli_fetch_array()?
A: By default, it returns an array containing both numeric and associative keys (MYSQLI_BOTH).
Q: How do you fetch all rows from a query result?
A: Use a while loop with mysqli_fetch_array() until it returns false.
Q: Can mysqli_fetch_array() return a multi-dimensional array?
A: No, it returns a one-dimensional array for one row each time it's called.
Q: What happens if you pass an invalid resulttype to mysqli_fetch_array()?
A: PHP will default to MYSQLI_BOTH or may throw a warning depending on error settings.
Q: Is it necessary to free the MySQL result set from mysqli_query()?
A: It is recommended to free result sets with $result->free() to free memory, especially with large datasets.

Conclusion

The mysqli_fetch_array() method is a versatile and essential function for retrieving query results in PHP with MySQLi. Understanding how to use its various modes—associative, numeric, or both—helps you write clearer, more efficient code. By following best practices and avoiding common pitfalls, you can handle database results effectively in any PHP project. Whether you’re a beginner preparing for interviews or an experienced developer optimizing performance, mastering mysqli_fetch_array() is invaluable.