Where to use $ stmt-close () & $ stmt-free_result () - MySQLi PHP?

First let's explain the difference between the two:

$stmt->free_result() frees up memory related to a set of results, while $stmt->close() frees the memory related to a prepared query. Subsequently, calling $stmt->close() will cancel any result that remains.

Basically, calling $stmt->close() will provide the same effect as invoking $stmt->free_result() since it also cancels the result set. But when invoking $stmt->free_result() the memory used by the prepared declaration will not be erased, in which case you must use $stmt->close() .

As to which one to use, there may be situations in which you intend to use the prepared statement you have initialized, but you no longer need the set of results you currently have. In that case, you would call $stmt->close() when you have finished with the prepared statement (you can pass different values to the same prepared query ... that is one of its advantages) and, instead, you would call $stmt->free_result() before executing another instruction.

• Source: $ stmt-> close () vs $ stmt-> free_result () in SO in English

Responding specifically to your question

In the code example that you put, if you look in the PHP Manual , the code is written like this:

 $consulta = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";

/*Antes  de hacer cualquier cosa con $stmt se verifica que no sea falso*/
/*$stmt sería false si hay algún error al llamar el método prepare*/
/*Todo lo que se hace con $stmt se hace dentro de este bloque*/
if ($stmt = $mysqli->prepare($consulta)) {

    /* ejecutar la sentencia */
    $stmt->execute();

    /* vincular las variables de resultados */
    $stmt->bind_result($nombre, $código);

    /* obtener los valores */
    while ($stmt->fetch()) {
        printf ("%s (%s)\n", $nombre, $código);
    }

    /* cerrar la sentencia */
    $stmt->close();
}

 /*Si hay un error preparando la consulta y aquí escribes $stmt->close()*/
 /*Tendrás el error: Exception: Call to a member function close() on boolean*/
 /*Porque $stmt sería false, no la representación de una consulta preparada*/

/* cerrar la conexión a la BD*/
$mysqli->close();

This code releases the $stmt when exiting the loop. When closing $stmt it is not necessary to use free.

You also put a code example using if ... but with fetch the data must be read in a loop, since reading them within a simple if you can not get all the results, in case there is more than one row.

In summary: If you use close when you finish using your prepared query and your data, it would be enough. If you want to use free_result before close , use it. There are debates about whether its use is vital or not. According to this answer the use of free_result does not add anything. You can do a test with memory_get_usage() in several scenarios and determine if it really contributes something. According to the same answer, its use is not vital.

None of the methods you use belong to PDOStatement (list of methods PDOStatement ) however if you you refer to the method PDOStatement::closeCursor and the function mysql_free_result .

Both release the memory but the first is when you use PDO and the other when you use it when you use the mysql api which is obsolete.

PDOStatement::closeCursor you should use it when in a query you do not go through the whole result and you want to execute another query, when the query is very large and you want to free the used memory, when you want to reuse the PDOStatement .

The examples you put are not valid because the structure of the if and the while is incorrect.

if ($stmt->fetch()) {
 echo "Resultados";
} $stmt->close(); // error de sintaxis en estructura de if y $stmt no tiene el metodo close es closeCursor()
 else {
   $stmt->close(); //$stmt no tiene el metodo close es closeCursor()
   echo "No existe resultados";
}

while ($stmt->fetch()) {
  echo "Resultados";
}
$stmt->close(); 
 else { // No es un if el else esta de mas y close no existe
  $stmt->close();
  echo "No existe resultados";
 }

Ideally,

while ($stmt->fetch()) {
  ...
}
$stmt->closeCursor();

and

if ($stmt->fetch()) {
  ...
}
$stmt->closeCursor();

Always use closeCursor, it never hurts. and those would be the ways I recommend.