0

I'm inherit a mobile app with CMS backed PhP code.

There is an export function for list of location

Previously the function still working fine. Recently we got this error

enter image description here

I guest I got some memory leak but can not find out why.

This is my full .php file code:

<?php
date_default_timezone_set('Asia/Singapore');
session_start();
require_once("redirect.php");
include("Connections/conn.php");
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version    1.7.9, 2013-06-02
 */

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once 'Excel/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Singpost CMS")
                             ->setLastModifiedBy("Singpost CMS")
                             ->setTitle("Singpost Locations Document")
                             ->setSubject("Singpost Locations Document")
                             ->setDescription("Singpost Locations Document")
                             ->setKeywords("Singpost Locations Document")
                             ->setCategory("Singpost Locations Document");


// Add some data

//$sheet = array(array("1"=>"a","2"=>"b","3"=>"c"),array("1","2","3"));
$sheet = array();
$headers = array("ID","Type","Company Name","Town","Address","Postal Code","Contact No.","Latitude","Longitude","Notification","Status","Posting Box",
                    "Monday Open","Monday Opening Hours","Monday Collection Time",
                    "Tuesday Open","Tuesday Opening Hours","Tuesday Collection Time",
                    "Wednesday Open","Wednesday Opening Hours","Wednesday Collection Time",
                    "Thursday Open","Thursday Opening Hours","Thursday Collection Time",
                    "Friday Open","Friday Opening Hours","Friday Collection Time",
                    "Saturday Open","Saturday Opening Hours","Saturday Collection Time",
                    "Sunday Open","Sunday Opening Hours","Sunday Collection Time",
                    "Public Holiday Open","Public Holiday Opening Hours","Public Holiday Collection Time",
                    "Service 1",
                    "Service 2",
                    "Service 3",
                    "Service 4",
                    "Service 5",
                    "Service 6",
                    "Service 7",
                    "Service 8",
                    "Service 9",
                    "Service 10",
                    "Service 11",
                    "Service 12",
                    "Service 13",
                    "Service 14",
                    "Service 15",
                    "Service 16",
                    "Service 17",
                    "Service 18",
                    "Service 19",
                    "Service 20",
                    );
array_push($sheet,$headers);
$WHERE = "";
if(isset($_GET['type']))
{
    $WHERE = "WHERE type = '".$_GET['type']."'" ;
}
$select="select * from locate_singpost $WHERE order by id desc limit 900";
$qry=mysql_query($select);
while($row=mysql_fetch_array($qry))
{
    $r = array();
    $r[0] = $row['id'];
    $r[1] = $row['type'];
    $r[2] = $row['name']; 
    if(($row['type'] == "Speedpost Agent") || ($row['type'] == "Postal Agent")) $r[3] = $row['town'];
    else $r[3] = "N/A";
    $r[4] = $row['address']; 
    $r[5] = $row['postal_code']; 
    if(($row['type'] == "Speedpost Agent") || ($row['type'] == "Postal Agent")) $r[6] = $row['contact_number'];
    else $r[6] = "N/A";
    $r[7] = $row['latitude'];
    $r[8] = $row['longitude'];
    $r[9] = $row['notification'];
    $r[10] = $row['status'];
    $r[11] = $row['posting_box'];
    $r[12] = $row['mon_open'];
    $r[13] = $row['mon_opening_hours'];
    $r[14] = $row['mon_collection_time'];
    $r[15] = $row['tue_open'];
    $r[16] = $row['tue_opening_hours'];
    $r[17] = $row['tue_collection_time'];
    $r[18] = $row['wed_open'];
    $r[19] = $row['wed_opening_hours'];
    $r[20] = $row['wed_collection_time'];
    $r[21] = $row['thu_open'];
    $r[22] = $row['thu_opening_hours'];
    $r[23] = $row['thu_collection_time'];
    $r[24] = $row['fri_open'];
    $r[25] = $row['fri_opening_hours'];
    $r[26] = $row['fri_collection_time'];
    $r[27] = $row['sat_open'];
    $r[28] = $row['sat_opening_hours'];
    $r[29] = $row['sat_collection_time'];
    $r[30] = $row['sun_open'];
    $r[31] = $row['sun_opening_hours'];
    $r[32] = $row['sun_collection_time'];
    $r[33] = $row['ph_open'];
    $r[34] = $row['ph_opening_hours'];
    $r[35] = $row['ph_collection_time'];

    $i = 36;
    $sql_services="select * from services where singpost_id='".$row['id']."' limit 20";
    $res_services=mysql_query($sql_services);
    $fetch_services = array();
    $srv = array();
    while($fetch_services=mysql_fetch_array($res_services))
    {
        $srv[]=$fetch_services['services'];
    }



    for($i = 36; $i <= 55; $i++)
    {
        $j = $i - 36;
        if(isset($srv[$j]))
        {
            $r[$i] = $srv[$j];
        }
        else
        {
            $r[$i] = "";
        }
    }

    array_push($sheet,$r);
    unset($r);
    unset($srv);
    unset($fetch_services);
    unset($res_services);
}

/*
function getOpeningHours($row)
{
    $monday = "Monday Open: ".formatTime($row['mon_opening_hours'])." Collection: ".formatTime($row['mon_collection_time']);
    $tuesday = "Tuesday Open: ".formatTime($row['tue_opening_hours'])." Collection: ".formatTime($row['tue_collection_time']);
    $wednesday = "Wednesday Open: ".formatTime($row['wed_opening_hours'])." Collection: ".formatTime($row['wed_collection_time']);
    $thursday = "Thursday Open: ".formatTime($row['thu_opening_hours'])." Collection: ".formatTime($row['thu_collection_time']);
    $friday = "Friday Open: ".formatTime($row['fri_opening_hours'])." Collection: ".formatTime($row['fri_collection_time']);
    $saturday = "Saturday Open: ".formatTime($row['sat_opening_hours'])." Collection: ".formatTime($row['sat_collection_time']);
    $sunday = "Sunday Open: ".formatTime($row['sun_opening_hours'])." Collection: ".formatTime($row['sun_collection_time']);
    $ph = "Public Holiday Open: ".formatTime($row['ph_opening_hours'])." Collection: ".formatTime($row['ph_collection_time']);
    return $monday."\n".$tuesday."\n".$wednesday."\n".$thursday."\n".$friday."\n".$saturday."\n".$sunday."\n".$ph;
}

function formatTime($time)
{
    $firsttwo = substr($time,0,2);
    $lasttwo = substr($time,2,2);
    if(strlen($time)==4)return $firsttwo.":".$lasttwo;  
}

function getService($id,$name)
{
    $select="select * from services where singpost_id = '".$id."' and services LIKE '%".$name."%' limit 1";
    $qry=mysql_query($select);
    $row = mysql_fetch_row($qry);
    if($row) return "Yes";
    else return "No";
}
*/

$objPHPExcel->getActiveSheet()->fromArray($sheet, "", 'A1');


$lastRow = $objPHPExcel->getActiveSheet()->getHighestRow();
for ($row = 1; $row <= $lastRow; $row++) {
    $cell = $objPHPExcel->getActiveSheet()->getCell('N'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('N'.$row)->getNumberFormat()->setFormatCode('0000');

    $cell = $objPHPExcel->getActiveSheet()->getCell('O'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('O'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('Q'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('Q'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('R'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('R'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('T'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('T'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('U'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('U'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('W'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('W'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('X'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('X'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('Z'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('Z'.$row)->getNumberFormat()->setFormatCode('0000');   

    $cell = $objPHPExcel->getActiveSheet()->getCell('AA'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AA'.$row)->getNumberFormat()->setFormatCode('0000');  

    $cell = $objPHPExcel->getActiveSheet()->getCell('AC'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AC'.$row)->getNumberFormat()->setFormatCode('0000');  

    $cell = $objPHPExcel->getActiveSheet()->getCell('AD'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AD'.$row)->getNumberFormat()->setFormatCode('0000');  

    $cell = $objPHPExcel->getActiveSheet()->getCell('AF'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AF'.$row)->getNumberFormat()->setFormatCode('0000');  

    $cell = $objPHPExcel->getActiveSheet()->getCell('AG'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AG'.$row)->getNumberFormat()->setFormatCode('0000');  

    $cell = $objPHPExcel->getActiveSheet()->getCell('AI'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AI'.$row)->getNumberFormat()->setFormatCode('0000');  

    $cell = $objPHPExcel->getActiveSheet()->getCell('AJ'.$row);
    if(is_numeric($cell->getValue()))
        $objPHPExcel->getActiveSheet()->getStyle('AJ'.$row)->getNumberFormat()->setFormatCode('0000');  


}

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Singpost Locations');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="locations.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
halfer
  • 18,701
  • 13
  • 79
  • 158
Lê Khánh Vinh
  • 2,329
  • 4
  • 24
  • 63
  • 1
    Possible duplicate of [Allowed memory size of 33554432 bytes exhausted (tried to allocate 43148176 bytes) in php](http://stackoverflow.com/questions/415801/allowed-memory-size-of-33554432-bytes-exhausted-tried-to-allocate-43148176-byte) – scrowler Feb 08 '17 at 02:38
  • Suggested reading: http://stackoverflow.com/help/mcve – wogsland Feb 08 '17 at 02:50
  • Thanks for your help. Seem solution all suggest to increase memory limit. I'd like to look through the code to see if there is something wrong – Lê Khánh Vinh Feb 08 '17 at 03:00

0 Answers0