Where to create and close database connection?

Question

I know we always have to close db connection at the end. Let's I have a class and there is four methods and each consists of db queries. Now my question is

Should I create a connection and close it in every different methods or create the connection in the constructor of the class? If I create the connection in the constructor then where will I close it. Because If I close that in any function no other method can access it.

If I create new connection for every method then no of connections may be very high. Then where will I create and close the function proper way?

Instead of manually managing your database connections, consider using [database connection pooling](http://stackoverflow.com/questions/4041114/what-is-database-pooling). — azurefrog, Jul 15 '16 at 19:37

gladiola · Answer 1 · 2016-07-15T20:05:35.957

If you open it, then you need to close it.

As long as you close the connection, you can close it where you want. It seems like maybe your closing woes are more about the design of the procedures of your program. Just because the database call uses a connection doesn't mean it has to be opened and closed right there with it.

Say you might want to reuse a $mysqli variable that's holding a connection. You can pass that connection to a function and either return it or close it inside. In this way, you can isolate the main activity of the database call in the body of the function without having to worry too much about the opening and closing of everything.

Using that technique, it's possible to reuse a connection for multiple queries, contained each inside their own method. Maybe you would be better off opening a class and passing a $mysqli connection to it, as needed.

This depends on your program and your choices about how it will work.

If you are developing a program, and you're curious about the impact of your opening and closing actions, you can call http://php.net/manual/en/mysqli.stat.php or a similar function. If you are opening many threads and not closing them, then you would see the thread count escalate in the answer from mysqli_stat().

The calls may not close, for example, if you have a poorly designed query that runs away from you; the scope of the connection object should close naturally with the end of the script.

score 0 · Answer 2 · answered Jul 15 '16 at 19:41

0

Because of the reference-counting system of php it's not necessary to close the connection, it will be freed automatically (unless you open persistent db links), so it shouldn't cause a headache.

You should open the connection only once btw.

answered Jul 15 '16 at 19:41

Astig NaGahum

31
2

score 0 · Answer 3 · answered Jul 15 '16 at 22:09

Please take into consider transaction concept. Say for example you methods are being called in a sequence, it makes sense to open a connection and keep it open If there is delay in call of other methods, i would not advice you to keep connection suspended especially if multiple people are going to access the application

Abhas · Answer 4 · 2016-07-15T22:28:11.667

Best way would be to implement classes and extend it to your functional class containing methods. Constructor of the extended class can call to the connection function and the destructor can call to disconnect whenever the object is set to NULL.

Or

As your question states, have two functions in base class having construction and other having destruction. Call to the constructor function before using your connection and destroy it after using. Example would be:

   class bar{
    protected function connect(){
    #connect to db
    }
    protected function disconnect(){
    #disconnect db
    }

    class foo extends bar{

    function func1 () {
    $this->connect();
    #Code
    $this->disconnect();
    }

    function func2() {
    $this->connect();
    #Code
    $this->disconnect();
    }

    function func3() {
    $this->connect();
    #Code
    $this->disconnect();
    }

    function func4() {
    $this->connect();
    #Code
    $this->disconnect();
    }
?>

Where to create and close database connection?

4 Answers4