asked on
Hi,
I have a specific problem that I'm struggling with and need some assistance ... ok here we go.
I'm outputting my MySql query into a PDF as follows using the following php.
The output of the php produces a pdf for a single order (salesRecordNumber = 11408) that has 12 pages in it because the customer purchased 12 individual products. When I run the query currently using my old MsAccess system I'm able to have a sub-group on the form based on the 'salesRecordNumber' that outputs one address on the PDF and within the single page 12 lines containing the product data, which I'm trying to emulate using php.
I've played around with group by functions in MySql and can't seem to return what I'm looking forward in order to condense the results into one page. So I then reasoned whether I needed to have a sub-function within the php based on a select query that returned just the product details.
I came up with the following but not sure if my logic is flawed of just incorrect from a syntax perspective, show below:
Even as I post I can see possible errors in the output but I was hoping to get a steer as to whether I was on the right tracks or not ... thanks in advance.
J
I have a specific problem that I'm struggling with and need some assistance ... ok here we go.
I'm outputting my MySql query into a PDF as follows using the following php.
<?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 where salesRecordNumber = '11408' group by salesRecordNumber, dateImport, catID, itemTitle, quantity, userID, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD, salePrice, totalPrice";
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
//============================================================+
The output of the php produces a pdf for a single order (salesRecordNumber = 11408) that has 12 pages in it because the customer purchased 12 individual products. When I run the query currently using my old MsAccess system I'm able to have a sub-group on the form based on the 'salesRecordNumber' that outputs one address on the PDF and within the single page 12 lines containing the product data, which I'm trying to emulate using php.
I've played around with group by functions in MySql and can't seem to return what I'm looking forward in order to condense the results into one page. So I then reasoned whether I needed to have a sub-function within the php based on a select query that returned just the product details.
I came up with the following but not sure if my logic is flawed of just incorrect from a syntax perspective, show below:
<?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, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD FROM master where salesRecordNumber ='11408' group by salesRecordNumber, dateImport, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD";
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);
// ETC ETC TO FILL OUT THE FORM
}
$query = "SELECT catID, itemTitle, quantity, salePrice FROM master where salesRecordNumber ='11408' group by salesRecordNumber, catID, itemTitle, quantity, salePrice";
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
$pdf1 = myPageFramework1($pdf1);
$pdf1->setXY(12,128);
$pdf1->Write(0, $row->quantity);
$pdf1->setXY(22,128);
$pdf1->Write(0, $row->catID);
$pdf1->setXY(12,163);
$pdf1->Write(0, $row->quantity);
$pdf1->setXY(32,163);
$pdf1->Write(0, $row->catID);
$pdf1->setXY(175,163);
$pdf1->Write(0, $row->salePrice);
// 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);
{ function myPageFramework1(TCPDF $pdf1) {
// BOXES FOR QTY & CAT ID
$pdf1->Rect( 10, 125, 190, 10);
$pdf1->Rect( 12, 127, 8, 6);
$pdf1->Rect( 21, 127, 177, 6);
// LINE FOR MIDPOINT SEPERATOR
$pdf1->Rect( 10, 150, 190, 0);
// BOXES FOR QTY & CAT ID & DESCRIPTION
$pdf1->Rect( 10, 152, 20, 10);
$pdf1->Rect( 30, 152, 130, 10);
$pdf1->Rect( 160, 152, 40, 10);
$pdf1->Rect( 10, 162, 20, 45);
$pdf1->Rect( 30, 162, 130, 45);
$pdf1->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
//============================================================+
Even as I post I can see possible errors in the output but I was hoping to get a steer as to whether I was on the right tracks or not ... thanks in advance.
J
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
This award recognizes an author who contributes the highest volume of original works or content. Recipients of this award produce extremely valuable content that prioritizes accuracy, relevancy, and professionalism.