Adapting MySQL result sets - PHP

As I expressed in the previous section, the last example that I plan to set up here concerning the implementation of the adapter pattern with PHP 5 consists basically of expanding the functionality of a MySQL result set handling class, obviously without having to appeal to the goodies of inheritance.

That being said, I listed the two base classes responsible for connecting to MySQL, running queries and so forth, as well as for handling data sets. Have a look at them, please:

// define 'MySQL' class 
class MySQL{
    private $conId;
    private $host;
    private $user;
    private $password;
    private $database;
    private $result;
    
public function__construct($options=array()){
     if(count($options)<4)
{throw new Exception('Invalid number of connection 

parameters');
        }
        foreach($options as $parameter=>$value){
            if(!$value){
                throw new Exception('Invalid parameter '.$parameter);
            }
  
$this->{$parameter}=$value;
        }
       
$this->connectDB();
    }
    // connect to MySQL
    private function connectDB(){
      
if(!$this->conId=mysql_connect($this->host,$this->user,

$this->password)){
            throw new Exception('Error connecting to the server');
        }
    
if(!mysql_select_db($this->database,$this->conId)){
            throw new Exception('Error selecting database');
        }
    }
    // run query 
    public function query($query){
       
if(!$this->result=mysql_query($query,$this->conId)){
            throw new Exception('Error performing query '.$query);
        }
        return new Result($this,$this->result);
    }
}
// define 'Result' class
class Result {
    private $mysql;
    private $result;
    public function__construct(&$mysql,$result){
      $this->mysql=&$mysql;
$this->result=$result;
    }
    // fetch database table rows
    public function fetchRow(){
        return mysql_fetch_assoc($this->result);
    }
    // count database table rows
    public function countRows(){
       
if(!$rows=mysql_num_rows($this->result)){
            throw new Exception('Error counting rows');
        }
        return $rows;
    }
    // count affected rows
    public function countAffectedRows(){
       
if(!$rows=mysql_affected_rows($this->mysql->conId)){
            throw new Exception('Error counting affected rows');
        }
        return $rows;
    }
    // obtain last insertion ID
    public function getInsertID(){
       if(!$id=mysql_insert_id($this->mysql->conId)){
            throw new Exception('Error getting ID');
        }
        return $id;
    }
    // seek row
    public function seekRow($row=0){
  
if(!int($row)||$row<0){
            throw newException('Invalid result set offset');
        }
 
if(!mysql_data_seek($this->result,$row)){
            throw new Exception('Error seeking data');
        }
    }
}

After examining the above two classes, you'll probably find them familiar, since I used them in some of my previous PHP articles. However, this time I want you pay attention to the respective "Result" class, because I'll use it as the starting point for extending its functionality via an adapter class.

Now, say you want to provide this class with the ability to return completely-formatted result sets, but as I said before, without deriving a subclass from it. Of course, if you learned something from the examples that I showed previously, then you'll have the right answer for doing this: an adapter class!

Precisely, based on this premise, below I defined an adapter class that extends the capacity of the original "Result" and is able to display entirely formatted data sets. The signature for this class is the following:

// define 'ResultAdapter' class
class ResultAdapter{
    private $resultObj;
    public function__construct(Result $resultObj){
 
$this->resultObj=$resultObj;
    }
    // get formatted result set
    public function fetchFormattedResult($fieldDelimiter=' ',

$closeTag='</p>'){
        $output='';
       
$opentag=str_replace('/','',$endTag);
      
while($row=$this->resultObj->fetchRow()){
            $tempOutput='';
            foreach($row as $data){
                 $tempOutput.=$fieldDelimiter.$data;
            }
         
$output.=$openTag.$tempOutput.$closeTag;
        }
     
unset($openTag,$closeTag);
        return $output;
    }
}

As you can see, the above defined adapter class certainly isn't rocket science at all. All I did here was pass in the original "Result" object to the corresponding constructor and create a specific "fetchFormattedResult()" method to provide data sets with a certain look and feel.

One possible implementation of the prior result set adapter class can be appreciated by examining the following script:

 
try{
    // connect to MySQL
    $db=new MySQL(array('host'=>'host','user'=>'user',

'password'=>'password','database'=>'database'));
    // get result set object
    $result=$db->query('SELECT * FROM users');
    // instantiate 'ResultAdapter' object
    $resultAdapter=new ResultAdapter($result);
    // display formatted result set
    echo $resultAdapter->fetchFormattedResult();
    /*
    displays the following data wrapped up into paragraphs:
    1 user1 user1@domain.com
    2 user2 user2@domain.com
    3 user3 user3@domain.com
    4 user4 user4@domain.com
    5 user5 user5@domain.com
    6 user6 user6@domain.com
    7 user7 user7@domain.com
    8 user8 user8@domain.com
    9 user9 user9@domain.com
    10 user10 user10@domain.com
    */
}
catch(Exception $e){
    echo $e->getMessage();
    exit();
}
 

Concerning the above example, all the rows contained in the sample "USERS" database table have been displayed inside paragraphs, via the corresponding "fetchFormattedResult()" method that belongs to the previous "ResultAdapter" class.

Here, it's clear to see how easy it is to create an adapter class without using inheritance. Of course, feel free to tweak the code of all the examples that I provided here, thus you can experiment with introducing your own modifications.

Final thoughts

Finally, we've come to the end of this series. In these two articles, I gave you some handy pointers on how to create adapter classes with PHP 5, in case you can't use inheritance as the approach for extending the functionality of a given class.

As I explained in these tutorials, the adapter pattern is more suitable to apply in situations where it's not only necessary to expand the capacity of a class, but the class must also fit the requirements of a third object.  As usual, see you in the next PHP series!