PHP how to check if a MySQLi query needs to be closed?

Question

I created a simple MySQLi class for a project I'm working on to make it easier and more streamlined to pull from and push to the DB. (also to get more familiar with OOP in PHP)

A problem I keep running into is in an effort to make it as efficient as possible. I try to close / free every query / statement / result set. In doing so I get the following error a lot:

Warning: mysqli_result::close(): Couldn't fetch mysqli_result

I think I get the above specifically because it's trying to close the query twice in a row.

Currently my class can make prepared and unprepared statements. So I try to close queries / statements in 2 places. I check when preparing a statement if I have already prepared a statement, if so I need to close the old one before I make a new one and lastly in the destructor of the class.

I realize I could just close the query / statement after I've pulled and stored the result but this seems to be messy for a few reasons. For one it removes the possibility of reusing the prepared statement. As well as disallowing me to pull some information about the query after it's been run such as affected rows and the like.

I know I could just store this info for every query and what not but it just seems like the proper way to go about this problem would be to close / free the query / statement if I need to make another one, and again at the end of the script.

I have tried looking around and reading up on how I should properly handle this task but I have been unable to come up with anything.

So my question is, is there anyway to test if a query or statement needs to be closed or freed? Or is there a better way I should be attempting to tackle this issue?

Thanks for any help or insight anyone can offer.

Answer 1

I think that if there are no rows left in the result set, it should be closed automatically. So, if you iterate through the whole result set, there's no need to call mysqli_result. However, you should close it if you fetch, let's say, only the first row from a result set containing about 100 rows.

Answer 2

You could set it to null after you close it, and check for null in the destructor before closing it there.

Obviously, you'd only want to null a prepared statement when you're sure you're done using it.

Answer 3

You could use RAII or a factory class+reference counting to manage the statements. That way you don't need to explicitly check when it's time to close anything. Without seeing the details of how you're using your wrapper, it's hard to go into detail. Objective-C's accessor pattern may also be of use, which would look something like:

private function unsetStatement() {
    if (isset($this->_stmt)) {
        $this->_stmt->close();
        unset($this->_stmt);
    }
}
function getStatement() {
    return $this->_stmt;
}
function setStatement($stmt) {
    $this->unsetStatement();
    $this->_stmt = $stmt;
}

You'd only ever access the statement member field through the above functions, even within the class. Actually, it looks more like:

-(Statement *)statement {
    return stmt_;
}
-(void)setStatement:(Statement *)stmt {
    [stmt_ release];
    stmt = [stmt retain];
}

Answer 4

This is what I use. If there are any results in the statement, I know I need to free the result:

if($stmt->num_rows() != 0)
{
    $stmt->free_result();

    return FALSE;
}