php – mysqli_free_result () VS mysqli_stmt_free_result() differences and usage?-ThrowExceptions

Exception or error:

What are the differences and specific use cases of these two functions? Here’s what I found:

mysqli_free_result — Frees the memory associated with a result

mysqli_stmt_free_result — Frees stored result memory for the given statement handle

An example will be nice.

How to solve:

These are two different functions, but you don’t need to use any of them.

mysqli_free_result

mysqli_free_result() is the procedural equivalent of $result->free(). All it does is it unsets the results from the variable. The object still exists, but becomes unusable.

$id = 1;
$stmt = $mysqli->prepare('SELECT * FROM student_detail WHERE ID=?');
$stmt->bind_param('s', $id);
$stmt->execute();
$result = $stmt->get_result();

$result->free();

var_dump($result->fetch_assoc()); // error

It also has 2 aliases. These 4 are all the same thing:

$result->free();
$result->close();
$result->free_result();
mysqli_free_result($result);

mysqli_stmt_free_result

Does the same thing, but with the statement itself. It is the equivalent of $stmt->free_result()

$id = 1;
$stmt = $mysqli->prepare('SELECT * FROM student_detail WHERE ID=?');
$stmt->bind_param('s', $id);
$stmt->execute();
$result = $stmt->store_result();

$stmt->free_result();

var_dump($stmt->fetch()); // false

You can assume that if you ever find yourself needing to use any of them, then it means you have probably done something wrong.

For example, some people claim it saves memory for your server when you free the result once you are done with it. In reality you should encapsulate your database queries in such way that the mysqli_result lives only as long as you need it. Take a look at one method I have written a while ago:

public function safeQuery(string $sql, array $params = []): ?array {
    $stmt = $this->prepare($sql);
    if ($params) {
        $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    }
    $stmt->execute();
    if ($result = $stmt->get_result()) {
        return $result->fetch_all(MYSQLI_BOTH);
    }
    return null;
}

It executes prepared statement, gets the result, fetches the records and the mysqli_result is alive as long as it is needed and not a moment more.

Answer:

They’re equivalent, it depends on how you created the results in the first place.

If you use a prepared statement and then use $stmt->store_result(), you would free the results using $stmt->free_result().

If you perform an ordinary query like $result = $conn->query(...), you would free it using $result->free_result().

Leave a Reply

Your email address will not be published. Required fields are marked *