Fatal error: Allowed memory size of ... - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Fatal error: Allowed memory size of ... (/showthread.php?tid=7121) |
Fatal error: Allowed memory size of ... - El Forum - 03-25-2008 [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); ?> <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> <? while($thisrow=mysql_fetch_array($result)){ ?> <tr class="<?= $tableclass ?>"> <td align="left"><?= $thisrow['custnumber'] ?></td> <td align="left"><?= $thisrow['custname'] ?></td> <td align="left"><?= $thisrow['po'] ?></td> <td align="left"><?= $thisrow['org'] ?></td> </tr> <? } ?> </table> </td> </tr> </table> </center> Fatal error: Allowed memory size of ... - El Forum - 03-25-2008 [eluser]Sam Dark[/eluser] Please show CI code you are using. Fatal error: Allowed memory size of ... - El Forum - 03-25-2008 [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: <?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; } } } ?> 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> <?php if($query){ $i=1; foreach($query->result() as $myrow1b){ ($i % 2) ? $tableclass = "gray_lite_bgcolor_row" : $tableclass = "blue_med_bgcolor_row"; ?> <tr class="<?= $tableclass ?>"> <td align="left"><?= $myrow1b->custnumber ?></td> <td align="left"><?= $myrow1b->custname ?></td> <td align="left"><?= $myrow1b->ponumber ?></td> <td align="left"><?= $myrow1b->org ?></td> </tr> <?php $i++; } } ?> </table> </td> </tr> </table> </center> Fatal error: Allowed memory size of ... - El Forum - 03-25-2008 [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. Fatal error: Allowed memory size of ... - El Forum - 03-26-2008 [eluser]megabyte[/eluser] PHP if its set up to allow you to change it will let you change the memory limit. Code: <?php 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. Fatal error: Allowed memory size of ... - El Forum - 03-26-2008 [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? Fatal error: Allowed memory size of ... - El Forum - 03-26-2008 [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. |