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

Question

Hello all,

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 docs for mysqli::query has an example that uses close, even though close is not part of mysqli_result (see example 1 at http://ca3.php.net/manual/en/mysqli.query.php). And in contrast, my normal php reference text uses free (PHP and MySQL Web Development, Fourth Edition, Welling and Thomson).

Thanks in advance as always!

Answer 1

The general PHP way is not to close any resource opened. Everything will be closed at the script end automatically. The only case when you have to care of manual close - if you have long heavy code to run, which is not very common for PHP

Answer 2

I'll try to provide some elements of answer, each time quoting the question before answering it

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 to it left.

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 -- on 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 DB 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 DB.

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