The mysqli_fetch_array() function in PHP retrieves a result row from a MySQLi result set as an array. This function allows you to fetch rows from a query result in both associative and numeric array formats. It can be used with MySQLi in procedural style.

Syntax

mysqli_fetch_array(result, resulttype)

Parameters

• result: The mysqli_result object returned by mysqli_query() when executing a SELECT query. This object represents the result set from the query.

• resulttype (optional): This parameter specifies the type of array to return. It can be one of the following constants:

  • MYSQLI_ASSOC (default if not specified): Returns an associative array where the keys are the column names.
  • MYSQLI_NUM: Returns a numeric array where the indices correspond to the column positions.
  • MYSQLI_BOTH: Returns an array that contains both associative and numeric indexes. This is the default behavior if resulttype is not specified.

Return Values

• On success: Returns an array representing the next row in the result set. The type of array depends on the resulttype parameter.
• On failure: Returns null when there are no more rows to fetch.

Example Usage

Here’s a step-by-step example demonstrating how to use mysqli_fetch_array():

1. Establish a Database Connection

<?php
// Connect to the database
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

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

2. Execute a SELECT Query

// Prepare and execute a SELECT query
$sql = "SELECT username, email FROM users";
$result = mysqli_query($conn, $sql);

3. Fetch and Display Rows

You can fetch rows in different formats depending on the resulttype parameter:

if ($result) {
    // Fetch rows as associative array
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        echo "Username: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
    }

    // Reset result pointer and fetch rows as numeric array
    mysqli_data_seek($result, 0);
    while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
        echo "Username: " . $row[0] . " - Email: " . $row[1] . "<br>";
    }

    // Reset result pointer and fetch rows as both associative and numeric arrays
    mysqli_data_seek($result, 0);
    while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
        echo "Username: " . $row['username'] . " - Email: " . $row[1] . "<br>";
    }

    // Free the result set
    mysqli_free_result($result);
} else {
    echo "Error: " . mysqli_error($conn);
}

4. Close the Database Connection

// Close the connection
mysqli_close($conn);
?>

How It Works

• Fetching Rows: Each call to mysqli_fetch_array() retrieves the next row from the result set. Depending on the resulttype, it returns the row in different array formats.

• Array Types:

  • MYSQLI_ASSOC returns an associative array where the array keys are the column names.
  • MYSQLI_NUM returns a numeric array where the array indices are the column positions.
  • MYSQLI_BOTH returns an array that includes both associative and numeric keys.

• Resetting Pointer: Use mysqli_data_seek() to reset the result pointer if you need to fetch the result set again in a different format.

• Freeing Result Set: After processing the result set, use mysqli_free_result() to free the memory associated with it.

Notes

• Flexibility: mysqli_fetch_array() provides flexibility in how you access the data, making it suitable for various use cases where you might need both associative and numeric array formats.
• Error Handling: Always check the result of mysqli_query() to handle potential errors before attempting to fetch rows.