We help IT Professionals succeed at work.

Populate PDF with Data from MySql Database Using Php

Ridgejp
Ridgejp asked
on
1,826 Views
Last Modified: 2017-04-13
Hi,

I'm using TCPDF to output a PDF using PHP and have now finished building the framework for the page which will contain the customer records. I need help on how to output the customer records a page at a time and also with the placement of the data within the PDF form itself.

The code for my PDF is enclosed to help visualise the process - I've also printed out the PDF and handwritten the data onto the form to give a feeling to how the completed form needs to look.

<?php
// Database Connection File
require_once('latch.php');

// Include the main TCPDF library (search for installation path).
require_once('tcpdf_include.php');

// create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Ridgejp');
$pdf->SetTitle('Print Run');
$pdf->SetSubject('Print Run');
$pdf->SetKeywords('');

// set default header data
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 123', PDF_HEADER_STRING, array(0,64,255), array(0,64,128));
//$pdf->setFooterData(array(0,64,0), array(0,64,128));

// set header and footer fonts
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
//$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// set default monospaced font
//$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
//$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
//$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
//$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
	require_once(dirname(__FILE__).'/lang/eng.php');
	$pdf->setLanguageArray($l);
}

// ---------------------------------------------------------

// set default font subsetting mode
$pdf->setFontSubsetting(true);

// Set font
// dejavusans is a UTF-8 Unicode font, if you only need to
// print standard ASCII chars, you can use core fonts like
// helvetica or times to reduce file size.
$pdf->SetFont('helvetica', '', 10, '', true);

// Add a page
// This method has several options, check the source code documentation for more information.
$pdf->AddPage();

// BOXES FOR POSTAL MARK & DELIVERY ADDRESS
$pdf->Rect(  10,  15,  90, 15);
$pdf->Rect(  10,  15,  90, 50);

// BOXES FOR POSTAL MARK & RETURNS LABEL
$pdf->Rect(  110,  15,  90, 15);
$pdf->Rect(  110,  15,  90, 50);

// BOXES FOR INVOICE No. & DATE
$pdf->Rect(  10,  100,  30, 8);
$pdf->Rect(  40,  100,  50, 8);
$pdf->Rect(  10,  108,  30, 8);
$pdf->Rect(  40,  108,  50, 8);

// BOXES FOR QTY & CAT ID
$pdf->Rect(  10,  125,  190, 10);
$pdf->Rect(  12,  127,  8, 6);
$pdf->Rect(  21,  127,  177, 6);

// LINE FOR MIDPOINT SEPERATOR
$pdf->Rect(  10,  150,  190, 0);

// BOXES FOR QTY & CAT ID & DESCRIPTION
$pdf->Rect(  10,  152,  20, 10);
$pdf->Rect(  30,  152,  130, 10);
$pdf->Rect(  160, 152,  40, 10);
$pdf->Rect(  10,  162,  20, 45);
$pdf->Rect(  30,  162,  130, 45);
$pdf->Rect(  160, 162,  40, 45);

// BOXES FOR VAT & TOTAL AMOUNT
$pdf->Rect(  120,  245,  40, 25);
$pdf->Rect(  160,  245,  40, 25);
$pdf->Rect(  120,  257.5,  80, 0);

// CONTENT FOR FIXED TITLE FIELDS
$pdf->SetFont('helvetica','',10);
$pdf->setXY(111, 33);
$pdf->Write(0, 'RETURNS ADDRESS:');

$pdf->setXY(111, 39);
$pdf->Write(0, 'MyCompany.com');

$pdf->setXY(111, 43);
$pdf->Write(0, 'Unit 9 An Industrial Estate');

$pdf->setXY(111, 47);
$pdf->Write(0, 'The Street');

$pdf->setXY(111, 51);
$pdf->Write(0, 'Stoke on Trent');

$pdf->setXY(111, 55);
$pdf->Write(0, 'Staffordshire');

$pdf->setXY(111, 59);
$pdf->Write(0, 'ST31 1PJ');

$pdf->setXY(10, 102);
$pdf->Write(0, 'Invoice Number:');

$pdf->setXY(10, 110);
$pdf->Write(0, 'Invoice Date:');

$pdf->setXY(124, 102);
$pdf->Write(0, 'email: customer.services@example.com');

$pdf->setXY(169, 110);
$pdf->Write(0, 'tel: 01872 123456');

$pdf->setXY(11, 155);
$pdf->Write(0, 'Quantity:');

$pdf->setXY(31, 155);
$pdf->Write(0, 'Product Description:');

$pdf->setXY(175, 155);
$pdf->Write(0, 'Unit Price (£)');

$pdf->setXY(121, 249);
$pdf->Write(0, 'VAT Amount (£)');

$pdf->setXY(121, 261);
$pdf->Write(0, 'Total Price (£)');



// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 24HR
$pdf->Image
( $img = 'images/rm48.jpeg'         // PATH/TO/IMAGE_FILE
, 11             // $x
, 16             // $y
, 88             // WIDTH
, 13             // HEIGHT
, 'jpeg'         // TYPE
)
;

// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 24HR
$pdf->Image
( $img = 'images/rm48.jpeg'         // PATH/TO/IMAGE_FILE
, 111            // $x
, 16             // $y
, 88             // WIDTH
, 13             // HEIGHT
, 'jpeg'         // TYPE
)
;

// IMAGE SETTINGS FOR TCH Logo
$pdf->Image
( $img = 'images/tch_logo.jpeg'         // PATH/TO/IMAGE_FILE
, 109            // $x
, 70             // $y
, 92             // WIDTH
, 0              // HEIGHT
, 'jpeg'         // TYPE
)
;


//ALTERNATIVE WAY TO WRITE CONTENT FOR FIXED TITLE FIELDS
//$invoiceNumber = <<<EOD
//<p>Invoice Number:</p>
//EOD;
// Print text using writeHTMLCell()
//$pdf->writeHTMLCell(0, 0, 10, 102, $invoiceNumber, 0, 0, 0, true, '', true);

// ---------------------------------------------------------

// Close and output PDF document
// This method has several options, check the source code documentation for more information.
$pdf->Output('Print.pdf', 'I');

//============================================================+
// END OF FILE
//============================================================+

Open in new window


Look forward to any help you can give.

Thanks J
DOC130417-13042017130359.pdf
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Which part do you need help with?  Can you get the information from your database?  If so, it's just a matter of creating the PDF, adding a page, plugging in the "framework" then using setXY() and Write() methods to fill in the data.  It looks to me like you've done all the heavy lifting already!

Author

Commented:
It's funny when you write these questions how much you think you've asked and yet omitted at the same time.

The select query needs to look something like this: -

select salesRecordNumber, catID, itemTitle, quantity, userID, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerPCD, salePrice, totalPrice from master;

I'm struggling with visualising how the output of the statement fits into the PDF - I'll also need to do a group by function at some point on the salesRecordNumber (a.k.a invoice number) for those who've bought more than one item.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
There's a lot to chew on here.  First thing I would do is encapsulate the layout in a function.  You can call the function and get back a static page with blanks that you can fill in from the query results set.  This is all going to be untested code, so feel free to hack away at it!
function myPageFramework(TCPDF $pdf) {
    // Add a page
    // This method has several options, check the source code documentation for more information.
    $pdf->AddPage();
    
    // BOXES FOR POSTAL MARK & DELIVERY ADDRESS
    $pdf->Rect(  10,  15,  90, 15);
    $pdf->Rect(  10,  15,  90, 50);
    
    // BOXES FOR POSTAL MARK & RETURNS LABEL
    $pdf->Rect(  110,  15,  90, 15);
    $pdf->Rect(  110,  15,  90, 50);
    
    // BOXES FOR INVOICE No. & DATE
    $pdf->Rect(  10,  100,  30, 8);
    $pdf->Rect(  40,  100,  50, 8);
    $pdf->Rect(  10,  108,  30, 8);
    $pdf->Rect(  40,  108,  50, 8);
    
    // BOXES FOR QTY & CAT ID
    $pdf->Rect(  10,  125,  190, 10);
    $pdf->Rect(  12,  127,  8, 6);
    $pdf->Rect(  21,  127,  177, 6);
    
    // LINE FOR MIDPOINT SEPERATOR
    $pdf->Rect(  10,  150,  190, 0);
    
    // BOXES FOR QTY & CAT ID & DESCRIPTION
    $pdf->Rect(  10,  152,  20, 10);
    $pdf->Rect(  30,  152,  130, 10);
    $pdf->Rect(  160, 152,  40, 10);
    $pdf->Rect(  10,  162,  20, 45);
    $pdf->Rect(  30,  162,  130, 45);
    $pdf->Rect(  160, 162,  40, 45);
    
    // BOXES FOR VAT & TOTAL AMOUNT
    $pdf->Rect(  120,  245,  40, 25);
    $pdf->Rect(  160,  245,  40, 25);
    $pdf->Rect(  120,  257.5,  80, 0);
    
    // CONTENT FOR FIXED TITLE FIELDS
    $pdf->SetFont('helvetica','',10);
    $pdf->setXY(111, 33);
    $pdf->Write(0, 'RETURNS ADDRESS:');
    
    $pdf->setXY(111, 39);
    $pdf->Write(0, 'MyCompany.com');
    
    $pdf->setXY(111, 43);
    $pdf->Write(0, 'Unit 9 An Industrial Estate');
    
    $pdf->setXY(111, 47);
    $pdf->Write(0, 'The Street');
    
    $pdf->setXY(111, 51);
    $pdf->Write(0, 'Stoke on Trent');
    
    $pdf->setXY(111, 55);
    $pdf->Write(0, 'Staffordshire');
    
    $pdf->setXY(111, 59);
    $pdf->Write(0, 'ST31 1PJ');
    
    $pdf->setXY(10, 102);
    $pdf->Write(0, 'Invoice Number:');
    
    $pdf->setXY(10, 110);
    $pdf->Write(0, 'Invoice Date:');
    
    $pdf->setXY(124, 102);
    $pdf->Write(0, 'email: customer.services@example.com');
    
    $pdf->setXY(169, 110);
    $pdf->Write(0, 'tel: 01872 123456');
    
    $pdf->setXY(11, 155);
    $pdf->Write(0, 'Quantity:');
    
    $pdf->setXY(31, 155);
    $pdf->Write(0, 'Product Description:');
    
    $pdf->setXY(175, 155);
    $pdf->Write(0, 'Unit Price (£)');
    
    $pdf->setXY(121, 249);
    $pdf->Write(0, 'VAT Amount (£)');
    
    $pdf->setXY(121, 261);
    $pdf->Write(0, 'Total Price (£)');
    
    // IMAGE SETTINGS FOR ROYAL MAIL INDICIA 24HR
    $pdf->Image
    ( $img = 'images/rm48.jpeg'         // PATH/TO/IMAGE_FILE
    , 11             // $x
    , 16             // $y
    , 88             // WIDTH
    , 13             // HEIGHT
    , 'jpeg'         // TYPE
    )
    ;
    
    // IMAGE SETTINGS FOR ROYAL MAIL INDICIA 24HR
    $pdf->Image
    ( $img = 'images/rm48.jpeg'         // PATH/TO/IMAGE_FILE
    , 111            // $x
    , 16             // $y
    , 88             // WIDTH
    , 13             // HEIGHT
    , 'jpeg'         // TYPE
    )
    ;
    
    // IMAGE SETTINGS FOR TCH Logo
    $pdf->Image
    ( $img = 'images/tch_logo.jpeg'         // PATH/TO/IMAGE_FILE
    , 109            // $x
    , 70             // $y
    , 92             // WIDTH
    , 0              // HEIGHT
    , 'jpeg'         // TYPE
    )
    ;
    
    return $pdf;
}

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019

Commented:
Are you asking how to write the data onto that template or create that template?

Author

Commented:
Ok Ray I'm with you so far ...
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks Ray - I'll take a look at that now.

Author

Commented:
Hi Julian,

I've written the template I'm looking at the best way to write the results of the select query into the PDF page by page. J

Author

Commented:
It's just spat out 774 pages so something is happening - I'll plug in the balance of setXY() & Write() and comeback shortly.
J

Author

Commented:
Works a treat got it all plumbed in now.

<?php
// Database Connection File
require_once('latch.php');

// Include the main TCPDF library (search for installation path).
require_once('tcpdf_include.php');

// EXTEND THE TCPDF OBJECT SO WE CAN SUBSTITUTE OUR OWN METHODS
class PDF extends TCPDF
	
{
    // NULLIFY AUTOMATIC HEADER AND FOOTER
    public function Header() {}
    public function Footer() {}
}

// create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Ridgejp');
$pdf->SetTitle('Print Run');
$pdf->SetSubject('Print Run');
$pdf->SetKeywords('');

// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
	require_once(dirname(__FILE__).'/lang/eng.php');
	$pdf->setLanguageArray($l);
}

// ---------------------------------------------------------

	$query = "SELECT salesRecordNumber, dateImport, catID, itemTitle, quantity, userID, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD, salePrice, totalPrice FROM master";

	if (!$result = $mysqli->query($query)) {
		$err
		= "QUERY FAIL: "
		. $query
		. ' ERRNO: '
		. $mysqli->errno
		. ' ERROR: '
		. $mysqli->error
		;
		trigger_error($err, E_USER_ERROR);
		}

	while ($row = $result->fetch_object())
	{
		// ROW BY ROW PROCESSING IS DONE HERE
		// ADD A PAGE TO THE PDF
		// INSERT THE INFORMATION INTO THE PDF WITH SOMETHING LIKE THIS
		$pdf = myPageFramework($pdf);

		$pdf->setXY(10,33);
		$pdf->Write(0, $row->buyerFullName);
		$pdf->setXY(10,39);
		$pdf->Write(0, $row->buyerAddress1);
		$pdf->setXY(10,43);
		$pdf->Write(0, $row->buyerAddress2);
		$pdf->setXY(10,47);
		$pdf->Write(0, $row->buyerAddress3);
		$pdf->setXY(10,51);
		$pdf->Write(0, $row->buyerTown);
		$pdf->setXY(10,55);
		$pdf->Write(0, $row->buyerCounty);
		$pdf->setXY(10,59);
		$pdf->Write(0, $row->buyerPCD);
		$pdf->setXY(41,102);
		$pdf->Write(0, $row->salesRecordNumber);
		$pdf->setXY(41,110);
		$pdf->Write(0, $row->dateImport);
		$pdf->setXY(12,128);
		$pdf->Write(0, $row->quantity);
		$pdf->setXY(22,128);
		$pdf->Write(0, $row->catID);
		$pdf->setXY(12,163);
		$pdf->Write(0, $row->quantity);
		$pdf->setXY(32,163);
		$pdf->Write(0, $row->catID);
		$pdf->setXY(175,163);
		$pdf->Write(0, $row->salePrice);
		$pdf->setXY(175,261);
		$pdf->Write(0, $row->totalPrice);
		
		// ETC ETC TO FILL OUT THE FORM
	}


// ---------------------------------------------------------

// set default font subsetting mode
$pdf->setFontSubsetting(true);

// Set font
// dejavusans is a UTF-8 Unicode font, if you only need to
// print standard ASCII chars, you can use core fonts like
// helvetica or times to reduce file size.
$pdf->SetFont('helvetica', '', 10, '', true);

function myPageFramework(TCPDF $pdf) {

// Add a page
// This method has several options, check the source code documentation for more information.
$pdf->AddPage();

// BOXES FOR POSTAL MARK & DELIVERY ADDRESS
$pdf->Rect(  10,  15,  90, 15);
$pdf->Rect(  10,  30,  90, 35);

// BOXES FOR POSTAL MARK & RETURNS LABEL
$pdf->Rect(  110,  15,  90, 15);
$pdf->Rect(  110,  15,  90, 50);

// BOXES FOR INVOICE No. & DATE
$pdf->Rect(  10,  100,  30, 8);
$pdf->Rect(  40,  100,  50, 8);
$pdf->Rect(  10,  108,  30, 8);
$pdf->Rect(  40,  108,  50, 8);

// BOXES FOR QTY & CAT ID
$pdf->Rect(  10,  125,  190, 10);
$pdf->Rect(  12,  127,  8, 6);
$pdf->Rect(  21,  127,  177, 6);

// LINE FOR MIDPOINT SEPERATOR
$pdf->Rect(  10,  150,  190, 0);

// BOXES FOR QTY & CAT ID & DESCRIPTION
$pdf->Rect(  10,  152,  20, 10);
$pdf->Rect(  30,  152,  130, 10);
$pdf->Rect(  160, 152,  40, 10);
$pdf->Rect(  10,  162,  20, 45);
$pdf->Rect(  30,  162,  130, 45);
$pdf->Rect(  160, 162,  40, 45);

// BOXES FOR VAT & TOTAL AMOUNT
$pdf->Rect(  120,  245,  40, 25);
$pdf->Rect(  160,  245,  40, 25);
$pdf->Rect(  120,  257.5,  80, 0);

// CONTENT FOR FIXED TITLE FIELDS
$pdf->SetFont('helvetica','',10);
$pdf->setXY(111, 33);
$pdf->Write(0, 'RETURNS ADDRESS:');

$pdf->setXY(111, 39);
$pdf->Write(0, 'MyCompany.com');

$pdf->setXY(111, 43);
$pdf->Write(0, 'Unit 5-8 Some Industrial Estate');

$pdf->setXY(111, 47);
$pdf->Write(0, 'The Street');

$pdf->setXY(111, 51);
$pdf->Write(0, 'The City');

$pdf->setXY(111, 55);
$pdf->Write(0, 'The County');

$pdf->setXY(111, 59);
$pdf->Write(0, 'AB1 2CD');

$pdf->setXY(10, 102);
$pdf->Write(0, 'Invoice Number:');

$pdf->setXY(10, 110);
$pdf->Write(0, 'Invoice Date:');

$pdf->setXY(124, 102);
$pdf->Write(0, 'email: customer.services@mycompany.com');

$pdf->setXY(169, 110);
$pdf->Write(0, 'tel: 01234 567890');

$pdf->setXY(11, 155);
$pdf->Write(0, 'Quantity:');

$pdf->setXY(31, 155);
$pdf->Write(0, 'Product Description:');

$pdf->setXY(175, 155);
$pdf->Write(0, 'Unit Price (£)');

$pdf->setXY(121, 249);
$pdf->Write(0, 'VAT Amount (£)');

$pdf->setXY(121, 261);
$pdf->Write(0, 'Total Price (£)');

// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 48HR
$pdf->Image
( $img = 'images/rm48.jpeg'         // PATH/TO/IMAGE_FILE
, 11             // $x
, 16             // $y
, 88             // WIDTH
, 13             // HEIGHT
, 'jpeg'         // TYPE
)
;

// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 48HR
$pdf->Image
( $img = 'images/rm48.jpeg'         // PATH/TO/IMAGE_FILE
, 111            // $x
, 16             // $y
, 88             // WIDTH
, 13             // HEIGHT
, 'jpeg'         // TYPE
)
;

// IMAGE SETTINGS FOR TCH Logo
$pdf->Image
( $img = 'images/tch_logo.jpeg'         // PATH/TO/IMAGE_FILE
, 109            // $x
, 70             // $y
, 92             // WIDTH
, 0              // HEIGHT
, 'jpeg'         // TYPE
)
;

	return $pdf;
}

// ---------------------------------------------------------

// Close and output PDF document
// This method has several options, check the source code documentation for more information.
$pdf->Output('Print.pdf', 'I');

//============================================================+
// END OF FILE
//============================================================+

Open in new window


But at present if a customer purchased one item it produces one page, however, if they purchased 2 or more items it produces 2 or more pages whereas I only ever want there to be one pdf per customer.

In MsAccess I use a group by function on the salesRecordNumber to pull the products into each - any thoughts?
J
Most Valuable Expert 2011
Author of the Year 2014

Commented:
MySQL has a GROUP BY clause, too.  You can try it with ORDER BY, as well.  If you want to cut down on the bulky output while you're testing, you might add a WHERE clause identifying one of the customers that has multiple purchases, and a LIMIT clause.
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions-and-modifiers.html

Author

Commented:
I'll give it some thought/research and post another question if I get stuck. In the meantime thanks for sorting the original question ... I've be struggling to get my head around OOP and the use of the function really helped clarify a few matters for me.