How static vs singleton classes work (databases)

Question

I am confused on how a singleton model vs a static model works for database connections. My friend created a "static" class and showed me it but it did not make any sense on how it was static. I kind of understand the singleton method of how to create a database connection, but i'm not sure it meets my goal.

The main thing I want to do is cut down on the number of connections opened to MYSQL. I have a class with a function that calls the database quiet frequently, and there is no reason for it to make a new connection each time someone requests something that requires the database. Could someone provide a small example class for doing this with the singleton or the static method (whichever is the correct approach) that connects to the database and shows a small sample query? I would greatly appreciate it.

Oh yeah, I am using PHP 5.3 :) Please feel free to ask for additional details.

Answer 1

Consider the following example which uses the singleton design pattern to access the instance of database object.(purpose of this is to reuse the same connection again and again through out the application)

class Database {

    protected static $_dbh;
    const HOST = 'localhost';
    const DATABASE = 'dbname';
    const USERNAME = 'username';
    const PASSWORD = 'password';

    //declare the constructor as private to avoid direct instantiation.   
    private function __construct() { }

    //access the database object through the getInstance method.
    public static function getInstance() {
        if(!isset($_dbh)) {
            #Connection String.
            self::$_dbh = new PDO('mysql:host='.self::HOST.';dbname='.self::DATABASE,self::USERNAME,self::PASSWORD);
            self::$_dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return self::$_dbh;
    }
}

now if i have to make use of the class anywhere in the application i would simple do it like this.

require_once('database.php');
$dbh = Database::getInstance();
$sth = $dbh->query('SELECT * FROM sometable');
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

the call to Database::getInstance(); uses static method. what this basically does is it restricts you from directly instantiating the object by declaring the constructor as private, and instead it checks if the object is already instanitated. if true then return already instantiated object. else create new and return newly created object. this makes sure that same database connection is reused through out the application.