Using PHP to access a MySQL database, can I keep a connection open?

Question

I'm fairly new to PHP and MySql. I have written a simple API to manipulate my database.

The handler class is like so:

class DbHandler {

    protected static $conn;

    function __construct() {


        if(!isset(self::$conn)) {

            $this->connect();
        }

    }

    function __destruct() {

        self::$conn->close();


    }

    function connect() {


        $config = parse_ini_file('../../config.ini');

        // Try and connect to the database
        self::$conn = mysqli_connect('localhost',$config['username'],$config['password'],$config['dbname']);


        if(self::$conn===FALSE) {

            header("HTTP/1.1 500 Internal Server Error");
            header("Content-Type: application/json");
            $response = array("Response"=>"Failed to connect to the database");
            echo json_encode($response);
            die();

        }


    }
}

Each time I query the database I create this DbHandler object (note there are also other methods inside such as add entry, search etc) which opens a connection. My question is is this the normal way to do things? And is it efficient to open up and connect to the database every single time when a query needs to be done?

L.H · Accepted Answer · 2015-08-13 12:45:23Z

Efficiency is a word of many colors in this regard. Generally there are two ways of handling the database connection: A persistent connection or a non-persistent connection (where as the last one is the one you use now). There are some pros and cons for both of them.

The persistent connection is a connection that normally wont die before it times out or is closed. The pros are such as less overhead between PHP and the MySQL, as the connection string/request is send fewer times. This could be good if you were to create a chat, a game or something like that which needs to listen to the database on a high frequency. The cons could be if your MySQL server has a maximum simultaneous allowed connection. If a user is to visit your site and pull some texts etc once from your database - the persistent connection would not be efficient as it would be "running without a job to do" afterwards and thereby be likely to limit the future users pull-requests.

The non persistent-connection, as you already might have guessed by now, dies as soon as the request from PHP has run. If you were to make the chat/game again here and you expected a response within X milliseconds, the overhead (the connection request) could cause a delay where as the response would be delayed and eventually delay your whole algorithm. In this situation the tradeoff could be like maximum users allowed vs. real-time data-access between PHP and the MySQL server.

That said, the persistent connection would not work if your application is not persistent as well. HTTP webservers works by serving a ressource on a path (e.g. myfile.php on mywebsite.com/my_files/myfile.php). When a user enters the address in his browser the webserver accepts the request, finds the data, interprets the PHP file and as a result, the generated data from the PHP file is served to the user. If your PHP application is not set to be persistent the request would die once served - hence: your MySQL connection is closed. In reality there are more steps to this process as the request to the server also looks up the name in a DNS server to match the IP adress used for the connection.

I barely scracthed the surface and a lot of people might have many more pros/cons and valuable points to this, but you can read more about PHP and persistent connections here: http://php.net/manual/en/features.persistent-connections.php

Also, this explains a bit about how webservers work: http://www.serverwatch.com/tutorials/article.php/1407961/Serving-Up-Web-Server-Basics.htm

Thanks for this, great answer. I'm not developing a chat app, or anything that needs to perform database commands in frequent intervals so I guess sticking with the non persistent-connection that I have now will suffice.
You are more than welcome. I also think the non-persistant way will be the best choice for you then. Good luck with your project.

Hatem Jaber · Accepted Answer · 2015-08-13 12:19:40Z

0

I believe mysqli_connect() will keep track of your connections and avoids opening the same connection multiple times.

answered Aug 13, 2015 at 12:19

Hatem Jaber

2,4372 gold badges25 silver badges40 bronze badges

Collectives™ on Stack Overflow

Using PHP to access a MySQL database, can I keep a connection open?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related