PHP fetch_row() and mysqli_fetch_row()

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

• fetch_row()
• mysqli_fetch_row()

Both functions are used to fetch the next row of the result set as an enumerated array. The only difference is that fetch_row() is used with PHP mysqli object-oriented script, whereas mysqli_fetch_row() is used with PHP mysqli procedural script.

PHP fetch_row()

The PHP fetch_row() function fetches the next row of a result set as an enumerated array 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 "Connection to the database failed!<BR>";
      echo "Reason: ", $conn -> connect_error;
      exit();
   }
   
   $sql = "SELECT name, email FROM customer";
   
   $result = $conn -> query($sql);
   if($result)
   {
      while($row = $result -> fetch_row())
      {
         echo "Name: ", $row[0];
         echo "<BR>";
         echo "Email: ", $row[1];
         echo "<HR>";
      }
   }
   else
   {
      echo "Something went wrong!<BR>";
      echo "Error Description: ", $conn -> error;
   }
   $result -> free_result();
   $conn -> close();
?>

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

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) by the most recent function call in object-oriented style.

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

In above example, the following code:

$row = $result -> fetch_row()

written as the conditional expression of the while loop, to continue fetching the row until the last one.

Also, since using the SQL code SELECT name, email FROM customer, only the columns named name and email were fetched; therefore, $row[0] refers to name, whereas $row[1] refers to email.

The above example can also be written as:

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

PHP fetch_row() Syntax

The syntax of fetch_row() in PHP is:

$result -> fetch_row()

where $result refers to the result set that was returned while querying the data.

PHP mysqli_fetch_row()

The PHP mysqli_fetch_row() function fetches the next row of a result set as an enumerated array in PHP mysqli procedural style. For example:

<?php
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   if(!mysqli_connect_errno())
   {
      if($result = mysqli_query($conn, "SELECT name, email FROM customer"))
      {
         while($row = mysqli_fetch_row($result))
         {
            echo "Name: ", $row[0];
            echo "<BR>";
            echo "Email: ", $row[1];
            echo "<HR>";
         }
      }
   }
   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_close() function is used to close an opened connection to the MySQL database in procedural style.

Fetch all columns of all rows using PHP mysqli object-oriented

To fetch all columns from all rows, just change the SQL code to SELECT * FROM customer. Now if you write $row[0], then it will refer to the first column of all rows, which is id in my case. Here is the complete script:

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

The output should be:

Name: Olivia
Age: 28
Email: codescracker.com@gmail.com

Name: Charlotte
Age: 24
Email: charloette@xyz.com

Name: Lucas
Age: 42
Email: newmail@xyz.com

Name: Sophia
Age: 29
Email: sophia@xyz.com

Name: Benjamin
Age: 31
Email: benjamin@xyz.com

Since name, age, and email are available at the second, third, and fourth column numbers, therefore, I have changed the index. Also, because indexing in PHP arrays, by default, starts with 0, index number 1 refers to the second column.

Fetch all columns of all rows using the PHP mysqli procedural

<?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_row($result))
         {
            echo "Name: ", $row[1];
            echo "<BR>";
            echo "Age: ", $row[2];
            echo "<BR>";
            echo "Email: ", $row[3];
            echo "<HR>";
         }
         mysqli_free_result($result);
      }
   }
   mysqli_close($conn);
?>

You will get the same exact output as the previous one.

Note: You can change the SQL code to fetch different data. You can also specify another code to get some specified data in a specified format. It is all about the SQL code. But the thing is, the rest of the process will be the same: fetch and get the data from the database, and then display it on the web.

PHP Online Test