Iterators in the Simplest Sense: Traversing Data Structures in PHP 5 - Traversing database result sets: building a MySQL iterator class
(Page 4 of 4 )
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!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 6 
