MySQLi query results: When do I close, free, or both?

Question

I have some questions about using MySQLi queries, and related memory management.

Suppose I have something like this:

$db = new mysqli($dbhost, $un, $ps, $dbname);

$query = "SELECT field1, field2  FROM table1 ";
$results = $db->query($query);

while ($result = $results->fetch_object()) {
    // Do something with the results
}

$query = "SELECT field1, field2 FROM table2 ";
// question 1
$results = $db->query($query);

while ($result = $results->fetch_object()) {
    // Do something with the second set of results
}

// Tidy up, question 2
if ($results) {
    $results->free();
}
if ($db) {
    $db->close();
}

// Question 3, a general one

So, based on the comments in the code above, here are my questions:

1. When I assign the results of the second query to $results, what happens to the memory associated with the previous results? Should I be freeing that result before assigning the new one?

2. Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

3. When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

I ask question 3 because the PHP documentation for mysqli::query has an example that uses close, even though close is not part of mysqli_result (see example 1 in the link above). And in contrast, my normal PHP reference text uses free (PHP and MySQL Web Development, Fourth Edition, Welling and Thomson).

Answer 1

When I assign the results of the second query to $results, what happens to the memory associated with the previous results?

When you execute this:

$results = $db->query($query);

If there was something in $results before, this old content cannot be accessed anymore, as there is no reference left to it.

In such a case, PHP will mark the old content of the variable as "not needed anymore" -- and it will be removed from memory when PHP needs some memory.

This, at least, is true for general PHP variables; in the case of results from an SQL query, though, some data may be kept in memory on the driver-level -- over which PHP doesn't have much control.

Should I be freeing that result before assigning the new one?

I never do that.

Related to 1, when I do clean up at the end, is cleaning up just the last results enough?

When the scripts end:

• The connection to the database will be closed -- which means any memory that might be used by the driver should be freed
• All variables used by the PHP script will be destroyed -- which means the memory they were using should be freed.

So, at the end of the script, there is really no need to free the result set.

When I do try to clean up a result, should I be freeing it as above, should I be closing it, or both?

If you close the connection to the database (using mysqli::close like you proposed), this will disconnect you from the database.

This means you'll have to re-connect if you want to do another query! Which is not good at all (takes some time, resources, ... )

Generally speaking, I would not close the connection to the database until I am really sure that I won't need it anymore -- which means I would not disconnect before the end of the script.

And as "end of the script" means "the connection will be closed" even if you don't specify it; I almost never close the connection myself.

Answer 2

The answers already provided are good, but I wanted to add one point and clarify another.

First, the clarification. Regarding the use of the close() method, it's important to note that the OP was referencing the close() method of the mysqli_result class, not the mysqli class. In the result class, the close() method is simply an alias to the free() method, as shown in the documentation, while in the mysqli class, it closes the connection. Thus, it's okay to use close() on the result in place of free() if desired.

Second, the additional point. As has already been pointed out, PHP's execution pattern means that everything will eventually be cleaned up behind you, and thus, you don't necessarily need to worry about releasing memory. However, if you're allocating a lot of result objects, or if you're allocating particularly big result objects (e.g., fetching a large amount of data), then you should probably free the memory when you're done to prevent problems further down the path of execution. This becomes especially important as your application starts to get more traffic, where the total amount of memory tied up across sessions can quickly become significant.