PHP free_result() and mysqli_free_result()

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

• free_result()
• mysqli_free_result()

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

PHP free_result()

The PHP free_result() function frees the stored result memory for the specified or given statement handle 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 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 the above PHP example on free_result() 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 fetch_row() function is used to fetch and return the result as an enumerated array 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 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 free_result() is used to free the stored result from the memory, 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", "", "codescracker");
   
   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 cannot fetch and get further rows from the database.

PHP mysqli_free_result()

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

<?php   
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   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() 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_fetch_row() function is used to fetch and return the result as an enumerated array 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 Online Test