Home arrow PHP arrow Page 4 - Iterators in the Simplest Sense: Traversing Data Structures in PHP 5

Traversing database result sets: building a MySQL iterator class - PHP

Welcome to the final part of the series “Iterators in the Simplest Sense.” In this set of articles, you’ll learn the basic concepts of iterators in PHP 4 and PHP 5. The overall learning experience is strongly focused on the practical sense of the topic, so you can start quickly using iterators within your own PHP applications.

TABLE OF CONTENTS:
  1. Iterators in the Simplest Sense: Traversing Data Structures in PHP 5
  2. Working with iterators in PHP 5: using some predefined SPL classes
  3. Traversing text files the easy way: the brand new FileIterator class
  4. Traversing database result sets: building a MySQL iterator class
By: Alejandro Gervasio
Rating: starstarstarstarstar / 8
March 22, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As I said before, my last example on building iterator classes in PHP 5 comprised a simple yet efficient MySQL iterator class. If you're planning to create a dataset paging system without appealing to hard-to-code routines, this iterator might fit your needs. But, first of all, let me show you how this class actually looks. Its definition is as follows:

class ResultIterator{
    private $iterator;
    public function __construct($result){
        if(get_resource_type($result)!='mysql result'){
            throw new Exception('result must be a MySQL result
set');
        }
        // get ArrayObject
        $arrayobj=new ArrayObject();
        // get Iterator object
        $this->iterator=$arrayobj->getIterator();
        while($row=mysql_fetch_row($result)){
            $arrayobj[]=implode('',$row);
        }
    }
    // reset pointer of MySQL result set
    public function rewind(){
        return $this->iterator->rewind();
    }
    // get current row
    public function current(){
        if($this->iterator->valid()){
            return $this->iterator->current();
        }
    }
    // get next row
    public function next(){
        if($this->iterator->valid()){
            return $this->iterator->next();
        }
    }
    // seek row
    public function seek($pos){
        if(!is_int($pos)||$pos<0){
            throw new Exception('Invalid position');
        }
        return $this->iterator->seek($pos);
    }
    // count rows
    public function count(){
        return $this->iterator->count();
    }
}

As you can see, the definition for the above "ResultIterator" class reaffirms the concepts that I explained at the beginning of this article. In this case I built this iterator class by redefining the same set of methods exposed by the previous "FileIterator." Doing so, it's possible to access a given MySQL data set by using the identical bunch of methods (also called an interface).

Regarding the definition of the class constructor, it's clear to see that it reuses the "ArrayObject" class, in order to store each dataset row as a new element of the pertinent array structure. Also, the following checking block:

if(get_resource_type($result)!='mysql result'){
    throw new Exception('result must be a MySQL result set');
}  

makes sure that only valid MySQL result sets are passed as input parameters to the constructor. Of course, as I explained before, the remaining "rewind()," "current()," "next()" methods, etc., are properly redefined within the class; thus they can be used to iterate over the corresponding database result set.

After defining the previous "ResultIterator" class, here's a possible implementation, in this case integrated with a couple of MySQL processing classes:

// define 'MySQL' class
class MySQL{
    private $host;
    private $user;
    private $password;
    private $database;
    private $connId;
    // constructor
    function __construct($options=array()){
        if(!is_array($options)){
            throw new Exception('Connection options must be an
array');
        }
        foreach($options as $option=>$value){
            if(empty($option)){
                throw new Exception('Connection parameter cannot
be empty');
            }
            $this->{$option}=$value;
        }
        $this->connectDb();
    }
    // private 'connectDb()' method
    private function connectDb(){
        if(!$this->connId=mysql_connect($this->host,$this-
>user,$this->password)) {
            throw new Exception('Error connecting to MySQL');
        }
        if(!mysql_select_db($this->database,$this->connId)){
            throw new Exception('Error selecting database');
        }
    }
    // public 'query()' method
    public function query($sql){
        if(!$result=mysql_query($sql)){
            throw new Exception('Error running query '.$sql.' '.mysql_error());
        }
        return new Result($this,$result);
    }
}
// define 'Result' class
class Result{
    private $mysql;
    private $result;
    // constructor
    public function __construct($mysql,$result){
        $this->mysql=$mysql;
        $this->result=$result;
    }
    // public 'fetch()' method
    public function fetch(){
        return mysql_fetch_array($this->result,MYSQL_ASSOC);
    }
    // public 'count()' method
    public function count(){
        if(!$rows=mysql_num_rows($this->result)){
            throw new Exception('Error counting rows');
        }
        return $rows;
    }
    // public 'get_insertId()' method
    public function getInsertId(){
        if(!$insId=mysql_insert_id($this->mysql->connId)){
            throw new Exception('Error getting insert ID');
        }
        return $insId;
    }
    // public 'seek()' method
    public function seek($row){
        if(!int($row)&&$row<0){
            throw new Exception('Invalid row parameter');
        }
        if(!$row=mysql_data_seek($this->mysql->connId,$row)){
            throw new Exception('Error seeking row');
        }
        return $row;
    }
    // public 'getAffectedRows()' method
    public function getAffectedRows(){
        if(!$rows=mysql_affected_rows($this->mysql->connId)){
            throw new Exception('Error counting affected rows');
        }
        return $rows;
    }
    // public 'getQueryResource()' method
    public function getQueryResource(){
        return $this->result;
    }
}
// define 'ResultIterator' class
class ResultIterator{
    private $iterator;
    public function __construct($result){
        if(get_resource_type($result)!='mysql result'){
            throw new Exception('result must be a MySQL result set');
                        }
        // get ArrayObject
        $arrayobj=new ArrayObject();
        // get Iterator object
        $this->iterator=$arrayobj->getIterator();
        while($row=mysql_fetch_row($result)){
            $arrayobj[]=implode('',$row);
        }
    }
    // reset pointer of MySQL result set
    public function rewind(){
        return $this->iterator->rewind();
    }
    // get current row
    public function current(){
        if($this->iterator->valid()){
            return $this->iterator->current();
        }
    }
    // get next row
    public function next(){
        if($this->iterator->valid()){
            return $this->iterator->next();
        }
    }
    // seek row
    public function seek($pos){
        if(!is_int($pos)||$pos<0){
            throw new Exception('Invalid position');
        }
        return $this->iterator->seek($pos);
    }
    // count rows
    public function count(){
        return $this->iterator->count();
    }
}
// implement 'ResultIterator'
try{
    // connect to MySQL
    $db=new MySQL(array('host'=>'host','user'=>'user','password'=>'password',
'database'=>'database'));
    // get result set
    $result=$db->query('SELECT * FROM mytable');
    // use 'ResultIterator' class
    $rIterator=new ResultIterator($result->getQueryResource());
    // reset pointer to beginning of result set
    $rIterator->rewind();
    // display current row of result set
    echo $rIterator->current();
    // move to next row of result set
    $rIterator->next();
    // display current row of result set
    echo $rIterator->current();
    // display number of rows in result set
    echo $rIterator->count();
    // move file pointer to third row in result set
    $rIterator->seek(3);
    // display third row in result set
    echo $rIterator->current();
}
catch(Exception $e){
    echo $e->getMessage();
    exit();
}

That's it. The above example shows a simple approach for traversing a MySQL dataset, after connecting to the server and fetching some rows from a hypothetical database table. Similar to the script I wrote for implementing the "FileIterator" class, all the methods for moving back and forth across the returned dataset are called in sequence. In addition, I listed the classes that connect to MySQL and fetch rows from the corresponding database table, therefore you can see how each class fits with each other. Quite simple, right?

Wrapping up

Unfortunately, this series has concluded now. If you've been reading each of the parts, then I hope you've acquired a deeper grounding in iterators in PHP 4/ PHP 5.

With all the written examples, I demonstrated how to use the iterator pattern for traversing different data structures, that is arrays, flat files and MySQL result sets, by utilizing nearly the same sets of methods, which is a good approach to coding standard classes for accessing many data types. As for other design patterns, mastering them takes a while, so be patient and digest the concepts very slowly. The reward is really worth it!



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: