Avatar of Ridgejp
Ridgejp
Flag 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
PHPMySQL Server

Avatar of undefined
Last Comment
Ridgejp

8/22/2022 - Mon
Ray Paseur

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!
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.
Ray Paseur

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Julian Hansen

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

ASKER
Ok Ray I'm with you so far ...
ASKER CERTIFIED SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ridgejp

ASKER
Thanks Ray - I'll take a look at that now.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
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
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ray Paseur

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