fetch_array

In this article, we will focus on the mysqli_fetch_array() function in PHP, which is used to fetch a row from a MySQLi result set as an associative, numeric, or combined array. We will provide an overview of the function, its parameters, and practical examples.

Introduction to the mysqli_fetch_array() function

The mysqli_fetch_array() function is a built-in PHP function that retrieves a row from a MySQLi result set. It returns the row as an associative array, a numeric array, or both, depending on the result_type parameter. This is particularly useful when you need to iterate over query results and access columns by name or index.

It works on the result returned by mysqli_query(). Each call advances an internal pointer to the next row and returns that row; when there are no rows left it returns null (the loop-ending value). It is the most flexible of the MySQLi fetch functions: mysqli_fetch_assoc() always returns name keys, mysqli_fetch_row() always returns index keys, and mysqli_fetch_array() lets you choose either or both.

How to use the mysqli_fetch_array() function

Using mysqli_fetch_array() requires a valid MySQLi result set. It is typically used inside a while loop to iterate through all rows. Here is a complete example with basic error handling:

Basic usage with a while loop

<?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) {
    die("Query failed: " . mysqli_error($mysqli));
}

// Fetch rows as an associative array
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    echo $row['column1'] . " - " . $row['column2'] . "<br>";
}

mysqli_close($mysqli);
?>

In this example, we establish a connection and execute a query. We check for connection and query errors to prevent silent failures. The while loop calls mysqli_fetch_array() repeatedly, returning false when no more rows exist. The MYSQLI_ASSOC constant ensures columns are accessed by name.

Fetch modes and advanced usage

The second parameter of mysqli_fetch_array() controls how the row is returned. There are three constants you can use:

• MYSQLI_ASSOC – Returns an associative array (column names as keys).
• MYSQLI_NUM – Returns a numeric array (column indices as keys).
• MYSQLI_BOTH – Returns both associative and numeric arrays. This is the default mode if the second parameter is omitted.

Here is how to fetch a row as a numeric array:

Numeric array fetch

<?php
// ... connection and query setup ...
while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
    echo $row[0] . " - " . $row[1] . "<br>";
}
?>

When using MYSQLI_BOTH, each column appears twice in the array: once by name and once by index. This can be useful for legacy code but increases memory usage, so MYSQLI_ASSOC or MYSQLI_NUM is generally preferred for modern applications. If you only ever need one form, prefer the dedicated mysqli_fetch_assoc() or mysqli_fetch_row() helpers, which skip building the extra keys.

Object-oriented style

mysqli_fetch_array() also has an object-oriented form. If you obtained the result from $mysqli->query(), call the method on the result object instead of passing the result to the procedural function:

OOP equivalent

<?php
$result = $mysqli->query("SELECT * FROM my_table");
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
    echo $row['column1'] . " - " . $row['column2'] . "<br>";
}
?>

Both styles call the same underlying behavior; pick whichever matches the rest of your codebase. To map each row to an object with named properties instead of an array, use mysqli_fetch_object(), and to pull every row at once into a 2D array, see mysqli_fetch_all().

Conclusion

The mysqli_fetch_array() function provides flexible row retrieval from MySQLi result sets. By understanding its fetch modes and incorporating basic error handling, you can write more robust and efficient database queries in PHP.

Practice