Совет по возвращению результатов из большого запроса, возвращающего ошибку. Допустимый размер памяти 734003200 байт исчерпан

Use getUnbufferedRow() for processing large result sets.

getUnbufferedRow()

This method returns a single result row without prefetching the whole result in memory as row() does. If your query has more than one row, it returns the current row and moves the internal data pointer ahead.

$query = $db->query("YOUR QUERY");

while ($row = $query->getUnbufferedRow()) {
    echo $row->title;
    echo $row->name;
    echo $row->body;
}

For use with MySQLi you may set MySQLi’s result mode to MYSQLI_USE_RESULT for maximum memory savings. Use of this is not generally recommended but it can be beneficial in some circumstances such as writing large queries to csv. If you change the result mode be aware of the tradeoffs associated with it.

$db->resultMode = MYSQLI_USE_RESULT; // for unbuffered results

$query = $db->query("YOUR QUERY");

$file = new \CodeIgniter\Files\File(WRITEPATH.'data.csv');

$csv = $file->openFile('w');

while ($row = $query->getUnbufferedRow('array'))
{
    $csv->fputcsv($row);
}

$db->resultMode = MYSQLI_STORE_RESULT; // return to default mode

Примечание:

When using MYSQLI_USE_RESULT all subsequent calls on the same connection will result in error until all records have been fetched or a freeResult() call has been made. The getNumRows() method will only return the number of rows based on the current position of the data pointer. MyISAM tables will remain locked until all the records have been fetched or a freeResult() call has been made.

You can optionally pass ‘object’ (default) or ‘array’ in order to specify the returned value’s type:

$query->getUnbufferedRow();         // object
$query->getUnbufferedRow('object'); // object
$query->getUnbufferedRow('array');  // associative array

freeResult()

It frees the memory associated with the result and deletes the result resource ID. Normally PHP frees its memory automatically at the end of script execution. However, if you are running a lot of queries in a particular script you might want to free the result after each query result has been generated in order to cut down on memory consumption.

$query = $thisdb->query('SELECT title FROM my_table');

foreach ($query->getResult() as $row) {
    echo $row->title;
}

$query->freeResult(); // The $query result object will no longer be available

$query2 = $db->query('SELECT name FROM some_table');

$row = $query2->getRow();
echo $row->name;
$query2->freeResult(); // The $query2 result object will no longer be available

На основе: MySQL: получить большой выбор по частям

Вы также можете попробовать получить данные порциями, используя предложение LIMIT.

Поскольку вы используете CodeIgniter 3, вот как вы можете это сделать.

Возможно, вам придется передать другой $orderByаргумент № 6 методу getChunk(...), если ваши объединенные таблицы имеют конфликтующие id имена столбцов.

То есть: $this->getChunk(..., ..., ..., 0, 2000, "tblProgram.id");

Решение:

<?php

class Csv_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function index()
    {
        $sql = <<< END
SELECT *
FROM `tblProgram`
JOIN `tblPlots` ON `tblPlots`.`programID`=`tblProgram`.`pkProgramID`
JOIN `tblTrees` ON `tblTrees`.`treePlotID`=`tblPlots`.`id`
END;

        $this->getChunk(function (array $chunk) {
            /*
             * Do something with each chunk here;
             * Do something with each chunk here;
             * log_message('error', json_encode($chunk));
             * */
        }, $this->db, $sql);
    }

    /*
     * Processes a raw SQL query result in chunks sending each chunk to the provided callback function.
     * */
    function getChunk(callable $callback, $DBContext, string $rawSQL = "SELECT 1", int $initialRowOffset = 0, int $maxRows = 2000, string $orderBy = "id")
    {
        $DBContext->query('DROP TEMPORARY TABLE IF EXISTS chunkable');
        $DBContext->query("CREATE TEMPORARY TABLE chunkable AS ( $rawSQL ORDER BY `$orderBy` )");

        do {

            $constrainedSQL = sprintf("SELECT * FROM chunkable ORDER BY `$orderBy` LIMIT %d, %d", $initialRowOffset, $maxRows);
            $queryBuilder = $DBContext->query($constrainedSQL);
            $callback($queryBuilder->result_array());
            $initialRowOffset = $initialRowOffset + $maxRows;

        } while ($queryBuilder->num_rows() === $maxRows);
    }

}