Link to home
Start Free TrialLog in
Avatar of Ridgejp
RidgejpFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Populate PDF with Data from MySql Database Using Php

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

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!
Avatar of Ridgejp

ASKER

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.
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

Avatar of Julian Hansen
Are you asking how to write the data onto that template or create that template?
Avatar of Ridgejp

ASKER

Ok Ray I'm with you so far ...
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ridgejp

ASKER

Thanks Ray - I'll take a look at that now.
Avatar of Ridgejp

ASKER

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
Avatar of Ridgejp

ASKER

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

ASKER

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
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
Avatar of Ridgejp

ASKER

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.