When Should I Close a Database Connection in PHP

When should I close a database connection in PHP?

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

When do I have to close mysqli (Database) connection?

A quote from the php.net site.

Open non-persistent MySQL connections and result sets are automatically closed when their objects are destroyed. Explicitly closing open connections and freeing result sets is optional. However, it's a good idea to close the connection as soon as the script finishes performing all of its database operations, if it still has a lot of processing to do after getting the results.

Source: http://php.net/manual/en/mysqli.close.php

How often should I close database connections?

If you have connection pooling on (http://en.wikipedia.org/wiki/Connection_pool) its ok to be grabbing a new connection when you need it. HOWEVER, I'd say to be in the habit of treating any resource as "limited" and if you open the db handle keep it around for as long as possible.

Do I need always close connection with db before return some value from function

No. There's very rarely a need to close a DB connection until you're actually done using it. Usually you don't even have to disconnect anyways, as PHP will do that automatically as part of the script's shutdown/cleanup routines.

Doing a connect-query->disconnect cycle repeatedly in your code is highly wasteful. The connect/disconnect sequences are just a waste of CPU cycles and memory resources. Connect once at the beginning, do however many DB operations are needed, then... just ignore the connection. PHP will handle the cleanup at the end.

About the only time you would want to force a disconnect is if your script is long-running, and you don't need the DB for the duration of that "long" section. Freeing up the DB connection may be useful in that case, but only if you have "many" other DB-using operations occurring in other parallel scripts. There's no point in holding a connection slot open and then not using it.

Similarly, you generally only ever need one database connection in your script, UNLESS you need to connect with different credentials to perform some sensitive operation, or have to connect to two or more completely separate DB engines.

When should I close database connection?

Connections are implicitly closed when a script has finished executing. The only reason you may want to close a connection is if you plan on opening another one; even then, PDO supports multiple concurrent connections.

Use of closing database connection in php

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

Freeing resources

Thanks to the reference-counting system introduced with PHP 4's Zend Engine, a resource with no more references to it is detected automatically, and it is freed by the garbage collector. For this reason, it is rarely necessary to free the memory manually.

Hope this helps you more.

(source)

edit:

The purpose of mysql_close() is also to save computer resources, but another key reason for using it is because there is a limited number of connections that a MySQL server can accept, and if you have several clients holding connections open for no reason then the server may well need to turn away other, waiting clients. Naturally this is a bad thing, so, as with mysql_free_result(), it is good to call mysql_close() if you think there will be some time between your last database use and your script ending.

Why do we have to close the MySQL database after a query command?

1. Yes, you can have multiple database connections. You are not opening a database, you are opening a database connection. The database is 'open' (i.e. running) all of the time, generally speaking, whether you are connected to it or not.
2. Depends... if you only have one open connection on a page, then you don't need to close it because it will automatically close when PHP is done. If you have many, then you could potentially make the database server slower, or make the database server run out of available connections (it can only have a certain number of connections open at the same time). That said, most modern database servers can handle hundreds of concurrent connections.
3. Optional, but recommended. It's not a big deal for small-medium projects (i.e. if you have less than 100 concurrent visitors at any given time, you probably won't have any issues regardless). Since you have many thousand visitors per minute, you should actively close the database connection as soon as you are done with it, to free it up as soon as possible.

Open and close connection to db or keep it

No. You should not close the connection if you want performance. You will have 0 effect on security by closing connection manually. Closing the connection will make things slower.

I'm going to write the rest of the answer based on your criteria of performance and I will assume you are serving php via php-fpm (if not, you should).

Closing and opening a connection carries overhead of handshaking with MySQL and opening file descriptors. If you have a lot of requests going on, this process will impact performance for no gain.
Using a persistent connection however, will boost the performance.

Here's why: once php script ends, php will keep the connection open. The next request coming in will use already established connection - you avoid the handshake, tcp overhead and what not.

Excerpt from php manual:

Many web applications will benefit from making persistent connections
to database servers. Persistent connections are not closed at the end
of the script, but are cached and re-used when another script requests
a connection using the same credentials. The persistent connection
cache allows you to avoid the overhead of establishing a new
connection every time a script needs to talk to a database, resulting
in a faster web application.

Source -> scroll down to example 4 to see how to implement this.

Is closing the mysql connection important?

From the documentation:

Note: The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().

If your script has a fair amount of processing to perform after fetching the result and has retrieved the full result set, you definitely should close the connection. If you don't, there's a chance the MySQL server will reach it's connection limit when the web server is under heavy usage. If you can't close the MySQL connection until near the end of the script, it's cleaner though unnecessary to do so explicitly.

I'm not certain how fastcgi affects things. One page claims that a build of PHP that supports fastcgi will create persistent connections, even for mysql_connect. This contradicts the documentation in that the connection is closed when the process, rather than the script, ends. Rather than testing it, I'm going to recommend using mysql_close(). Actually, I recommend using PDO, if it's available.

---

Related Topics