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 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 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.