In this tutorial, we will learn how to use the mysqli_fetch_array function in PHP. This function allows us to retrieve rows from a MySQL database and store them as a PHP array. The function enables you to store the values as an associative array, a numerical array, or a combination of both. You can then manipulate the array as usual. Let us dive in.

Function Syntax

The following snippet shows the procedural syntax for the mysqli_fetch_array function:

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

The function parameters are as shown:

result – this parameter allows you to specify the mysql_result object returned by the mysqli_query(), mysqli_store_result(), mysqli_stmt_get_result(), or mysqli_use_result() functions.
mode – the mode parameter defines the type of array used to store the resulting values. Accepted values include:
1. MYSQLI_ASSOC – associative array.
2. MYSQLI_NUM – numerical array.
3. MYSQLI_BOTH – a combination of the associative and numerical array.

The function will then return the array with the fetched rows. If there are no fetched rows, the function returns null or false on failure.

Example Usage

Let us discuss an example of using the mysqli_fetch_array() function. Start by creating a sample database, table, and data as shown in the query below:

CREATE DATABASE src;
USE src;
DROP TABLE IF EXISTS stack_mapping;
CREATE TABLE stack_mapping(
id INT AUTO_INCREMENT PRIMARY KEY,
server_name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
installed_version VARCHAR(50),
tool_id INT
);
INSERT INTO stack_mapping(server_name, address, installed_version, tool_id)
VALUES ('SQL Server', 'localhost:1433', '15.0', 1),
('Elasticsearch', 'localhost:9200', '8.4', 2),
('Redis', 'localhost:6379', '6.0', 3),
('PostgreSQL', 'localhost:5432', '14.5', 4),
('MySQL', 'localhost:3306', '8.0', NULL);

The resulting table is as shown:

The following example shows how to use PHP mysqli_fetch_array function to return the rows from the table above as an array.

Start by creating a PHP file:

$ touch fetch.php

Edit the file:

$ vim fetch.php

Add the code as shown:

<?php
$mysqli = mysqli_connect("localhost:3306", "root", "", "src");
if (mysqli_connect_errno()) {
die("Could not connect");
}

$query = "SELECT server_name, address FROM stack_mapping";
$result = mysqli_query($mysqli, $query);

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
printf("%s (%s)\n", $row["server_name"], $row["address"]);
}
mysqli_free_result($result);
?>

Finally, run the script with PHP:

php fetch.php

This should return the rows as an associative array, also known as a dictionary in other programming languages.

NOTE: Note we are accessing the values of the dictionary using the row name (dictionary key).

An example output is as shown:

SQL Server (localhost:1433)
Elasticsearch (localhost:9200)
Redis (localhost:6379)
PostgreSQL (localhost:5432)
MySQL (localhost:3306)

To return the values as a numerical array, we can use the query:

<?php
$mysqli = mysqli_connect("localhost:3306", "root", "", "src");
if (mysqli_connect_errno()) {
die("Could not connect");
}

$query = "SELECT server_name, address FROM stack_mapping";
$result = mysqli_query($mysqli, $query);

while($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
printf("%s (%s)\n", $row[0], $row[1]);
}
mysqli_free_result($result);
?>

Similarly, the code above should return the rows as:

SQL Server (localhost:1433)
Elasticsearch (localhost:9200)
Redis (localhost:6379)
PostgreSQL (localhost:5432)
MySQL (localhost:3306)

Finally, to get the results as both associative and numerical arrays.

<?php
$mysqli = mysqli_connect("localhost:3306", "root", "", "src");
if (mysqli_connect_errno()) {
die("Could not connect");
}

$query = "SELECT server_name, address FROM stack_mapping";
$result = mysqli_query($mysqli, $query);

while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
printf("%s (%s)\n", $row[0], $row["address"]);
}
mysqli_free_result($result);
?>

Conclusion

In this article, you learned how to use the mysqli_fetch_array function in PHP to fetch the rows from a database as an array.

PHP mysqli_fetch_array

Function Syntax

Example Usage

Conclusion

About the author

John Otieno