How to Loop PHP MySQLi Fetch Array Function
MySQLi fetch function is used to access data from the database server. After fetching the data, you can also iterate over the MySQLi query.
In this article, we’ll see the use of the mysqli_fetch_array() function and the way to iterate over the accessed data.
This extension was introduced with PHP version 5.0.0, and the aim for designing it is to work with MySQL version 4.1.13 or above. We’re using PHP Version 7.4.1 and phpMyAdmin in this tutorial.
You can download XAMPP from its official website. (You don’t have to install MySQL and PHP separately if you have XAMPP).
Iteration Over mysqli_fetch_array() Function
mysqli_fetch_array() is used to retrieve data of the current row from the database using the $result as the first parameter, save the output as an associative array, numeric array, or both (depends on the second parameter). Right now, we have the following data in our students’ database.
mysqli_fetch_array() Function Using MYSQLI_NUM Mode
Let’s write the following program to read data from the students’ database named db_students using the mysqli_fetch_array() function. The following code will connect with the database and display the failure message if it fails.
After a successful connection with the database, it will read the records using the mysqli_query function and save them into the $result variable. mysqli_fetch_array() will take that result variable and MYSQLI_NUM (it behaves like the mysqli_fetch_row() function) as a parameter to display the current row as a numeric array where the indexes range from 0 to n-1.
Example Code:
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_students";
$connection = mysqli_connect($host, $username, $password, $database);
if (mysqli_connect_errno()) {
echo "Database connection failed.";
}
$sql = "SELECT * FROM tb_students";
$result = mysqli_query($connection, $sql);
$row = mysqli_fetch_array($result,MYSQLI_NUM);
print_r($row)
?>
Output:
mysqli_fetch_array() Function Using MYSQLI_ASSOC Mode
Here, the mysqli_fetch_array() function behaves like the mysqli_fetch_assoc() due to parameter MYSQLI_ASSOC, and the column names of the table will be displayed as array’s indexes. Practice the following code and observe the output.
Example Code:
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_students";
$connection = mysqli_connect($host, $username, $password, $database);
if (mysqli_connect_errno()) {
echo "Database connection failed.";
}
$sql = "SELECT * FROM tb_students";
$result = mysqli_query($connection, $sql);
$row = mysqli_fetch_array($result,MYSQLI_ASSOC);
print_r($row)
?>
Output:
mysqli_fetch_array() Function Using MYSQLI_BOTH Mode
Using the mysqli_fetch_array() function with parameter MYSQLI_BOTH will store the data into an array that we can access using column names and column indexes. Practice the following code and see the output.
Example Code:
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_students";
$connection = mysqli_connect($host, $username, $password, $database);
if (mysqli_connect_errno()) {
echo "Database connection failed.";
}
$sql = "SELECT * FROM tb_students";
$result = mysqli_query($connection, $sql);
$row = mysqli_fetch_array($result,MYSQLI_BOTH);
print_r($row)
?>
Output:
Loop Over mysqli_fetch_array() Function
We’ll use the following code to iterate over the mysqli_query and compare the output with the students’ table records.
Keep in mind that you can loop all modes of mysqli_fetch_array(), MYSQLI_ASSOC, MYSQLI_NUM, and MYSQLI_BOTH. You must pass it as a parameter while using mysqli_fetch_array().
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_students";
$connection = mysqli_connect($host, $username, $password, $database);
if (mysqli_connect_errno()) {
echo "Database connection failed.";
}
$sql = "SELECT * FROM tb_students";
$result = mysqli_query($connection, $sql);
$std_num=0;
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
echo "Student Number ".$std_num."<br>";
echo "ID: ".$row['ID']."<br>";
echo "First Name: ".$row['FIRST_NAME']."<br>";
echo "Last Name: ".$row['LAST_NAME']."<br>";
echo "Age: ".$row['AGE']."<br>";
echo "<br><br>";
$std_num++;
}
?>
Output:
You can see and compare ID, FIRST_NAME, LAST_NAME, AGE with the following table named tb_students.
Conclusion
The above discussion concluded that the mysqli_fetch_array() function helps us retrieve data from the database.
Depending on our needs and requirements, we can use its output mode, whether to access values via column index or column names, or both. Then, we can loop the results to see each record of the table.
