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

Question

I'm pretty sick of having to rewrite my code every time I learn something new about php (like the fact that mysql connections cannot be passed around in a session as a handle).

How do you implement mysql connection in your projects? A lot of people have proposed "connection pooling", but after reading the manual i'm still lost. It's like: "connection pooling is mysql_pconnect!" - me: "and...? how is that any different in reality? can you pass around a mysql_pconnect in a session? why is this seemingly mysterious aura??"

Let me explain my situation. I have a function called "query1":

function query1($query)
{
    $db = new mysql(HOST,USER,PASS,DBNAME);
    $result = $db->query($query);
    $db->close();
    return $result;
} 

This is seems like a squanderous and inefficient way of querying a db (especially since you need a mysql handle for functions like mysql_real_escape_string). What is the correct form to do it? Can someone please help me?

Thank you I'd really appreciate a good honest answer.

Answer 1

Normally you would connect 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 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.

Answer 2

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

Here is the example 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 do I implement connection pool". You don't. It's maintained by the server behind the scene and used if you (or the PHP library for work with PHP) use mysql_pconnect() function.

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

class DB
{
  public static $adoConn;
}

// ...

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

Answer 3

Few things concerning your function:

1. Don't ever call a function query1! That's a horrible function name, it doesn't talk to you! Call it fetchAllFlowers or callMyGrandma or if you want it more generic callPerson($personToCall).

2. As @Chacha102 suggests you should build your code the other way around. Don't connect with the database everytime you want to run a query. Connect once, store the connection in a variable or object property and then reused this connection for every query.

3. Don't use close(). In most cases it's not necessary and counter productive for what you are trying to do.