Singleton PDO Database class, tips/suggestions?

maZtah · 04-28-2009, 04:27 PM

Hi all,

This is a singleton PDO Database class I wrote. Do you have any tips/suggestions?

PHP Code:


			
<?php



class Database

{

    private static $m_pInstance = NULL;

    private $m_pConn;

    

    private function __construct() {

        $this->connect('hostname', 'username', 'password', 'database');

    }

    

    private function __clone() { }

    

    public static function get_instance()

    {

        if (!self::$m_pInstance)

        {

            self::$m_pInstance = new Database();

        }

        

        return self::$m_pInstance;

    }

    

    public function connect($szHostname, $szUsername, $szPassword, $szDatabase)

    {

        try

        {

            $this->m_pConn = new PDO('mysql:host='.$szHostname.';dbname='.$szDatabase, $szUsername, $szPassword);

                        

            $this->m_pConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        }

        catch(PDOException $e)

        {

            echo $e->getMessage();

        }

    }

    

    public function query($szQuery)

    {

        $pStatement = $this->m_pConn->prepare($szQuery);

        $pStatement->execute();

        

        $aResult = $pStatement->fetchAll();

        

        return $aResult;

    }

    

    public function query_row($szQuery)

    {

        $aResult = $this->query($szQuery);

        

        return $aResult[0];

    }

    

    public function query_column($szQuery)

    {

        $aResult = $this->query_row($szQuery);

        

        return $aResult[0];

    }

    

    public function insert($szQuery)

    {

        $iCount = $this->m_pConn->exec($szQuery);

        

        return $iCount;

    }

}



?>

Jim · 05-04-2009, 08:27 AM

PDO is nice but I personally would go for a Singleton/Factory.

The class looks pretty nice though :)

maZtah · 05-18-2009, 03:33 PM

I've made a little update to the database class, here it is:

Again, tips and suggestions are more than welcome!

PHP Code:


			
<?php

class Database
{
    private static $m_pInstance;
    private $m_pConn;
    
    private function __construct()
    {
        if (LOCAL)
        {
            $this->connect('localhost', 'username', 'password', 'database');
        }
        else
        {
            $this->connect('localhost', 'username', 'password', 'database');
        }
    }
    
    private function __clone() { }
    
    public static function get_instance()
    {
        if (!self::$m_pInstance)
        {
            self::$m_pInstance = new Database();
        }
        
        return self::$m_pInstance;
    }
    
    public function connect($szHostname, $szUsername, $szPassword, $szDatabase)
    {
        try
        {
            $this->m_pConn = new PDO('mysql:host='.$szHostname.';dbname='.$szDatabase, $szUsername, $szPassword);
            
            if (LOCAL)
            {
                $this->m_pConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }
            else
            {
                $this->m_pConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            }
        }
        catch (PDOException $e)
        {
            echo $e->getMessage();
        }
    }
    
    public function query($szQuery)
    {
        $pStatement = $this->m_pConn->prepare($szQuery);
        $pStatement->execute();
        
        $aResult = $pStatement->fetchAll();
        
        return $aResult;
    }
    
    public function select_query($szTable, $aFields, $aWhere = NULL, $aOrderBy = NULL, $aLimit = NULL)
    {
        $szQuery = 'SELECT ';
        
        $szQuery .= implode(', ',$aFields);
        $szQuery .= ' FROM '.$szTable;
        
        if (is_array($aWhere))
        {
            $szWhere = '';
            
            foreach($aWhere as $szKey => $szValue)
            {
                if (!is_numeric($szValue))
                {
                    $szValue = "'".$szValue."'";
                }
                $szWhere .= ' '.$szKey.' = '.$szValue.' AND';
            }
            
            $szWhere = rtrim($szWhere, 'AND');
            
            $szQuery .= ' WHERE '.$szWhere;
        }
        
        if (is_array($aOrderBy))
        {
            $szOrderBy = '';
            
            foreach($aOrderBy as $szKey => $szValue)
            {
                $szOrderBy .= ' '.$szKey.' '.$szValue.',';
            }
        
            $szOrderBy = rtrim($szOrderBy, ',');
            
            $szQuery .= ' ORDER BY '.$szOrderBy;
        }
        
        if ($aLimit !== NULL)
        {
            $szLimit = implode(',', $aLimit);
            $szQuery .= ' LIMIT '.$szLimit;
        }
        
        return $this->query($szQuery);
    }
    
    public function select_query_row($szTable, $aFields, $aWhere = NULL, $aOrderBy = NULL, $aLimit = NULL)
    {
        $aResult = $this->select_query($szTable, $aFields, $aWhere, $aOrderBy, $aLimit);
        
        return (isset($aResult[0]) ? $aResult[0] : FALSE);
    }
    
    public function select_query_column($szTable, $aFields, $aWhere = NULL, $aOrderBy = NULL, $aLimit = NULL)
    {
        $aResult = $this->select_query_row($szTable, $aFields, $aWhere, $aOrderBy, $aLimit);
        
        return (isset($aResult[0]) ? $aResult[0] : FALSE);
    }
    
    public function insert_query($szTable, $aFields, $aValues)
    {
        $szQuery = 'INSERT INTO '.$szTable.' (';
        $szQuery .= implode(', ',$aFields);
        $szQuery .= ') VALUES';
        
        foreach ($aValues as $aValue)
        {
            $aValue = array_map(array($this, 'clean_input'), $aValue);
            
            $szQuery .= ' (';
            $szQuery .= implode(', ',$aValue);
            
            $szQuery = rtrim($szQuery, ',');
            $szQuery .= '),';
        }
        
        $szQuery = rtrim($szQuery, ',');
        
        $iCount = $this->m_pConn->exec($szQuery);
        
        return $iCount;
    }
    
    private static function clean_input($mInput)
    {
        if (is_numeric($mInput))
        {
            return $mInput;
        }
        else
        {
            return "'".$mInput."'";
        }
    }
}

?>

Wildhoney · 05-18-2009, 03:44 PM

Where is LOCAL defined and why do we have an empty __clone method?

xenon · 05-18-2009, 04:43 PM

Quote:

Originally Posted by Wildhoney

Where is LOCAL defined and why do we have an empty __clone method?

I don't see either where that LOCAL constant was defined...supposedly in another file, which kind of breaks the OO paradigm. Instead, you should have a private member in your class (called $env or such), and a getter/setter method for that. That way, your class will be entirely decoupled from the rest of the application (and you wouldn't need to define a LOCAL constant in all your scripts - not doing so throws a warning).

As for the private __clone method, that's a common thing (I do it myself), and it's good combined with the singleton pattern. Trying to clone the object returned by the getInstance() method will be permitted (thus breaking the singleton rules), as long as you don't define the __clone method as private

I don't agree though with the public connect function. Calling that method several times will only recreate the connection to the DB. You'd probably want to extend the actual PDO class and transform it into a singleton from within your class.

maZtah · 05-18-2009, 08:36 PM

Thanks for the feedback. The first file which gets loaded is init.php:

PHP Code:


			
<?php
// Check if we are local
if ($_SERVER['SERVER_ADDR'] == '127.0.0.1')
{
    define('LOCAL', TRUE);
}
else
{
    define('LOCAL', FALSE);
}

// Activate error reporting on local server
if (LOCAL)
{
    error_reporting(E_ALL);
}

// Include the main functions
include_once('library.php');

// Load mandatory class(es)
include_once('database.class.php');

// Prepare for autoloading the appropriate classes
function __autoload($szClass)
{
    include_once(strtolower($szClass).'.class.php');
}

// Set locale to Dutch
setlocale(LC_ALL, 'nl_NL');

// Define the Google Maps API key
define('GMAPS_KEY', (LOCAL ? 'ABQIAAAAXRbN4TY9xbY5dw4fbMiPVRQQjDJwdt1aUvnAUED1_0C4Ozpy2hTgmlZavSPTSh4PGmg3dWXDWR79mw' : 'ABQIAAAAXRbN4TY9xbY5dw4fbMiPVRR9LNncnZ2GbBrTPZVxzPxSqrkWtRSSZ078PFurHFpxZZ3lovXlkZukJg'));

// Get the main menu
$aMainMenu = Database::get_instance()->select_query('menu', array('id', 'name', 'href'), array('parent_id' => 0), array('rank' => 'ASC'));

// Get the weather details
$pWeather = new Weather();

?>

I just made the connect() function private. I will also add a getter/setter to the class instead of relying on a defined global.

Thanks!

maZtah · 05-20-2009, 10:04 AM

What I did now instead of the global defined LOCAL variable, I made a Registry class where I save global variables. Then i pass the Registry class to every other class which might need the global variables.

In my init.php file:

php Code:

// Load the registry
$pRegistry = new Registry();

// Check if we are local
if ($_SERVER['SERVER_ADDR'] == '127.0.0.1')
{
    $pRegistry->is_local = TRUE;
}
else
{
    $pRegistry->is_local = FALSE;
}

// Load database with the Registry settings
$pRegistry->m_pDatabase = Database::get_instance($pRegistry);

And then my Database class looks like:

php Code:

<?php

class Database
{
    private static $m_pInstance;
    private $m_pConn;
    private $m_pRegistry;
    
    private function __construct(Registry $pRegistry)
    {
        $this->m_pRegistry = $pRegistry;
        
        if ($this->m_pRegistry->is_local)
        {
            $this->connect('hostname', 'username', 'password', 'database');
        }
        else
        {
            $this->connect('hostname', 'username', 'password', 'database');
        }
    }
    
    private function __clone() { }
    
    public static function get_instance(Registry $pRegistry = NULL)
    {
        if (!self::$m_pInstance)
        {
            self::$m_pInstance = new Database($pRegistry);
        }
        
        return self::$m_pInstance;
    }
    
    private function connect($szHostname, $szUsername, $szPassword, $szDatabase)
    {
        try
        {
            $this->m_pConn = new PDO('mysql:host='.$szHostname.';dbname='.$szDatabase, $szUsername, $szPassword);
            
            if ($this->m_pRegistry->is_local)
            {
                $this->m_pConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }
            else
            {
                $this->m_pConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            }
        }
        catch (PDOException $e)
        {
            echo $e->getMessage();
        }
    }
    
    public function query($szQuery)
    {
        $pStatement = $this->m_pConn->prepare($szQuery);
        $pStatement->execute();
        
        $aResult = $pStatement->fetchAll();
        
        return $aResult;
    }
    
    public function select_query($szTable, $aFields, $aWhere = NULL, $aOrderBy = NULL, $aLimit = NULL)
    {
        $szQuery = 'SELECT ';
        
        $szQuery .= implode(', ',$aFields);
        $szQuery .= ' FROM '.$szTable;
        
        if (is_array($aWhere))
        {
            $szWhere = '';
            
            foreach($aWhere as $szKey => $szValue)
            {
                if (!is_numeric($szValue))
                {
                    $szValue = "'".$szValue."'";
                }
                $szWhere .= ' '.$szKey.' = '.$szValue.' AND';
            }
            
            $szWhere = rtrim($szWhere, 'AND');
            
            $szQuery .= ' WHERE '.$szWhere;
        }
        
        if (is_array($aOrderBy))
        {
            $szOrderBy = '';
            
            foreach($aOrderBy as $szKey => $szValue)
            {
                $szOrderBy .= ' '.$szKey.' '.$szValue.',';
            }
        
            $szOrderBy = rtrim($szOrderBy, ',');
            
            $szQuery .= ' ORDER BY '.$szOrderBy;
        }
        
        if ($aLimit !== NULL)
        {
            $szLimit = implode(',', $aLimit);
            $szQuery .= ' LIMIT '.$szLimit;
        }
        
        return $this->query($szQuery);
    }
    
    public function select_query_row($szTable, $aFields, $aWhere = NULL, $aOrderBy = NULL, $aLimit = NULL)
    {
        $aResult = $this->select_query($szTable, $aFields, $aWhere, $aOrderBy, $aLimit);
        
        return (isset($aResult[0]) ? $aResult[0] : FALSE);
    }
    
    public function select_query_column($szTable, $aFields, $aWhere = NULL, $aOrderBy = NULL, $aLimit = NULL)
    {
        $aResult = $this->select_query_row($szTable, $aFields, $aWhere, $aOrderBy, $aLimit);
        
        return (isset($aResult[0]) ? $aResult[0] : FALSE);
    }
    
    public function insert_query($szTable, $aFields, $aValues)
    {
        $szQuery = 'INSERT INTO '.$szTable.' (';
        $szQuery .= implode(', ',$aFields);
        $szQuery .= ') VALUES';
        
        foreach ($aValues as $aValue)
        {
            $aValue = array_map(array($this, 'clean_input'), $aValue);
            
            $szQuery .= ' (';
            $szQuery .= implode(', ',$aValue);
            
            $szQuery = rtrim($szQuery, ',');
            $szQuery .= '),';
        }
        
        $szQuery = rtrim($szQuery, ',');
        
        $iCount = $this->m_pConn->exec($szQuery);
        
        return $iCount;
    }
    
    private static function clean_input($mInput)
    {
        if (is_numeric($mInput))
        {
            return $mInput;
        }
        else
        {
            return "'".$mInput."'";
        }
    }
}

?>

Any tips/suggestions on this way of doing things?