Continously enter data without closing sql connection

Question

Please forgive me for this question, I am very new to PHP and I can't find the answer to my question anywhere.

I need to update data from the user to the server continuously at a timed internal which I have set up using javascript and AJAX.

Is there anyway to set up php so that once it connects to the server, it will keep the connection open and continue to accept new data until I tell it to close?

[Server-Sent Events](http://www.html5rocks.com/en/tutorials/eventsource/basics/). — Emil S. Jørgensen, Jun 08 '16 at 12:18

score 1 · Accepted Answer · edited May 23 '17 at 12:08

You could set up a persistent connection to MySQL:

<?php
  $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(PDO::ATTR_PERSISTENT => true
  ));
?>

However, persistent connections in a web application can create serious issues. See What are the disadvantages of using persistent connection in PDO topic here on SO for a detailed discussion on persistent database connections.

In MySQL there is no significant overhead associated with creating and closing database connections, so just establish a new connection every time you call your php script.

score 0 · Answer 2 · answered Jun 08 '16 at 12:25

Why do you want to keep the connection open?

When you send your data the second time, a new instance of that page will be run in PHP on the server and proceed with your request. A database connection once opened should be closed as soon as a transaction is made otherwise it will not free up the resources for other queries to run.

Suppose there are 2 users working with your application, if the first one starts sending some data and then waits before sending more, and the server has kept it's connection open with the database, then user 2 will not be able to process it's request as the database is currently occupied by user 1's request.

I strongly suggest you open and close connections individually for each set of data.

score 0 · Answer 3 · answered Jun 08 '16 at 12:27

I think what you really need it's a singleton DB connection model, so: you'll get an static instance for reutilization.

class DB {

    private $_connection;
    private static $_instance; //The single instance
    private $_host = MY_HOSTNAME;
    private $_username = MY_USERNAME;
    private $password = MY_PASSWORD;
    private $_database = MY_DBNAME;

    public static function getInstance() {
        if(!self::$_instance) { // If no instance then make one
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    // Constructor
    private function __construct() {
        $this->_connection = new mysqli($this->_host, $this->_username, 
            $this->_password, $this->_database);

        if(mysqli_connect_error()) {
            trigger_error("Failed to conencto to MySQL: " . mysql_connect_error(),
                 E_USER_ERROR);
        }
    }

    private function __clone() { }

    // Get mysqli connection
    public function getConnection() {
        return $this->_connection;
    }

    // Force mysqli connection to close
    public function closeConnection() {
        return mysqli_close($this->_connection);
    }
}

This is still going to open a new connection on each request. — tlenss, Jun 08 '16 at 12:37
Yes, but it could be useful for DB instance reutilization in the same script. — Nacho M., Jun 08 '16 at 12:39

Continously enter data without closing sql connection

3 Answers3