This function allows you to fetch a result row as an associative array, a numeric array, or both. This article will delve into the details of mysqli_fetch_array(), providing examples and use cases to illustrate its utility.
Understanding mysqli_fetch_array()
The mysqli_fetch_array() function fetches a result row as an associative array, a numeric array, or both. Its basic syntax is:
mysqli_fetch_array(result, array_type);
result: The result resource that is being evaluated. This result comes from a call to mysqli_query(), mysqli_store_result(), or mysqli_use_result().
array_type: This optional parameter specifies what type of array to return. It can be one of the following values:
MYSQLI_ASSOC (fetches associative array)
MYSQLI_NUM (fetches numeric array)
MYSQLI_BOTH (default, fetches both associative and numeric array)
Basic Usage of mysqli_fetch_array()
Let’s start with a simple example. Consider a table named users with the following structure:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT NOT NULL
);
Example 1: Fetching Data as an Associative Array
In this example, we fetch data from the users table and print it as an associative array.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
In this example, we use MYSQLI_ASSOC to fetch each row as an associative array. The keys of the array correspond to the column names of the fetched data.
Example 2: Fetching Data as a Numeric Array
In this example, we fetch data from the users table and print it as a numeric array.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
echo "ID: " . $row[0] . " - Username: " . $row[1] . " - Email: " . $row[2] . " - Age: " . $row[3] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
In this example, we use MYSQLI_NUM to fetch each row as a numeric array. The keys of the array are numeric indices, starting from 0.
Example 3: Fetching Data as Both Associative and Numeric Arrays
In this example, we fetch data from the users table and print it as both an associative and numeric array.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
echo "ID: " . $row[0] . " - Username: " . $row[1] . " - Email: " . $row[2] . " - Age: " . $row[3] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
In this example, we use MYSQLI_BOTH to fetch each row as both an associative and numeric array. This provides the flexibility to access data using either the column names or numeric indices.
Use Cases for mysqli_fetch_array()=
Use Case 1: Displaying Data in an HTML Table
One common use case for mysqli_fetch_array() is to display data in an HTML table. This is especially useful for creating dynamic web pages that display database content.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table border='1'><tr><th>ID</th><th>Username</th><th>Email</th><th>Age</th></tr>";
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["username"] . "</td><td>" . $row["email"] . "</td><td>" . $row["age"] . "</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
This code retrieves data from the users table and displays it in an HTML table. Each row of the result set is fetched using mysqli_fetch_array() and printed inside the table rows.
Use Case 2: Processing Data for Further Operations
Sometimes, you may need to fetch data from a database, process it, and then perform additional operations based on the retrieved data. Here’s an example where we calculate the average age of users:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT age FROM users";
$result = $conn->query($sql);
$total_age = 0;
$count = 0;
if ($result->num_rows > 0) {
while($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
$total_age += $row[0];
$count++;
}
$average_age = $total_age / $count;
echo "The average age of users is: " . $average_age;
} else {
echo "0 results";
}
$conn->close();
?>
In this example, we fetch the age column from the users table, sum the ages, and calculate the average age.
Use Case 3: Fetching Data for JSON API Responses
When building APIs, it’s common to fetch data from a database and return it as a JSON response. Here’s an example of how to do this using mysqli_fetch_array():
<?php
header('Content-Type: application/json');
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
$users = array();
if ($result->num_rows > 0) {
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
$users[] = $row;
}
echo json_encode($users);
} else {
echo json_encode(array());
}
$conn->close();
?>
This code fetches data from the users table, stores it in an array, and returns it as a JSON response. This is particularly useful for building RESTful APIs.