MySQLi query results: Best approach, do you close, free, both?

Question

I have some questions about using MySQLi, queries, and related memory management. The code here is just to clarify my questions, so don't dump on it for error checking, etc. I know that needs to be done :)

Suppose I have something like this:

@ $db = new mysqli($dbhost, $un, $ps, $dbname);
$query = "SELECT field1, field2 ".
         "FROM table1 ".
         "WHERE field1={$some_value}";
$results = $db->query($query);

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

$query = "SELECT field1, field2 ".
         "FROM table2 ".
         "WHERE field1={$some_value2}";
// 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 mysqli::query). 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 -- but, quoting the manual page of mysqli_result::free:

Note: You should always free your result with mysqli_free_result(), when your result object is not needed anymore

It probably doesn't matter for a small script... And the only way to be sure would be to test, using memory_get_usage before and after calling that method, to see whether there is a difference or not.

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 probably really no need to free the resultset.

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.

Which 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.