Web Design Forum: OOP PHP Part 2: Database access - Web Design Forum

In the last post, I quickly ran through how to create an object. From here on in, I want to focus on practical applications of OOP, and to avoid abstract examples where possible. I often see people cite abstract examples as a barrier to entry, so I want to help bridge that gap with some practical classes that can be used in your projects from the off.

So, without further ado - let's get started.

Generally, we create new instances of objects using the 'new' statement. Sometimes, however, we don't always need/want to create an instance of a class. In some cases, we just want an object to act as a toolbox from which we can retrieve tools. So, let's make a utility class, where we can dump some useful functions.

class Time_Utility{
    private function __construct(){}
    private function __clone(){}  

    //returns current time in specific format
    public static function currentTime(){
        return date('Y-m-d H:i:s');
    }
}

Let's walk through the class. At the top, you'll see that the __construct and __clone functions are private. This means that you cannot create any instances of the object using the new statement. So, if you can't get an instance of the class, how do you use it's methods?
This is where the static keyword comes in. Public static methods (and properties) are accessible externally using the paamayim nekudotayim operator. Yes, that's really what it's called. It's also called the scope resolution operator. (I just call it double colon or static colons...). The scope resolution operator imparts a lot of interesting capabilities, but for the time being, we'll just use it to access a static method. So, to get the current time, I would do this:

echo Utility_Time::currentTime();

Armed with our utility class, we can dump interesting or useful functions inside and call them from our script when we need them. Static methods/properties are slightly controversial, since they are effectively global properties by one remove, but as with global properties -sometimes they can be very useful if handled correctly. The main drawback with this method is that you don't get the dynamic setup that comes with being able to use a constructor. So, how can we get the best of both worlds? Enter the Singleton. Singleton is an OOP design pattern that ensures there is only ever one instance of the object in the system, while also allowing you to instantiate the object. We'll use one to set up a database connection:

class DB_Utility(

    private static $instance;
    private function __clone(){}
    private function __construct(){
        $this->db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASS);
    }

    public static function getInstance(){
    if( !isset( self::$instance ) ){
        self::$instance = new self();
    }
    return self::$instance;
    }
}

(I'm using the singleton pattern as described in PHP: Objects Patterns and Practice by Matt Zandstra - but you can see this pattern in several sources.)

I can still use static methods from within this class without instantiating it - but now I get the added benefit of automating some configuration in the constructor by calling the object like so (If you work with Zend Framework, you'll see this everywhere (it's perhaps a little over used IMO).):

$DB = DB_Utility::getInstance(); 

Now we're getting somewhere - the $DB variable now contains a unique, non replicable handle for a database. So let's start putting it to some use! Add in the following lines:

public static function insert($values,$table){
   $sql = "INSERT INTO $table (";
   $n = count($values);
   $i = 0;

//prep parameter string to aggregate array contents
   $sql_params = '';

//Assemble values array
   foreach($values as $k=>$v){
      $sql_params = ':'.$k;
      if($i != $n){
          $sql .= ',';
          $sql_params .= ','; 
       }
     $i++;
   }
   $sql .= ') VALUES '.$sql_params;

   //create statement
   $stmt = $this->db->prepare($sql);

   //bind values to the statement
   foreach($values as $k=>$v){
     $stmt->bindValue(':'.$k,$v,PDO::PARAM_STR);
   }

   //try the statement
   try($stmt->execute()){
   }catch(PDOException $e){
       return $e->getMessage();
   }
}

public static function select($columns,$table,$where){
    $sql = 'SELECT ';

    //assign columns
    if(!is_array($columns)){
       $sql .= $columns;
    }else{
    $sql .= implode(',',$columns);
    }

    //assign table
    $sql .= ' FROM '.$table;

    //assuming $where is a string i.e. index = "value"
    //this is far from ideal, purely for demonstration
    $sql .= ' WHERE '.$where;

    try{
        $result = $this->db->query($sql);
        $return = (count($result) > 0)?$result:null;//check row count with ternary operator statement
        return $return;
    }catch(PDOException $e){
        return $e->getMessage();
    }
}

This isn't production quality code by any means, but you get the gist. We've basically added 2 common database statements to the DB object. So now, rather than peppering our code with complex MySQL routines, you can simply invoke the class - which now encapsulates all that dirty work!

//adds a record to the great_sages table
$DB::insert(array(
        'first_name'=>'sun',
        'mid_name'=>'wu',
        'last_name'=>'kung'),
    'great_sages');

//retrieves same record
$record = $DB::select('mid_name','great_sages','last_name="kung"');
echo $record['mid_name'];//prints 'wu';

Well, that about wraps that up - we've assembled our first useful class. You can add methods to this depending on the project requirements, or use it as a template for other classes to inherit from. I'll go into that in greater detail in the next post.

DISCLAIMER: I apologise if there are any typos/bugs in the code, I wanted to get this post off the ground ASAP - the principles remain the same however.