How to Loop PHP MySQLi Fetch Array Function

Mehvish Ashiq Feb 02, 2024
  1. Iteration Over mysqli_fetch_array() Function
  2. Conclusion
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.

loop mysqli fetch array function - data

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:

loop mysqli fetch array function - fetch data using mysqli_num mode

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:

loop mysqli fetch array function - fetch data using mysqli_assoc mode

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 mysqli fetch array function - fetch data using mysqli_both mode

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:

loop mysqli fetch array function - loop

You can see and compare ID, FIRST_NAME, LAST_NAME, AGE with the following table named tb_students.

loop mysqli fetch array function - data

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.

Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

Related Article - MySQL PHP