[ACCEPTED]-How do you efficiently connect to mysql in php without reconnecting on every query-connect

Accepted answer

Score: 17

Normally connections happen once a page 19 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 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 18 in the beginning of the page, close it at 17 the end page. Then, you can make various 16 queries during the page and don't have to 15 do anything to the connection.

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

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

Global $db;

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

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

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

mysql_connect();

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

mysql_connect();

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

Per the comments:

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

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

Score: 0

You don't need to connect to the database 20 in every function. You need to connect to 19 the database when the script starts running 18 and save connection object in global state. In 17 any function you can use that connection 16 object to query the database. Your connection 15 object will be recreated for every time 14 script is executed but it will be very fast, because 13 special connection pool is used behind the 12 scene, so connection will be done immediately 11 (matter of microseconds, because actually 10 connection was not even broken, it was saved 9 in connection pool).

Here is the example 8 you asked for:

// this code should be executed on every page/script load:
$adoConn = ADONewConnection(...);
$adoConn->PConnect(...);

// ...

//And then in any place you can just write:
global $adoConn;
$adoConn->ExecuteNonQuery("INSERT INTO test SET Value = 'Hello, world!'");

As for your question "how 7 do I implement connection pool". You don't. It's 6 maintained by the server behind the scene 5 and used if you (or the PHP library for 4 work with PHP) use mysql_pconnect() function.

PS. If 3 you are afraid to keep $adoConn as a global 2 variable (I'm not) then you can create a 1 class with a static property:

class DB
{
  public static $adoConn;
}

// ...

DB::$adoConn->ExecuteNonQuery(...);

Source: stackoverflow.com

[ACCEPTED]-How do you efficiently connect to mysql in php without reconnecting on every query-connect

Mysql connector - MultipleActiveResultSets issue

What properties does Node.js express's Error object expose?

What is a good session store for a single-host Node.js production app?

how to pass qobject as argument from signal to slot in qt connect

Check each node.js request for authentication credentials

How do I combine Connect middleware into one middleware?

How to disable Express BodyParser for file uploads (Node.js)

How do I test if there is a server open on a port with python?

How do I set the bodyParser upload limit to specific routes rather than globally in the middleware?

ssh: connect to host heroku.com port 22: Connection timed out