Properly closing database connections in PHP

Question

I'm facing some doubts in PHP database connections. Since I can't just put a large try/catch/finally block on my method (Java style), what's the best approach to properly closing all connections and prepared statements when size/logic tends to increase? Considering the next method, is everything done right?

public function createRegister($register) {
        $this->openConnection();

        $query = "INSERT INTO register (username, password, email, confirmationToken) VALUES (?, ?, ?, ?)";
        $result = $this->mysqli->query($query);

        if ($statement = $this->mysqli->prepare($query)) {  
            $statement->bind_param("ssss", $register->username, $register->passwordHash, $register->email, $register->confirmationToken);

            if (!$statement->execute()) {
                $this->closeConnection();
                throw new DAOException("Failed to execute statement: " . $statement->error);
            }

            $statement->close();

        } else {
            $this->closeConnection();
            throw new DAOException("Failed to prepare statement: " . $this->mysqli->error);
        }

        $this->closeConnection();
    }

do you really want to open/close connection for each query? and why are you mixing code for different layers? you should encapsulate the query execution into a separate function.. — Karoly Horvath, Feb 28 '12 at 18:13
@yi_H: Excellent point. In my response, I mentioned the ability to recycle connections via singleton/factory patterns. — Mike Purcell, Feb 29 '12 at 21:28

score 0 · Accepted Answer · edited May 23 '17 at 10:34

You can still use try/catch in PHP:

public function createRegister($register) {

    $this->openConnection();

    $query = "INSERT INTO register (username, password, email, confirmationToken) VALUES (?, ?, ?, ?)";

    try {

        // This line is not needed
        // $result = $this->mysqli->query($query);

        if ($statement = $this->mysqli->prepare($query)) {

            $statement->bind_param("ssss", $register->username, $register->passwordHash, $register->email, $register->confirmationToken);

            if (!$statement->execute()) {                   
                throw new DAOException("Failed to execute statement: " . $statement->error);
            }

            $statement->close();

        } else {
            throw new DAOException("Failed to prepare statement: " . $this->mysqli->error);
        }
    } catch (Exception $e) {

        if ((isset($statement)) && (is_callable(array($statement, 'close')))) {
            $statment->close();
        }

        $this->closeConnection();

        throw $e;
    }

    $this->closeConnection();
}

This works well for establishing a connection for one specific task, but what if you want to share the same connection for multiple tasks that also need access to the same schema? You may want to consider a more advanced solution using a singleton/factory pattern for creating and access database connections. I posted such an example as a solution to another question. It is a bit more advanced but once you get your head around it, it is more performant.

Where should I properly close my $statement? – Rui Feb 28 '12 at 15:06 — Rui, Feb 28 '12 at 15:06
@Rui: What did you end up doing? – Mike Purcell Mar 02 '12 at 21:25 — Mike Purcell, Mar 02 '12 at 21:25

Properly closing database connections in PHP

1 Answers1