Max Execution time Error after 1 year working

Question

I have the code below, and it was working fine for over an year now, but today it's giving a max execution time error. I tried increasing the numbers on php.ini but it did not resolve. Also tried adding the max_execution_time directly into the php code and it did not help as well. Can anyone help me fingure out why would it break now? When I tried to access it from the webpage to download the pdf file it says "The webpage at https://webpage.address.com/quicklistpdf.php?user=$user might be temporarily down or it may have moved permanently to a new web address. ERR_INVALID_RESPONSE

Thanks.

 <?php

        require_once 'C:/phpexcel/Classes/PHPExcel.php';
        require_once 'C:/phpexcel/Classes/PHPExcel/IOFactory.php';

        $objPHPExcel = new PHPExcel();

        // Set properties
        $objPHPExcel->getProperties()->setCreator("Samara Galvao");
        $objPHPExcel->getProperties()->setLastModifiedBy("Samara Galvao");
        $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Report Test");
        $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Report Test");
        $objPHPExcel->getProperties()->setDescription("Test report for Office 2007 XLSX, generated using PHP classes.");

        //styling borders
         $styleArray = array(
              'borders' => array(
                  'allborders' => array(
                      'style' => PHPExcel_Style_Border::BORDER_THIN
                  )
              )
          );
        $objPHPExcel->getDefaultStyle()->applyFromArray($styleArray);



        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle('QuickList');
        $objPHPExcel->setActiveSheetIndex(0);
        $insideTitle='QuickList';
        $objPHPExcel->getActiveSheet()->setCellValue('B1', $insideTitle);
        $objPHPExcel->getActiveSheet()->setCellValue('G1', PHPExcel_Shared_Date::PHPToExcel( gmmktime(0,0,0,date('m'),date('d'),date('Y')) ));
        $objPHPExcel->getActiveSheet()->getStyle('G1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15);

        // Set column widths
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(9);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(7);

        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(7);





        // Set fonts
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(false);
        //$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);

        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);


        //return records for the table

        require_once('quicklistQueries.php');


        $result=pg_query($sql_data);

        $rowNumber=3;
        while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC)){
            //-----sets STUDY HEADER-----------------
            $col ='A';
            $studyHeader=array('Agent', 'Study', 'IND No', 'IND Sponsor', 'Current Status');
            foreach ($studyHeader as $study){


                $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$study);


                $col++;
            }
            $rowNumber++;
            //---------set STUDY DATA---------------------------------
            $col='A';
            $rows=array($row['agent'], $row['study'], $row['ind_no'],$row['sponsor'], $row['status']);
            foreach($rows as $cell){
                $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell);
                    $col++;
                }
                $rowNumber++;

            $result2=pg_query($sql_data2." WHERE study = '".$row['study']."'");
            while ($row2=pg_fetch_array($result2, NULL, PGSQL_ASSOC))
                //---------set SITE HEADER---------------------------------
            {
                $col ='B';
                $siteHeader=array('Site', 'PI', 'Status', 'IRBappcr', 'OHRP', 'IB', 'PROT', 'IC', 'Global1572', '1572', 'IBC', 'OBARAC', 'DSA');
                foreach ($siteHeader as $site){
                $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$site);
                $col++;
            }
            $rowNumber++;
            //---------set SITE DATA---------------------------------
            $col='B';
                $rows2=array($row2['name'], $row2['pi'], $row2['status'], $row2['irbappcr'], $row2['ohrp'], $row2['ib'], $row2['prot'], $row2['ic'], $row2['global1572'], $row2['1572'], $row2['ibc'], $row2['obarac'], $row2['dsa']);
                foreach($rows2 as $cell2){
                    $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell2);
                $col++;
                }
            $rowNumber++;

                $result3=pg_query($sql_data3." WHERE amtrackid= '".$row2['amtrackid']."'"); 
        //---------set LAB HEADER---------------------------------      
                $col='C';
                $labsHeader=array('Lab', 'CAP', 'CLIA', 'LNV');
                foreach ($labsHeader as $labs){
                $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$labs);
                $col++;
            }
            $rowNumber++;
                while ($row3=pg_fetch_array($result3, NULL, PGSQL_ASSOC)){
        //---------set LAB DATA---------------------------------
                    $col ='C';


                    $rows3=array($row3['name'], $row3['cap'], $row3['clia'],$row3['lnv']);
                    foreach($rows3 as $cell3){
                        $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell3);
                        $col++;
                    }

            $rowNumber++;

                }
                $result4=pg_query($sql_data4." WHERE amtrackid= '".$row2['amtrackid']."'"); 
                //---------set INVESTIGATOR HEADER---------------------------------
                $col ='C';
                    $invHeader=array('Investigator', 'CV', 'ML', 'FD', 'HSPT', 'DOT');
                    foreach ($invHeader as $invs){
                $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$invs);
                $col++;
            }
            $rowNumber++;
                while ($row4=pg_fetch_array($result4, NULL, PGSQL_ASSOC)){
                    //---------set INVESTIGATOR DATA---------------------------------

            $col='C';
                     $rows4=array($row4['name'], $row4['cv'], $row4['ml'],$row4['fd'], $row4['hspt'],$row4['dot']);
                     foreach($rows4 as $cell4){
                         $objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell4);
                        $col++;
                     }
            $rowNumber++;
                }
            }
        }



        //eliminate gridlines
        //$objPHPExcel->getActiveSheet()->setShowGridlines(false);

        // Set fills
        $objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_NONE);
        //$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setARGB('FF808080');

        /*
        // Set header and footer. When no different headers for odd/even are used, odd header is assumed.
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BReport&RPrinted on &D');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
        */

        // Set page orientation and size
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

        $objWorksheet = $objPHPExcel->getActiveSheet();


        // Save Excel 2007 file
        //$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
        ob_end_clean();
        //$objWriter->setIncludeCharts(TRUE);
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        $filename='QuickList_';
        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/pdf');
        $filedate=date('dmY');
        //header('Content-Type: application/xlsx');
        header('Content-Disposition: attachment;filename='.$filename.$filedate.'.pdf');
        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->save('php://output');


   ?>

was there a service upgrade done within that year / recently? — Funk Forty Niner, Sep 21 '17 at 18:43
you seem to be using this locally, correct? the `C:/` suggests it. — Funk Forty Niner, Sep 21 '17 at 18:44
We may never know what's causing trouble. I suggest you to comment all lines and uncomment block by block starting from the begining. This will give you which block of code is causing long execution time. Then you optimize that part only. — Ergec, Sep 21 '17 at 18:47
I have the same code working for exporting to Excel. Just the pdf is not. Can it be a problem with the pdf library? — Samara Galvão, Sep 21 '17 at 19:14

score 0 · Accepted Answer · answered Oct 18 '17 at 09:22

A few things I noticed in your code that could be adding load time or messing things up for you. Just for starters, you can reduce your code with a quick for loop.

This

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(9);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(7);
    ...
    $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(7);

Can be changed to

$col = 'A';
$col_width = 7;
for($i=0;$i<=17;$i++) {
    if($col === ('A'||'C')) {
         $col_width = 8;
    } else if($col === 'B') {
         $col_width = 9;
    }
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth($col_width);        
    $col++;
}

Secondly, if your going to be setting $objWorksheet = $objPHPExcel->getActiveSheet(); then you can put it up top and replace all $objPHPExcel->getActiveSheet() with $objWorksheet. For example, this code would then look like this:

$col = 'A';
$col_width = 7;
for($i=0;$i<=17;$i++) {
    if($col === ('A'||'C')) {
         $col_width = 8;
    } else if($col === 'B') {
         $col_width = 9;
    }
    $objWorksheet->getColumnDimension($col)->setWidth($col_width);        
    $col++;
}

and lastly, to possibly answer your question, you need to clean, then set headers, then use the writer and finally detach (exit) from the workbook. Well, that is how it worked for me anyway.

Update this code like so.

//set after doc creation but before setting headers
ob_end_clean();

$filedate=date('dmY');
$filename='QuickList_'.$filedate.'.pdf';

//Redirect output to a client’s web browser (PDF)
header('Content-Type: application/pdf');

//Redirect output to a client’s web browser (Excel2007)
//header('Content-Type: application/xlsx');

header('Content-Disposition: attachment;filename='.$filename);
//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

//Save Excel 2007 file
//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF')->save('php://output');

//dont forget to exit
exit;//to detach from objWriter properly

If that does not fix the issue, then I would look at your quicklistQueries.php to see if your queries are what is causing the overuse of execution time. It may need a flush() or garbage collection before or between iterations of your results.

$result=pg_query($sql_data);
//flush database and clear memory then go through results

Thank you so much. Will try it and let you know – Samara Galvão Oct 18 '17 at 19:35 — Samara Galvão, Oct 18 '17 at 19:35

Max Execution time Error after 1 year working

1 Answers1