Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts

    mysql query results iterator

    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
    Last edited by eak; 01-02-2006 at 07:58 AM.
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •