PHP free_result() and mysqli_free_result()

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

• free_result()
• mysqli_free_result()

Both functions are used to free the stored result memory. The only difference is, the free_result() is used with PHP MySQLi object-oriented script, whereas the mysqli_free_result() is used with PHP MySQLi procedural script.

PHP free_result()

The PHP free_result() function frees the stored result memory, for the specified/given statement handle, 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 name, age FROM customer";
   
   $result = $conn -> query($sql);
   if($result)
   {
      while($row = $result -> fetch_row())
      {
         echo "Name: ", $row[0];
         echo "<BR>";
         echo "Age: ", $row[1];
         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 free_result() is shown in the snapshot given below:

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 fetch_row() is used to to fetch and return the result as an enumerated array, 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 name, age FROM customer";
   
      if($result = $conn -> query($sql))
      {
         while($row = $result -> fetch_row())
         {
            echo "Name: ", $row[0];
            echo "<BR>";
            echo "Age: ", $row[1];
            echo "<HR>";
         }
         $result -> free_result();
      }
   }
   $conn -> close();
?>

Since the free_result() is used to free the stored result from the memory, therefore the question is, what if we use free_result() before retrieving all the rows ?
Let's find out the answer using the example given below:

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

Note the free_result() inside the while loop. Now the output should be:

That is, after using the free_result() associated with $result, the stored result has been freed, therefore we can not fetch and get further row from the database.

PHP mysqli_free_result()

The PHP mysqli_free_result() function frees the stored result memory, for the specified/given statement handle, in PHP MySQLi procedural style. For example:

<?php   
   $conn = mysqli_connect("localhost", "root", "", "fresherearth");
   
   if(!mysqli_connect_errno())
   {
      $sql = "SELECT name, age FROM customer";
   
      if($result = mysqli_query($conn, $sql))
      {
         while($row = mysqli_fetch_row($result))
         {
            echo "Name: ", $row[0];
            echo "<BR>";
            echo "Age: ", $row[1];
            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_fetch_row() is used to to fetch and return the result as an enumerated array, 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 Online Test