PHP fetch_array() and mysqli_fetch_array()

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

• fetch_array()
• mysqli_fetch_array()

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

PHP fetch_array()

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

<?php
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "codescracker";
   
   $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 the above PHP example on fetch_array() is shown in the snapshot given below:

The data produced in the above output is stored in the database.

Note: The mysqli() function 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 or return the error code (if any) from the last connect call in object-oriented style.

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

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

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

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

Note: The close() function 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", "", "codescracker");
   
   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 the fetch_array() function in PHP is:

$result -> fetch_array(type)

The "type" parameter is optional and is used to define the type of row to return. Here are the three values that we can use to define this parameter:

• MYSQLI_BOTH: the default value, used to return the row result as an associative as well as a numeric array.
• MYSQLI_ASSOC: return the row result as an associative array.
• MYSQLI_NUM: return the row result as a numeric array.

For example:

<?php
   $conn = new mysqli("localhost", "root", "", "codescracker");
   
   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 the 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 for each row using column number 3. And at column number 3, the field email is available.

But if we use the other two, say MYSQLI_ASSOC or MYSQLI_NUM, then we need to access the field only using the defined type. For example, MYSQLI_ASSOC cannot be used to access the field using numeric data, whereas MYSQLI_NUM cannot 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, a numeric array, or both in PHP mysqli procedural style. For example:

<?php
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   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() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

Note: The mysqli_query() function is used to perform queries on the MySQL database in procedural style.

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

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

PHP mysqli_fetch_array() Syntax

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

mysqli_fetch_array(result, type)

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

PHP Online Test