PHP fetch_array() and mysqli_fetch_array()

This article is created to cover the two functions of PHP, that are:

• fetch_array()
• mysqli_fetch_array()

Both functions are used to fetch the next row of result set as an associative array or as a numeric array, or as both. The only difference is, the fetch_array() uses with PHP MySQLi object-oriented script, whereas the mysqli_fetch_array() uses with PHP MySQLi procedural script.

PHP fetch_array()

The PHP fetch_array() function fetches the next row of a result set as an associative array or as a numeric array, or as both, in PHP MySQLi object-oriented style. For example:

<?php
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "fresherearth";
   
   $conn = new mysqli($server, $user, $pass, $db);
   
   if($conn -> connect_errno)
   {
      echo "Database connection failed!<BR>";
      echo "Reason: ", $conn -> connect_error;
      exit();
   }
   
   $sql = "SELECT * FROM customer";
   
   $result = $conn -> query($sql);
   if($result == true)
   {
      while($row = $result -> fetch_array())
      {
         echo "Name: ", $row['name'];
         echo "<BR>";
         echo "Email: ", $row['email'];
         echo "<HR>";
      }
      $result -> free_result();
   }
   else
   {
      echo "Something went wrong!<BR>";
      echo "Error Description: ", $conn -> error;
   }
   $conn -> close();
?>

The output produced by above PHP example on fetch_array() is shown in the snapshot given below:

The data produced in above output, are stored in the database.

Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.

Note - The new keyword is used to create a new object.

Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.

Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.

Note - The query() is used to perform query on the MySQL database, in object-oriented style.

Note - The free_result() is used to free the stored result, in object-oriented style.

Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.

Note - The close() is used to close an opened connection, in object-oriented style.

The above example can also be written as:

<?php
   $conn = new mysqli("localhost", "root", "", "fresherearth");
   
   if(!$conn -> connect_errno)
   {
      $sql = "SELECT * FROM customer";
      if($result = $conn -> query($sql))
      {
         while($row = $result -> fetch_array())
         {
            echo "Name: ", $row['name'];
            echo "<BR>";
            echo "Email: ", $row['email'];
            echo "<HR>";
         }
         $result -> free_result();
      }
   }
   $conn -> close();
?>

PHP fetch_array() Syntax

The syntax of fetch_array() function in PHP, is:

$result -> fetch_array(type)

The type parameter is optional, used to define the type to return the row. Here are the three values, that we can use to define this parameter (the type parameter):

• MYSQLI_BOTH - This is the default value, used return the row result as an associative as well as numeric array
• MYSQLI_ASSOC - Used to return the row result as an associative array
• MYSQLI_NUM - Used to return the row result as a numeric array

For example:

<?php
   $conn = new mysqli("localhost", "root", "", "fresherearth");
   
   if(!$conn -> connect_errno)
   {
      $sql = "SELECT * FROM customer";
      if($result = $conn -> query($sql))
      {
         while($row = $result -> fetch_array(MYSQLI_BOTH))
         {
            echo "Name: ", $row['name'];
            echo "<BR>";
            echo "Email: ", $row[3];
            echo "<HR>";
         }
         $result -> free_result();
      }
   }
   $conn -> close();
?>

Notice the $row['name'] and $row[3] in above example. The first one refers to associative, whereas the second one refers to numeric. That is, using the first one, we get the data of each row, whose column name is name. Whereas using the second one, we get the data of each row, using the column number 3. And at column number 3, the field email is available.

But if we use, other two say MYSQLI_ASSOC or MYSQLI_NUM, then we need to access the field, only using the defined type. For example, MYSQLI_ASSOC can not be used to access the field using numeric data, whereas MYSQLI_NUM can not be used to access the field using associative data.

PHP mysqli_fetch_array()

The PHP mysqli_fetch_array() function fetches the next row of a result set as an associative array or as a numeric array, or as both, in PHP MySQLi procedural style. For example:

<?php
   $conn = mysqli_connect("localhost", "root", "", "fresherearth");
   
   if(!mysqli_connect_errno())
   {
      $sql = "SELECT * FROM customer";
      if($result = mysqli_query($conn, $sql))
      {
         while($row = mysqli_fetch_array($result))
         {
            echo "Name: ", $row['name'];
            echo "<BR>";
            echo "Email: ", $row['email'];
            echo "<HR>";
         }
         mysqli_free_result($result);
      }
   }
   mysqli_close($conn);
?>

Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.

Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.

Note - The mysqli_query() is used to perform query on the MySQL database, in procedural style.

Note - The mysqli_free_result() is used to free the stored result, in procedural style.

Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.

PHP mysqli_fetch_array() Syntax

The syntax of mysqli_fetch_array() function in PHP, is:

mysqli_fetch_array(result, type)

Here too, the type works in similar way, as defined in fetch_array() Syntax section.

PHP Online Test