My site has a facility to download a potentially large body of data from my database as a CSV file. This is currently implemented using a view.csv.php file that serves this data by first setting the MIME encoding and content disposition as follows, then fetching the data using JDatabaseQuery::loadObjectList() and rendering to stdout:
$document = &JFactory::getDocument();
$filename = 'myfile.csv';
JResponse::setHeader('Content-disposition', 'inline; filename="myfile.csv"', true);
With large enough data sets though, I get a PHP 'Allowed memory size of 134217728 bytes exhausted' error. Currently, I observe this happening during the rendering of the data to output. However, I am also concerned that if even larger datasets were requested, the error might occur when fetch the data from the database - I have not tested this to date.
One option I am considering to solve the immediate problem (errors during rendering) is to write the CSV content to a temporary file and return that somehow, rather than buffering the results directly to stdout, though I am unsure off-hand of how to return the file contents as the HTTP response once written, or if this will in fact solve the issue anyway.
So, my question is two-fold:
- Is using a temporary file the right approach to the memory-exhaustion-during-rendering issue, and if so, how do I subsequently return the content of this file as the HTTP response?
- Is there any mechanism to process SQL result sets one row at a time via JDatabase + JDatabaseQuery, rather than just fetching all of the data in bulk? I thought such a means existed, but on reviewing the Joomla API, it's not jumping out at me just now.