Back to Fred Mac Donald's Blog
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);
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
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.
I decided to approach the solution by firstly splitting the process into two sections.
My problem was that I could not find any examples that demonstrate using multicell() to generate dynamic rows of data.
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
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:
// 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.