mysql - When should I close a database connection in PHP?

Question

I'm nothing like a php developer but I do have to use it and I'm not really aware on how PHP handle memory allocation during session.

I'm working on an application that ask for an HTTP authentication, once you're logged in you can manipulate data through a nice interface.
Someone told me on another post, that I shouldn't close the mysql data connection after every execution, but I don't know how this connection stay is memory during using this app because in the server side I have no idea what PHP keeps in memory or not.

So my question is should I use a singleton to connect to the db and never close it (because I will never know when the application is not in use. Or should I stand with what I do today: opening connection --> execute statement --> close connection.

PS: I'm using mysqli

Edit 1:
My application is design with MVC pattern meaning :

|''''''''''|      |'''''''''''''|      |''''''''''''''|
| view.php |  ==> | control.php |  ==> | database.php |
|----------|      |_____________|      |______________|

That pattern allow the view to interact with data only through the control.php which then call a function from database.php to SELECT or EDIT data. with the explanation you give me, I should put:

public function __destruct(){
    mysql_close($this->connection);
}

inside database.php, but that page is load when there's a need to select or modify data, so it's only executed for a short time, meaning it will still close the connection at the end of the request.

Which gives a more precise question of where should I put the peace of code you provide, or does my pattern relevant in PHP ?

Answer 1

Never create a new connection for every query; You don't even have to close it manually.

Just create the connection at the beginning of you page

have a look at: How do you efficiently connect to mysql in php without reconnecting on every query

you can use this:

public function __destruct()
{
   mysql_close($this->connection);
}

it will get called when the page is closed