[FIXED] Codeigniter loop to chunk query only ever gets the first chunk of rows?

Issue

I'm trying to get a large amount of data by grabbing it in chunks from the database and writing it to CSV. For some reason the code below is only writing the first chunk (2000 rows) to CSV. I have my $chunk and $limit variables writing to a text file, and those are going through the loop and writing out the correct values. So why isn't $result=$this->db->get('tblProgram', $chunk, $offset)->result_array(); grabbing the next chunks?

Can you not run $this->db->get('tblProgram', $chunk, $offset)->result_array(); multiple times with different offsets? How else would I loop through the results?

I can confirm that I have more than 200k rows returned from the query, and that if I set chunk to something different, I'm still only getting the first chunk returned.

//Get rows from tblTrees based on criteria set in options array in downloadable format
    public function get_download_tree_data($options=array(), $rand=""){

        $this->db->reset_query();
        $this->db->join('tblPlots','tblPlots.programID=tblProgram.pkProgramID');
        $this->db->join('tblTrees','tblTrees.treePlotID=tblPlots.id');
        $this->db->order_by('tblTrees.id', 'ASC');

       // $allResults=$this->db->count_all_results('tblProgram', false);
        $allResults=200000;
        $offset=0;
        $chunk=2000;
        $treePath=$this->config->item('temp_path')."$rand/trees.csv";
        $tree_handle=fopen($treePath,'a');
        $tempPath=$this->config->item('temp_path')."$rand/trees.txt";
        $temp_handle=fopen($tempPath,'a');
        
        while (($offset<$allResults)) {
            $temptxt=$chunk." ".$offset."\n";
            fwrite($temp_handle,$temptxt);

            $result=$this->db->get('tblProgram', $chunk, $offset)->result_array();
            foreach ($result as $row) {
                fputcsv($tree_handle, $row);
            }    
            $offset=$offset+$chunk;
        }
                    
            fclose($tree_handle);
            fclose($temp_handle);
            return array('resultCount'=>$allResults);
 
    }

Solution

https://github.com/bcit-ci/CodeIgniter/blob/develop/system/database/DB_query_builder.php

Looks like calling the get method resets your model:

    public function get($table = '', $limit = NULL, $offset = NULL)
    {
        if ($table !== '')
        {
            $this->_track_aliases($table);
            $this->from($table);
        }

        if ( ! empty($limit))
        {
            $this->limit($limit, $offset);
        }

        $result = $this->query($this->_compile_select());
        $this->_reset_select();
        return $result;
    }

I'd imagine this is the case of any version of ci.

Answered By - Kisaragi

Answer Checked By - Marie Seifert (PHPFixing Admin)