How do you manage database connections in php?

Question

So recently I've really started to use php actively, and I need some insights on different ways to use database connections.

At first I just used the simple mysql_connect():

<?php
    $connection = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
    mysql_select_db(DB_DB, $connection);
?>

After a while I created a database class which I started to include and initialize in every file - something like this:

<?php
class MySQL_DB {

var $connection;

function MySQL_DB(){
 $this->connection = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
 mysql_select_db(DB_DB, $this->connection);
}

function query($q){
 $res = mysql_query($q, $this->connection) or die(mysql_error());
 return $res;
}
}

$database = New MySQL_DB;
?>

And this is what I'm using at the time - and it's working fine - but there are always ways to improve.

So my question to you is how do you manage your database connections?

• Do you use classes?
• What does your classes contain (just the connection or even functions?)
• What practices do you recommend?

Answer 1

I recommend to use PDO. Don't reinvent the weel. It's a nice OO-interface to many database engines. Additionally I create a small function which just inititializes PDO object. So all connection settings can be changed in one place.

Answer 2

Usage of classes are the way to go to increase customized re-usability.

Bring in all generic implementations into the class. You are on the right track.

This website has the following clean approach .

class connection {
    // Possible Modules are as follows:
    // DBX_MYSQL, DBX_ODBC, DBX_PGSQL, DBX_MSSQL, DBX_FBSQL, DBX_SYBASECT, DBX_OCI8, DBX_SQLITE
    private $module = DBX_MYSQL; 

    private $host = "localhost";
    private $database = "test";
    private $username = "testuser";
    private $password = "testpass";

    private $link;
    private $result;
    public $sql;

    function __construct($database=""){
            if (!empty($database)){ $this->database = $database; }
            $this->link = dbx_connect($this->module,$this->host,$this->database,$this->username,$this->password);
            return $this->link;  // returns false if connection could not be made.
    }

    function query($sql){
            if (!empty($sql)){
                    $this->sql = $sql;
                    $this->result = dbx_query($this->link,$sql,DBX_RESULT_UNBUFFERED);
                    return $this->result;
            }else{
                    return false;
            }
    }

    function fetch($result=""){
            if (empty($result)){ $result = $this->result; }
            return dbx_fetch_row($result);
    }

    function __destruct(){
            dbx_close($this->link);
    }

}

Answer 3

Your current approach is pretty standard, and works well. I used it for a long time. It's true that modules like PDO provide base functionality like this now, which is very nice as well and can get you away from problems with home-brew code.

However, I've taken the connection management one step further. If you get into a complex application, you might get into a situation where you have multiple databases, or heavy database use. Including a single database connection file and having a global $database variable becomes unwieldy for multiple databases, and it's unnecessary for application requests that might not need a database connection. Remember, connecting to the database is expensive.

What I've done is create a singleton DatabaseManager class that handles the database object for me, and makes sure multiple connections to a given DB don't get instantiated. Instead of initializing a new database object at the top of your app, you simply call on the DatabaseManager every time you need the object.

$db = DatabaseManager::getDatabase();

Here's an example class that I had whipped up for a CodeIgniter project. You can see in the getDatabase() function it simply loads CodeIgniter's default database object, which you would substitute for your own class (and run the connection routine for it) if you weren't using CI. This is a pretty simplistic management class, and could be extended to manage multiple connections to different databases fairly easily.

<?php

/**
 * Implements the Singleton pattern to prevent multiple instantiations and connections
 * to the application database.
 *
 */
class Database_manager
{
    private static $instance;
    public $db;

    /**
     * Constructor function is declared private to prevent instantiation.
     *
     */
    protected function __construct()
    {
     parent::__construct();
    }

    /**
     * Returns an instance of a Database_manager.
     *
     * @return object Database_manager object
     */
    public static function getInstance()
    {
     if (self::$instance == null) {
      $className = __CLASS__;
      self::$instance = new $className();
     }
     return self::$instance;
    }

    public static function getDatabase()
    {
     $instance = self::getInstance();
     if ($instance->db == null) {
      //utilize CodeIgniter's database loader
      $instance->db = $instance->load->database('',true);
      if (! is_object($instance->db)) throw new Exception("Could not load database.");
     }
     return $instance->db;
    }
}

Perhaps the most common advantage I get out of using this style of connection management is when I have to take down an application for database maintenance. By not instantiating a database connection until I need it, I can easily put up a "maintenance in progress" message on a site (short circuiting normal MVC dispatching), and not worry about requests to the application opening a DB connection while maintenance is in progress.

Answer 4

In your database manager example, you did not define a parent for your class. Therefore, invoking parent::__constructor() yields an exception, and also, you cannot use the load property of code ignitor.

Which class did you use as an extension for your DatabaseManager?

Since i do not know where you placed your databasemanager code, nor which class you used as its parent, i circumvented the exceptions by making the getDatabase() method receive an input parameter which i called $loader. Normally, this $loader object will be the model class requiring access to a database.

public static function getDatabase($loader)   
{  
    $instance = self::getInstance();  
    if ($instance->db == null) {  
            //utilize CodeIgniter's database loader  
            $instance->db = $loader->load->database('default',true);  
            if (! is_object($instance->db)) throw new Exception("Could not load database.");  
    }  
    return $instance->db;  
}  

Best regards.