Extending PDO while keeping a Singleton happy

[Xeoncross]

 

After diving into PDO I found that I still wanted "helper" functions that would do things like help me to sort column types and stuff. So I built a simple script to handle the PDO connection but now I am wondering how to implement helper functions. I thought of two ways:

 

1) I could leave them as functions (mysql_column_info() and sqlite_column_info())

2) I could build a custom class that was my abstraction between the PDO and added my methods based on the type of database.

3) I could build a second DB class that has the helper functions

 

I don't really want to have 5-15+ "helper functions" for different DB's just floating around my system - and I really don't want to try to abstract over the PDO system.

However, before I build a second "helper" class that contains addition functions based on the DB type - I wanted to ask if anyone had a better way?

 

 

 

Here is a simple version of a PDO class:

<?php

class db {

    // Declare instance
    private static $instance = NULL;

    /**
    * The constructor is set to private so
    * no one can create a new instance using new
    */
    private function __construct() {
      /*** maybe set the db name here later ***/
    }
    
    /**
    * Like the constructor, we make __clone private
    * so nobody can clone the instance
    */
    private function __clone(){}

    /**
    * Return DB instance or create intitial connection
    *
    * @return object (PDO)
    * @access public
    */
    public static function instance() {
        if (!self::$instance) {
            //self::$instance = new PDO("mysql:host='localhost';dbname='animals'", 'username', 'password');
            self::$instance = new PDO("mysql:host=localhost;dbname=pdo", 'root', 'admin');
            self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return self::$instance;
    }


}

//Get the Singleton instance of the DB connection
$db = DB::instance();

//Run a query
$statment = $db->query('SELECT * FROM animals');
$result = $statment->fetch(PDO::FETCH_ASSOC);

foreach ($result as $key => $value) {
    echo $key.' - '.$value.'<br />';
}

?>

[448191]

This is NOT a Singleton. Db isn't the same class as PDO. That said, to answer your question, I would go with option number 2. Just a basic Decorator would do the trick.

[Xeoncross]

This is NOT a Singleton. Db isn't the same class as PDO.

 

class db is a simple object wrapper to provide a Singleton instance of the PDO object. I could do it with a function too.

I just want to avoid global $db; when using PDO in functions and methods. So what is wrong with this?

 

 

I would go with option number 2. Just a basic Decorator would do the trick.

 

What if I had several classes like: sqlite_helper, mysql_helper, firebird_helper and I wanted to extend a class that was a wrapper for the PDO object like you said. How would I add optional functionality to a db class like what is above?

 

 

 

[448191]

class db is a simple object wrapper to provide a Singleton instance of the PDO object. I could do it with a function too.

I just want to avoid global $db; when using PDO in functions and methods. So what is wrong with this?

 

Nothing wrong with it (aside from some stuff you find in Singletons as well). But it's not a Singleton. Terminology is everything.

 

What if I had several classes like: sqlite_helper, mysql_helper, firebird_helper and I wanted to extend a class that was a wrapper for the PDO object like you said. How would I add optional functionality to a db class like what is above?

 

If you can afford it, more abstraction it usually better. If you don't know how to accomplish that, or need some specific help, I'd be pleased to help out. Adding functionality to an existing class can be done in a million different ways.... If you show us something that clearly illustrates your intentions, I can help, otherwise, not.

[Xeoncross]

This is really far off the mark - but you get the idea:

 

<?php

abstract class db {
    public static function instance() {
        if (!self::$instance) {
            //self::$instance = new PDO("mysql:host='localhost';dbname='animals'", 'username', 'password');
            self::$instance = new PDO("mysql:host=localhost;dbname=pdo", 'root', 'admin');
            self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return self::$instance;
    }
}

class mysql_helper extends db {
    function column_data {
        print 'Mysql Column Data';
    }
}

class sqlite_helper extends db {
    function column_data {
        print 'Sqlite Column Data';
    }
    function nonstandardextrafunc() {
        print 'Hello';
    }
}

class mssql_helper extends db {
    function column_data {
        print 'MSSQL Column Data';
    }
}


$db = DB::instance();

$db->query('SELECT * FROM table');
$db->column_data();
?>

 

The fictitious function "column_data()" is just an example of adding a function to the PDO to help manage SQL data.

 

[448191]

That isn't going to work. You'll have to wait for PHP 5.3 and late static binding. Also, you should implement nonstandardextrafunc in all subclasses. That's what polymorphism is about. Try to sensibly emulate the features.

 

 

[Xeoncross]

You'll have to wait for PHP 5.3 and late static binding.

 

"Late Static Binding" huh? I'll need to look into that.

 

Anyway, after I posted on this I found a topic in which someone EXTENDED the PDO class. DUH!

I thought that since it was part of PHP it couldn't be extended! So the following code works fine.

 

However, is there any problem with doing it this way?

 

<?php

//The type of database
$database_type = 'mysql';

//Two classes that add extra stuff to PDO
class mysql extends PDO {
    function column_data() {
        print 'Mysql Column Data';
    }
}
class sqlite extends PDO {
    function column_data() {
        print 'Sqlite Column Data';
    }
}


class db {

    // Declare instance
    private static $instance = NULL;

    /**
    * Return DB instance or create intitial connection
    */
    public static function instance() {
        //Get the type of database we are using
        global $database_type;
        
        if (!self::$instance) {
            self::$instance = new $database_type("mysql:host=localhost;dbname=pdo", 'root', 'admin');
        }
        return self::$instance;
    }


}



//Get the Singleton instance of the DB connection
$db = db::instance();


//Run a query
$statment = $db->query('SELECT * FROM animals');
$result = $statment->fetch(PDO::FETCH_ASSOC);

foreach ($result as $key => $value) {
    echo $key.' - '.$value.'<br />';
}

//Also run my custom function
$db->column_data();
?>

Output: 
animal_id - 1
animal_type - kookaburra
animal_name - bruce
Mysql Column Data

[448191]

It's fine, except for the global.

[Xeoncross]

 

Ok, I am having some trouble with PDO::prepare() to create queries which can later have values added.

 

<?php
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql);

//Run the Code replacing values with new ones
$sth->execute(array(':calories' => 150, ':colour' => 'red'));

//Get result
$red = $sth->fetchAll();

?>

 

 

Now the problem I am having is that I need to PDO::quote() objects before I put them in the query - and I don't know if they will be ':value', '?', or 'A real value that could be XSS'. (this is a simple class for helping to me make SQL queries)

 

 

<?php
/**
     * Creates the in() part of a query.
     */
    public function in_list($data=array()) {
        $output = ' in(';
        
        foreach((array) $data as $value) {
            /*
            if(substr($value, 0, 1) == ':') {
                $output .= $value. ',';
            } else {
                $output .= $this->quote($value). ',';
            }
            */
            $output .= $value. ',';
        }
        return rtrim($output, ','). ')';
        
    }
?>

 

I would just $this->quote() everything and not worry about it - but if the values I used are going to be replaced when I run PDOStatement::execute() - it also runs the PDO::quote() again! So if I quote my values they might become:

'\':value\''

'\'?\''

'\'A real value that could be XSS\''

 

 

and I if I don't and they might be:

:value

?

A real value that could be XSS

 

So I need to figure out how to know if a value is a replacement value or if it is a final value that won't be replaced. The PDO manual states that ? and :value are the way to do it.

 

 

 

 

[djbuddhi]

this is not PHP5 OOP

[Xeoncross]

this is not PHP5 OOP

 

lol, actually - this isn't even valid anymore. I built the CXPDO class that you can get at CodeXplorer .com