PHP Connection Pooling MySQL

PHP Mysqli database connection pooling to avoid maximum user count

There is no connection pooling in PHP.

The persistent connection you are trying to use (that "p:" bit) is the most sure way to hit the maximum connection number, so get rid of it immediately.

15 concurrent connections is, actually, A LOT. Simply optimize your queries, so a typical connection would last 0.1 second, which would mean 150 concurrently executed PHP scripts which is like 1500 users on-line.

Also, you need to add

static $connect;

as the first line in your function, or is will create the new connection every time it is called, which is the actual reason why you are getting this error.

<?php
function getConnection(){
    static $connect;

    if (!$connect){
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        $connect = new mysqli('xxxx','yyy','zzz','aaaa');
        $connect->set_charset('utf8mb4');
    }
    return $connect;
}

Better yet, get rid of this function at all, have a separate file with the mysql connection code, and then use the $connect variable all around your code. See my article about mysqli connect for the details.

How do you efficiently connect to mysql in php without reconnecting on every query

Normally connections happen once a page load. AKA

class Database{
    public function connect()
    {
         $this->connection = mysql_connect();
    }

    // This will be called at the end of the script.
    public function __destruct()
    {
        mysql_close($this->connection);
    }

    public function query($query)
    {
        return mysql_query($query, $this->connection);
    }
}
$database = new Database;
$database->connect();

$database->query("INSERT INTO TABLE (`Name`) VALUES('Chacha')");

Basically, you open the connection in the beginning of the page, close it at the end page. Then, you can make various queries during the page and don't have to do anything to the connection.

You could even do the mysql_connect in the constructor as Erik suggest.

To use the above using global variables (not suggested as it creates global state), you would do something like

Global $db;

$db = new Database;
// ... do startup stuff

function doSomething()
{
    Global $db;
    $db->query("Do Something");
}

Oh, and no one mentioned you don't have to pass around a parameter. Just connect

mysql_connect();

Then, mysql_query will just use the last connection no matter what the scope is.

mysql_connect();

function doSomething()
{
    mysql_query("Do something");
}

Per the comments:

I think you should use mysql_pconnect() instead of mysql_connect(), because mysql_connect() doesn't use connection pooling. – nightcoder

You might want to consider whether you use mysql_connect or mysql_pconnect. However, you should still only connect once per script.

Related Topics