Fatal error: Allowed memory size of ...

[eluser]paynterc[/eluser]
I'm writing an application that is meant to pull large data sets from a table and export them as either .xls or .tab files.

It works ok for smaller result sets (I've had success up to 300 rows), but I get an error when I get up to the 30,000 row range (could be smaller, I'm not sure exactly what the limit is)

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1 bytes) in /Library/WebServer/Documents/codeigzv09/system/database/drivers/mysql/mysql_result.php on line 168

I upped memory_limit to 256M in my php.ini file, but it's still running out of memory. Now I get:

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 5 bytes) in /Library/WebServer/Documents/codeigzv09/system/database/drivers/mysql/mysql_result.php on line 168


The funny thing is that this works ok when I write the code outside of CodeIgniter. I wrote a one page script that runs this query then outputs the results to a .xls file. Everything worked fine. What is it about CodeIgniter that it's running out of memory?

I'm running php 5.2.4 on a Mac. I get the same problem in Safari and Firefox.

Here's the non-CI code, the code that works. Some names have been changed to protect the innocent:

$sql="
SELECT *
FROM (`mytable`)
WHERE mytable.billingdate >= '2008-03-16'
AND mytable.billingdate <= '2008-03-16'
AND `org` = '8888'
";
$result=mysql_query($sql);


$headerexport = "Content-Disposition: attachment; filename=\"Shipments_".time().".xls\"";
header('Content-Type: application/vnd.ms-excel');
header($headerexport);

?&gt;
<center>
<table border="0" class="bigger_table">
<tr>
<td class="gray_dark_bgcolor center">

<table border="0" width="100%">

<tr class="blue_dark_bgcolor">
<td align="left" width=100><font size="-2">Cust#</font></td>
<td align="left"><font size="-2">Cust Name</font></td>
<td align="left"><font size="-2">PO#</font></td>
<td align="left"><font size="-2">Org#</font></td>

</tr>
&lt;?

while($thisrow=mysql_fetch_array($result)){
?&gt;
<tr class="&lt;?= $tableclass ?&gt;">
<td align="left">&lt;?= $thisrow['custnumber'] ?&gt;</td>
<td align="left">&lt;?= $thisrow['custname'] ?&gt;</td>
<td align="left">&lt;?= $thisrow['po'] ?&gt;</td>
<td align="left">&lt;?= $thisrow['org'] ?&gt;</td>

</tr>
&lt;?

}
?&gt;

</table>
</td>
</tr>
</table>
</center>

[eluser]Sam Dark[/eluser]
Please show CI code you are using.

[eluser]paynterc[/eluser]
From the controller:
if(isset($_POST['form'])){

$data_qry['query'] = $this->db_model->getSearchResults($_POST['form']);
}else{
$data_qry['query'] = null;

}



$data_exp['headerexport'] = "Content-Disposition: attachment; filename=\"Shipments_".time().".xls\"";
$this->load->view("exportheader",$data_exp);
$this->load->view($_SESSION['shipsearch']['r_view'],$data_qry);


From model:

&lt;?php
class DB_model extends Model {

function DB_model()
{
parent::Model();

}

function getSearchResults ($criteria)
{


$sumt="summary_table";



$this->db->select('*');



if($criteria['startdate']!='' && $criteria['enddate']!=''){
$datecrit=array($sumt . '.billingdate >=' => date("Y-m-d",strtotime($criteria['startdate'])), $sumt.'.billingdate <=' => date("Y-m-d",strtotime($criteria['enddate'])));
$this->db->where($datecrit);
}elseif($criteria['startdate']!=''){
$datecrit=array($sumt . '.billingdate >=' => date("Y-m-d",strtotime($criteria['startdate'])));
$this->db->where($datecrit);
}

if($criteria['org']!=''){
$orgcrit=array('org'=>$criteria['org']);
$this->db->where($salesorgcrit);
}





$query = $this->db->get($sumt);
$str = $this->db->last_query();
//echo "<br>" . $str . "<p>";
if ($query->num_rows() > 0) {
return $query;
} else {
return null;
}


}

}


?&gt;



From the view:
<center>
<table border="0" class="bigger_table">
<tr>
<td class="gray_dark_bgcolor center">

<table border="0" width="100%">

<tr class="blue_dark_bgcolor">
<td align="left" width=100><font size="-2">Cust#</font></td>
<td align="left"><font size="-2">Cust Name</font></td>
<td align="left"><font size="-2">PO#</font></td>
<td align="left"><font size="-2">ORG#</font></td>

</tr>


&lt;?php
if($query){
$i=1;
foreach($query->result() as $myrow1b){
($i % 2) ? $tableclass = "gray_lite_bgcolor_row" : $tableclass = "blue_med_bgcolor_row";
?&gt;
<tr class="&lt;?= $tableclass ?&gt;">
<td align="left">&lt;?= $myrow1b->custnumber ?&gt;</td>
<td align="left">&lt;?= $myrow1b->custname ?&gt;</td>
<td align="left">&lt;?= $myrow1b->ponumber ?&gt;</td>
<td align="left">&lt;?= $myrow1b->org ?&gt;</td>
</tr>
&lt;?php
$i++;
}
}
?&gt;
</table>
</td>
</tr>
</table>
</center>

[eluser]wiredesignz[/eluser]
From experience I know CI will create 4 copies of your resultset compared to the single resultset in non CI code. ie:
The CI database driver (MySQL in this case) has the original resultset, the model has a copy ($query), the controller has a copy ($data_qry) and finally the view has its copy ($query).

To avoid this you can access the Model directly from your View, Store the resultset in your Model and only pass a reference to this into your View.

Even better for large datasets you could create a function in your model that iterates the MySQL result directly.

[eluser]megabyte[/eluser]
PHP if its set up to allow you to change it will let you change the memory limit.

Code:

<?php
ini_set( 'memory_limit', $newLimit . 'M' );
?>

Search the internet if you need a bit more info. I found it very useful when using the GD library to manipulate images as you most often need much more memory than is available.

[eluser]GSV Sleeper Service[/eluser]
I know this is nothing to do with your problem, but why are you saving a bunch of HTML as an .xls file?

[eluser]paynterc[/eluser]
Oh, that's just to save time. If you export a simple html table to a .xls file, Excel will open it like a regular spreadsheet. This way I can use the same code for the browser and the Excel file.