HomePHP 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.
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!
PHP 
/ 8 
