Database connection in constructor and destructor

Question

I am playing with different ways to do database interaction in PHP, and one of the ideas I have been playing with is connecting to the DB in the constructor and disconnecting in the destructor. This is the code from my Database class.

function __construct()
{
  $this->link = mysql_connect($this->server.':'.$this->port, $this->username);
  if(!$this->link)
    die('Could not connect: '.mysql_error());

  if(!mysql_select_db($this->database, $this->link))
    die('Could not select database: '.mysql_error());
}    

function __destruct()
{
  if(mysql_close($this->link))
    $this->link = null; 
}

This works well, my only reservation is that if I need to connect several to hit the database several times it will do multiple connections and disconnects. If I do that a lot I can see, maybe, potential problems. Is this a concern or is there a better way to do this? And is my code even up to snuff in general?

Answer 1

From your question I infer that you're thinking of having several instances of the DB class. If so I'd suggest abstracting the connection out to another class and holding a reference to the same connection in each DB instance.

You could then set your connection up as a singleton and thus only connect & disconnect once.

Apologies in advance if I've missed anything here - my PHP is far from fluent.

Answer 2

You could use MySQLi (PHP extension) which is class based by default instead of MySQL. It is very easy to set up multiple connections. You are, however, required to know the connection you are querying always.

Answer 3

You might also look into the built-in php command mysql_pconnect(). This differs from mysql_connect in that it opens a persistent connection to the DB the first time it is called, and each subsequent time, it checks to see if an existing connection to that database exists and uses that connection instead. You should then remove the mysql_close command from the destructor, as they will persist between page loads.

The php manual page: http://php.net/manual/en/function.mysql-pconnect.php

Answer 4

use an abstraction library like Pear MDB2 for your database connection.

This abstracts all the connection logic away from your code, so should ever change your database (mysql to SQLite,etc) you won't have to change your code.

http://pear.php.net/manual/en/package.database.mdb2.php

Answer 5

I don't think it makes any difference in regards to connecting to the database within the construction or within a connect method, what i do think you need to change is those die commands.

using die causes the script to halt and send 1 little message to the user, who would think this is rubbish, and never visit your site again :( :(

What you should be doing is catching your errors, and redirecting to a static page where you can show a very nice message to the user, fully apologising for the technical issues your having.

You can also have an box that says, Enter your email address and we will email you when were back on line, you get the idea.

as for the code I would go down the lines of:

class Database
{
    public function __construct($autoconnect = false)
    {
        //Here you would 'globalize' your config and set it locally as a reference.
        if($autoconnect === true)
        {
             $this->connect();
        }
    }

    public function connect()
    {
        if($this->connected() === false)
        {
             $result = $this->driver->sendCommand("connect");
             if($result === true)
             {
                  $this->setConnectionState("active");
                  $this->setConnectionResource($this->driver->sendCommand("get_resource"));
             }else
             {
                 throw new DatabaseConnectionError($this->driver->sendCommand("getDriverError"));
             }
        }
    }
}

This gives you more functionality in the long run as every action is decidable within your APP, nothing is auto fired on default.

you can simple use try,catch blocks to maintain your error reporting.