mysql query results iterator

[eak]

mysqlIterator lets you use a foreach loop to iterate your query results.

This works in php5+ since it implements Iterator which is part of the SPL

PHP Code:

class mysqlIterator implements Iterator{

    const    RESULT_ASSOC    = 0,
            RESULT_ARRAY    = 1,
            RESULT_BOTH        = 2,
            RESULT_OBJECT    = 3;

    private $results         = null,
            $fetchMethod    = null,
            $index             = 0,
            $numrows         = 0,
            $valid             = FALSE,
            $errors            = Array();

    /**
        @param query_result - results from mysql_query()
        @param fetchMethod  - optional parameter.  defauls to RESULT_BOTH.
            This parameter is used when fetching data.  see current().
    */
    public function __construct($query_result,$fetchMethod=mysqlIterator::RESULT_BOTH){
        if(is_resource($query_result)){
            if(get_resource_type($query_result)=="mysql result"){
                $this->fetchMethod=$fetchMethod;
                $this->results=$query_result;
                $this->numrows=mysql_num_rows($this->results);
            } else {
                $e=new Exception(__CLASS__." __construct argument is not a mysql result");
                $this->errors[]=$e->__toString();
                return false;
            }
        } else {
            $e=new Exception(__CLASS__." __construct argument is not a resource");
            $this->errors[]=$e->__toString();
            return false;
        }
    }

    public function __destruct(){
        if(is_resource($this->results) && get_resource_type($query_result)=="mysql result")
            mysql_free_result($this->results);
        unset($this->valid);
        unset($this->index);
        unset($this->numrows);
        unset($this->results);
    }

    /**
        This method moves the internal pointer of the query results.
        @param index - int row number starting with zero as usual.
    */
    private function goto($index){
        if($this->numrows==0){
            return false;
        } else {
            return mysql_data_seek($this->results,$index);
        }
    }

    /**
        This method resets the pointer of the query results back to the first row.
    */
    public function rewind(){
        $this->index=0;
        $this->valid = $this->goto(0);
    }

    /**
        @return array/object - current row of query results.
            The fetch method used to retrieve the data depends on the fetchMethod.
            The row could be returned as an associative array, enumerated array,
            combination of associative and enumerated, or an object.
    */
    public function current(){
        $this->goto($this->index);
        switch($this->fetchMethod){
            case mysqlIterator::RESULT_BOTH:
                return mysql_fetch_array($this->results);
            break;
            case mysqlIterator::RESULT_ASSOC:
                return mysql_fetch_assoc($this->results);
            break;
            case mysqlIterator::RESULT_ARRAY:
                return mysql_fetch_row($this->results);
            break;
            case mysqlIterator::RESULT_OBJECT:
                return mysql_fetch_object($this->results);
            break;
            default:
                return false;
        }
    }

    /**
        @return index of current row in query results.
    */
    public function key(){
        return $this->index;
    }
    
    /**
        This method sets the valid member variable.
        if there are more rows then valid is true.
        if not then valid is false.
    */
    public function next(){
        $this->valid = ( ++$this->index < $this->numrows );
    }
    
    /**
        @return boolean - state of the valid member variable.
    */
    public function valid(){
        return $this->valid;
    }
    
    /**
        This is just a sample toString method.
        It can be changed to what ever your needs are.
    */
    public function __toString(){
        $tmp=new mysqlIterator($this->results,mysqlIterator::RESULT_ASSOC);
        $returnString="";
        foreach($tmp as $index=>$record){
            $returnString.="<dl>";
            foreach($record as $key=>$value){
                $returnString.="<dt>$key</dt><dd>$value</dd>";
            }
            $returnString.="</dl>";
        }
        return $returnString;
    }

    /**
        @return string - error messages.
    */
    public function getErrorMsg(){
        $error="";
        foreach($this->errors as $key=>$msg){
            if($key>0)$error.="<br />";
            $error.=$msg;
        }
        return $error;
    }
}

?> 


the following is an example of how to use it.

PHP Code:

$content="";
$database = new mysqlIterator(mysql_query("show databases;"),mysqlIterator::RESULT_OBJECT);


foreach($database as $current){
    $content.=$current->Database."<br />";
    //$content.=$current["Database"]."<br />";
    //$content.=$current[0]."<br />";
    //These are the other ways to access the columns.
    //Just change the fetchMethod to the one you want.
    //if you leave out the fetchMethod, it will return the 
    //row as if you used MYSQL_BOTH.
}

echo $content;