Back to Fred Mac Donald's Blog

Tabular data with TCPDF and Internal Server 500 error

Tabular data with TCPDF and Internal Server 500 error

How to avoid the dreaded Internal Server Error (500) when trying to generate a PDF file using TCPDF with table tags and 1000's of rows of data?

Generating PDF files is pretty much part of any web application to allow the users to save certain reports and one of the most popular PDF generator projects is TCPDF.  It is probably one of the world's most active Open Source projects, used daily by millions of users and included in thousands of CMS and Web applications. Altho the application is very flexible it is not without its problems and the biggest problem is the fact that it is really heavy on server resources. The more complex and longer your pdf document is, the bigger the possibility that you will run into an “Internal Server Error 500” error while waiting for the pdf to be generated.

In the case of the XMS Systems E-Commerce and Point of Sale module, it is used to print stock reports of varying complexity. As you can imagine it is possible to have 1000’s of stock items each with a number of parameters that might need to be checked and calculated while retrieving the information from the database. Next, TCPDF needs to build the PDF file in memory while adding the rows of information and at the same time format each row to be human readable and well presented.

Recently I ran into this “Internal Server Error 500” and no matter what I did, I could not solve the problem. The usual suggestions to increase the available memory and max execution time did not work at all.

ini_set("memory_limit", "-1");
ini_set('max_execution_time', 0);
set_time_limit(0);

The bottleneck

I know my script is working as expected with no errors in the server logs. So the only conclusion is that somewhere in the script, there is something happening that causes a bottleneck that slow things down sufficiently to kill the server.

Looking at my script, there are three potential bottlenecks

  1. The MySQL query itself. It is fairly complex filtering for certain conditions before returning the results
  2. The loop that builds the actual HTML table that gets inserted into the pdf.
  3. A combination of the two. Effectively the MySQL query is part of the loop. So if the HTML generation takes a lot of resources and the server needs to run the query in between, it could potentially kill things.

After some experimenting, I found that the query is not the problem but the main resource killer is the fact that the HTML for the table generates a LOT of code and quickly uses up the resources.

Twofold solution

I decided to approach the solution by firstly splitting the process into two sections.

  1. Instead of running the MySQL query at the same time as running the loop building the list I will run the query in a separate process and save the results in a JSON file. Now the script does not have to “wait” for complex queries to run. It simply reads directly from a JSON file. One line at a time.
  2. Secondly, instead of using HTML and tables to render the rows of data, I implemented the TCPDF multicell() functionality. Basically, when using tables, you generate the HTML first in memory then TCPDF needs to convert it to pdf format in memory, generate the pdf file and do whatever you want it to do with it.

My problem was that I could not find any examples that demonstrate using multicell() to generate dynamic rows of data.

The solution

I am not going to discuss how to generate a JSON file or how to read data from it. That is not really relevant to the TCPDF problem.

Points to remember about using multicell() for TCPDF

  • It looks a lot worse than it is
  • It will not automatically split the rows over pages.
  • It can be positioned, like anything else in TCPDF
  • It will keep on adding cells one after the other till you make it stop.

In my example, I will not be showing you how to initiate and set the normal TCPDF parameters. The code deals only with the section that generates the data rows. I also include a header on each page because the TCPDF Page header is already used and I do not want to display that on every page. The “sub-header” as below is good enough.

Three important thing to note here is:

  • you need to count the rows you add to a page and manually add a new page using $pdf->AddPage();
  • you need to add $pdf->Ln(18);// Set a new line BEFORE each data-row
  • you will need to adjust the left position of the next data row if needed using: $pdf->SetX('13'); // Set the left position of the next row  
// Set MultiCell Defaults
$width = ‘’// Set this per cell
$height = '5';
$text = ‘’//Set this per cell
$border = '1';// 1/0 or L, T, R, B
$align = 'L';// L, C, R, J
$fill = '0'; //1/0
$Ln = '0';// 0=right, 1 = beginning of next line, 2=below
$floatX = '';
$floatY = '';
$resetH = 'true';


// Sub-Header
$pdf->SetFont('helvetica', 'n', 15);
$pdf->MultiCell('85', $height, 'SKU', $border, $align, $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('280', $height, 'Product', $border, $align, $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('68', $height, 'Price', $border, 'R', $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('68', $height, 'Pk Size', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('68', $height, 'Min', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('68', $height, 'Ord Lvl', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('68', $height, 'On Hnd', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('70', $height, 'Counted', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
$pdf->MultiCell('32', $height, 'Var', $border, 'C', $fill, $Ln, $floatX, $floatY, true);

// Set DataRows
$pdf->SetXY('13', '50');
$pdf->SetFont('helvetica', '', 10);
$pdf->SetTextColor(0,0,0);
$pdf->setCellPaddings(3, 3, 3, 3);

if (!empty($json))
    {
        $rows = '0'; // Counter for rows of processed data
        foreach($json as $item) //start loop for adding
            {
                $pdf->Ln(18);// Set a new line BEFORE each datarow
                $pdf->SetX('13'); // Set the left position of the next row                        
                $pdf->MultiCell('85', $height, $item['sku_prd'], $border, $align, $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('280', $height, substr($item['name_prd'],0,60), $border, $align, $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('68', $height, $item['prefix_cur'].$item['sto_price'], $border, 'R', $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('68', $height, $item['sto_pack_size'], $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('68', $height, $item['sto_min'], $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('68', $height, $item['sto_reorder_level'], $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('68', $height, $item['sto_qty_on_hand'], $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('70', $height, '', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                $pdf->MultiCell('32', $height, '', $border, 'C', $fill, $Ln, $floatX, $floatY, true);    
                $rows++;// increment with 1 after each datarow
                        
                if ($rows == 27) // Set max number of datarows per page
                    {
                        $pdf->AddPage();
                        $pdf->SetXY('13', '18');
                        
                        // Sub-Header for new page
                        $pdf->SetFont('helvetica', 'n', 15);
                        $pdf->MultiCell('85', $height, 'SKU', $border, $align, $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('280', $height, 'Product', $border, $align, $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('68', $height, 'Price', $border, 'R', $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('68', $height, 'Pk Size', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('68', $height, 'Min', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('68', $height, 'Ord Lvl', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('68', $height, 'On Hnd', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('70', $height, 'Counted', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                        $pdf->MultiCell('32', $height, 'Var', $border, 'C', $fill, $Ln, $floatX, $floatY, true);
                        
                        // Reset font and rows to DataRows
                        $pdf->SetFont('helvetica', '', 10);
                        $pdf->SetXY('13', '25');
                        $rows = '0';// reset to "0" ready for the new page
                    }                                    
            }
    }

The performance increase compared to using HTML tables are significant. The pdf generated by this script is 32 pages and it takes just a couple of seconds longer than my script displaying the data on the website

The script above results in an output like in the image below.

TCPDF file with tabular data without using tables

Written by:  - 14 Jan, 2018  
comments powered by Disqus
flashy