Group By Function Required in PDF Output

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.

<?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
//============================================================+

Open in new window

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
//============================================================+

Open in new window

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

RidgejpAsked: 2017-04-18

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 111

Ray PaseurCommented: 2017-04-18

Let's try to simplify this a bit. It's probably not a PDF-related issue, more likely it's about the MySQL queries.

Here's the first query from line 41, reformatted for readability.

$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
"
;

Open in new window

As I understand things, this query really just needs to get the buyer information - name, address, etc., for a single purchase. So I would change the query to something more like this. Adding a LIMIT 1 clause will (1) make the query run faster and (2) return only one row.

$query 
= "
SELECT 
  salesRecordNumber
, dateImport
, buyerFullName
, buyerAddress1
, buyerAddress2
, buyerAddress3
, buyerTown
, buyerCounty
, buyerPCD 
FROM master 
WHERE salesRecordNumber ='11408' 
LIMIT 1
"
;

Open in new window

Now let's turn to the query on line 84. It's the one that gathers the sales details for several transactions. Presently it says this

$query 
= "
SELECT 
  catID
, itemTitle
, quantity
, salePrice 
FROM master 
WHERE salesRecordNumber ='11408' 
GROUP BY 
  salesRecordNumber
, catID
, itemTitle
, quantity
, salePrice
"
;

Open in new window

I think you will get everything you need, and probably in the order you need it, if you change the query to this...

$query 
= "
SELECT 
  catID
, itemTitle
, quantity
, salePrice 
FROM master 
WHERE salesRecordNumber ='11408' 
GROUP BY catID
ORDER BY catID
"
;

Open in new window

Some experimentation may be necessary. You should get back a collection of rows in the results set. In this case, because you do not have a LIMIT clause, your script should iterate over the collection as it builds the PDF.

RidgejpAuthor Commented: 2017-04-19

Hi Ray,

Think we're nearly there .... adjusted some of the select queries in line with your comments but I need the group by function for the second item to remains as below, as the salesRecordNumber is the unique identifier for that collection of records.

$query 	="
			SELECT 
			 catID
			,itemTitle
			,quantity
			,salePrice 
			FROM master 
			WHERE salesRecordNumber ='11408' 
			GROUP BY salesRecordNumber, catID, itemTitle, quantity, salePrice
			ORDER BY catID
			";

Open in new window

New revised seems to work but with a few issue still - it looks like this: -

<?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'
			LIMIT 1
			"
			;

	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);
	}

	$query 	="
			SELECT 
			 catID
			,itemTitle
			,quantity
			,salePrice 
			FROM master 
			WHERE salesRecordNumber ='11408' 
			GROUP BY salesRecordNumber, catID, itemTitle, quantity, salePrice
			ORDER BY catID
			";

	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(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);
	}


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

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

This produces the desired 1 page document albeit the 12 products are all written on top of each other (therefore illegible) presumably due to the setting of the setXY() function - this was achieved by returning to just one function = 'function myPageFramework' for both select queries and commenting out the following

//$pdf = myPageFramework($pdf);

Open in new window

in the second select query.

This leaves as I see if two issues: -

How do I cause the multiple lines to increment down the document according to how many lines there are?
When I remove from the select query the salesRecordNumber it returns the address details but loses the product information?

J

RidgejpAuthor Commented: 2017-04-19

Actually more accurately it produces a single page for each order complete with address header etc and then appears to dump the entire second select query onto the last page.

RidgejpAuthor Commented: 2017-04-19

Sorry I actually meant that this happens when I remove from the SELECT query the WHERE statement related to the salesRecordNumber at this point the above occurs.

J

LVL 111

Ray PaseurCommented: 2017-04-19

How do I cause the multiple lines to increment down the document according to how many lines there are?

You would probably want to use some kind of counter and increment to change the Y value for setXY(). As you increase the Y value, the newly added lines will be placed below the previous lines.

RidgejpAuthor Commented: 2017-04-19

Thanks ... do you have any examples I can review?

The issue caused by the removal of the WHERE statement - any thoughts?

LVL 111

Ray PaseurCommented: 2017-04-19

Sorry, I don't really have any examples that would help. But maybe some pidgin-code would be useful

Start the PDF object
Add all the "framework" and images
Query#1 - Get name and address information -- fetch one row
Add the name and address information
Query#2 - Get the purchase detail information
Put the XY cursor on the first line of the PDF that will get purchase detail information
Use a while() loop to retrieve each of the rows
Copy the row information into the PDF
Increase the Y value to move down the document
end-while
Write the PDF

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

RidgejpAuthor Commented: 2017-04-19

Ok perhaps we can pick up the XY counter incrementation in a separate post later. In the meantime, I'm keen box of the PDF generation (even if the multiple lines show as being overlaid on top of each other for the time being) so that I can move on.

Do you have any thoughts on the reason's why the addresses and product information separate when the WHERE statement linking the salesRecordNumber is removed?

If you've indicated it previously I'll be honest I'm missing it...

Thanks J

LVL 111

Ray PaseurCommented: 2017-04-19

No, I really don't know. It's hard to follow things like this through long dialogs. Can you please post the queries again, so I can see which one is working and which one is not working?

RidgejpAuthor Commented: 2017-04-19

I know it's gets mega confusing eh ...

I'll build a dummy database and provide some pdf outputs so you can see what the different variations provide - be back soon.

PS - Thanks for sticking with it!

RidgejpAuthor Commented: 2017-04-20

Hi Ray,

I've provided a csv test file to show you how the data is loaded into the database - every row has the name/address/product details regardless of whether 1 or 6 items where purchased - as shown in the test file.

I've then enclosed the original php for the output of the PDF's.

<?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
			,totalPrice
			,catID
			,itemTitle
			,quantity
			,salePrice 
			FROM master 
			GROUP BY salesRecordNumber
			,dateImport
			,buyerFullName
			,buyerAddress1
			,buyerAddress2
			,buyerAddress3
			,buyerTown
			,buyerCounty
			,buyerPCD
			,totalPrice
			,catID
			,itemTitle
			,quantity
			,salePrice 
			ORDER BY salesRecordNumber, catID asc
			"
			;

	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(175,261);
		$pdf->Write(0, $row->totalPrice);
		$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->itemTitle);
		$pdf->setXY(175,163);
		$pdf->Write(0, $row->salePrice);
	}


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

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

Finally, I've also uploaded how the output to a PDF is shown with the actual PDF's.

In total the php has output 20 pages to PDF and yet we only have 11 orders which means it's creating a separate PDF for each additional product purchased (where a customer has more than 1 item), I aiming to get to 11 pages one page per customer with all products purchased in the product description box whether 1, 5 or 6 in the data provided.

Hope that helps ...

J
Download---Test-File.csv
DOC200417-20042017120646.pdf

LVL 111

Ray PaseurCommented: 2017-04-20

OK, I'll try to work with the test data you've given me, but I see that there is no instance of catId or itemTitle in the new data set. Are we working with the correct data here?

RidgejpAuthor Commented: 2017-04-20

Apologies I'd uploaded and unfinished file this is the correct data.

Thanks again.

J
Download---Test-File.csv

LVL 111

Ray PaseurCommented: 2017-04-20

Let's start with this much. You should be able to add your own database credentials and run it. It loads the CSV data into a database table and queries the table. It gets the addresses into a collection. Then it runs another query to get all of the details. With this information we can associate the address with all of the detailed transaction records.

Does this appear to be on the right track? I'll wait for your sync signal before I start on the PDF part of things.

<?php // demo/temp_ridgejp.php
/**
 * Demonstrate how to query a database and put information into a TCPDF document
 *
 * TCPDF 6.2.13
 */
error_reporting(E_ALL);

// THE CSV LOOKS LIKE THIS
$csv = <<<CSV
orderid,orderitemid,purchasedate,paymentsdate,buyeremail,buyername,buyerphonenumber,catID,itemTitle,quantitypurchased,currency,itemprice,itemtax,shippingprice,shippingtax,shipservicelevel,recipientname,shipaddress1,shipaddress2,shipaddress3,shipcity,shipstate,shippostalcode,shipcountry
111112,,,,customer1@gmail.com,Mr Smith,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Standard,Mr Smith,No. 1 The Street,,,,London,SE1 3PD,GB
111112,,,,customer1@gmail.com,Mr Smith,,Product_Code_2,This is the description for product code no. 2,1,GBP,4.99,0,0,0,NextDay,Mr Smith,No. 1 The Street,,,,London,SE1 3PD,GB
111112,,,,customer1@gmail.com,Mr Smith,,Product_Code_3,This is the description for product code no. 3,1,GBP,4.99,0,0,0,Standard,Mr Smith,No. 1 The Street,,,,London,SE1 3PD,GB
111112,,,,customer1@gmail.com,Mr Smith,,Product_Code_4,This is the description for product code no. 4,1,GBP,4.99,0,0,0,Standard,Mr Smith,No. 1 The Street,,,,London,SE1 3PD,GB
111112,,,,customer1@gmail.com,Mr Smith,,Product_Code_5,This is the description for product code no. 5,1,GBP,4.99,0,0,0,Standard,Mr Smith,No. 1 The Street,,,,London,SE1 3PD,GB
111112,,,,customer1@gmail.com,Mr Smith,,Product_Code_6,This is the description for product code no. 6,1,GBP,4.99,0,0,0,Standard,Mr Smith,No. 1 The Street,,,,London,SE1 3PD,GB
111113,,,,customer2@gmail.com,Mr Johnson,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Expedited,Mr Johnson,No. 2 The Street,,,,Hants,SO5 6FQ,GB
111114,,,,customer3@gmail.com,Mr Ridge,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Standard,Mr Ridge,No. 3 The Street,,,,Kent,CT1 6DR,GB
111115,,,,customer4@gmail.com,Mr Jones,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Standard,Mr Jones,No. 4 The Street,,,,Leicestershire,LE1 2LD,GB
111116,,,,customer5@gmail.com,Mr Doe,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,NextDay,Mr Doe,No. 5 The Street,,,,Hants,PO1 2SN,GB
111117,,,,customer6@gmail.com,Mr Pickford,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Standard,Mr Pickford,No. 6 The Street,,,,Staffordshire,ST1 3JE,GB
111118,,,,customer7@gmail.com,Mr Millward,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,NextDay,Mr Millward,No. 7 The Street,,,,Derbyshire,DE5 1QL,GB
111119,,,,customer8@gmail.com,Mr Sandland,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Expedited,Mr Sandland,No. 8 The Street,,,,W Midlands,B6 1BZ,GB
111120,,,,customer9@gmail.com,Mr Rose,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Standard,Mr Rose,No. 9 The Street,,,,Essex,CM5 0LS,GB
111121,,,,customer10@gmail.com,Mr Arrowsmith,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,NextDay,Mr Arrowsmith,No. 10 The Street,,,,Glasgow,G6 6LF,GB
111122,,,,customer11@gmail.com,Mr Farmer,,Product_Code_1,This is the description for product code no. 1,1,GBP,4.99,0,0,0,Standard,Mr Farmer,No. 11 The Street,,,,Greater Manchester,M2 8LJ,GB
111122,,,,customer11@gmail.com,Mr Farmer,,Product_Code_2,This is the description for product code no. 2,1,GBP,4.99,0,0,0,Standard,Mr Farmer,No. 11 The Street,,,,Greater Manchester,M2 8LJ,GB
111122,,,,customer11@gmail.com,Mr Farmer,,Product_Code_3,This is the description for product code no. 3,1,GBP,4.99,0,0,0,NextDay,Mr Farmer,No. 11 The Street,,,,Greater Manchester,M2 8LJ,GB
111122,,,,customer11@gmail.com,Mr Farmer,,Product_Code_4,This is the description for product code no. 4,1,GBP,4.99,0,0,0,Standard,Mr Farmer,No. 11 The Street,,,,Greater Manchester,M2 8LJ,GB
111122,,,,customer11@gmail.com,Mr Farmer,,Product_Code_5,This is the description for product code no. 5,1,GBP,4.99,0,0,0,NextDay,Mr Farmer,No. 11 The Street,,,,Greater Manchester,M2 8LJ,GB
CSV;


// LOAD THE DATA
$url = 'https://filedb.experts-exchange.com/incoming/2017/04_w16/1157723/Download---Test-File.csv';
$fpr = fopen($url, 'r');
if (!$fpr) trigger_error("Unable to read $url", E_USER_ERROR);
$top = fgetcsv($fpr);
while(!feof($fpr))
{
    $csv = array_combine($top, fgetcsv($fpr));
    $data[] = $csv;
}


// DATABASE CONNECTION AND SELECTION VARIABLES
$db_host = "localhost";
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, orderid VARCHAR(64) NOT NULL DEFAULT ''
, orderitemid VARCHAR(64) NOT NULL DEFAULT ''
, purchasedate VARCHAR(64) NOT NULL DEFAULT ''
, paymentsdate VARCHAR(64) NOT NULL DEFAULT ''
, buyeremail VARCHAR(64) NOT NULL DEFAULT ''
, buyername VARCHAR(64) NOT NULL DEFAULT ''
, buyerphonenumber VARCHAR(64) NOT NULL DEFAULT ''
, catID VARCHAR(64) NOT NULL DEFAULT ''
, itemTitle VARCHAR(64) NOT NULL DEFAULT ''
, quantitypurchased VARCHAR(64) NOT NULL DEFAULT ''
, currency VARCHAR(64) NOT NULL DEFAULT ''
, itemprice VARCHAR(64) NOT NULL DEFAULT ''
, itemtax VARCHAR(64) NOT NULL DEFAULT ''
, shippingprice VARCHAR(64) NOT NULL DEFAULT ''
, shippingtax VARCHAR(64) NOT NULL DEFAULT ''
, shipservicelevel VARCHAR(64) NOT NULL DEFAULT ''
, recipientname VARCHAR(64) NOT NULL DEFAULT ''
, shipaddress1 VARCHAR(64) NOT NULL DEFAULT ''
, shipaddress2 VARCHAR(64) NOT NULL DEFAULT ''
, shipaddress3 VARCHAR(64) NOT NULL DEFAULT ''
, shipcity VARCHAR(64) NOT NULL DEFAULT ''
, shipstate VARCHAR(64) NOT NULL DEFAULT ''
, shippostalcode VARCHAR(64) NOT NULL DEFAULT ''
, shipcountry VARCHAR(64) NOT NULL DEFAULT ''
)
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// LOADING THE TEST DATA
foreach ($data as $row)
{
    $sql
    = 'INSERT INTO my_table ('
    . implode(',', array_keys($row))
    . ') VALUES ('
    . "'"
    . implode("','", array_values($row))
    . "')"
    ;
    if (!$res = $mysqli->query($sql))
	{
	    $err
	    = 'QUERY FAILURE:'
	    . ' ERRNO: '
	    . $mysqli->errno
	    . ' ERROR: '
	    . $mysqli->error
	    . ' QUERY: '
	    . $sql
	    ;
	    trigger_error($err, E_USER_ERROR);
	}
}


// COLLECTING THE ADDRESS INFORMATION BY orderid
$addrs = [];
$sql = "SELECT * FROM my_table GROUP BY orderid ORDER BY orderid";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    $addrs[$row->orderid] = $row;
}


// COLLECT THE ORDER INFORMATION AND ASSOCIATE THE ADDRESS
$old_addr = '?';
$sql = "SELECT * FROM my_table ORDER BY orderid";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    $addr = $addrs[$row->orderid];
    if ($addr != $old_addr)
    {
        echo PHP_EOL .  '<h3>' . $row->buyername . '</h3>';
        $old_addr = $addr;
    }
    echo PHP_EOL . "<br>ORDER: $row->orderid, BUYER: $row->buyername, VIA: $row->shipservicelevel";
}
die();

Open in new window

Outputs

<h3>Mr Smith</h3>
<br>ORDER: 111112, BUYER: Mr Smith, VIA: Standard
<br>ORDER: 111112, BUYER: Mr Smith, VIA: NextDay
<br>ORDER: 111112, BUYER: Mr Smith, VIA: Standard
<br>ORDER: 111112, BUYER: Mr Smith, VIA: Standard
<br>ORDER: 111112, BUYER: Mr Smith, VIA: Standard
<br>ORDER: 111112, BUYER: Mr Smith, VIA: Standard
<h3>Mr Johnson</h3>
<br>ORDER: 111113, BUYER: Mr Johnson, VIA: Expedited
<h3>Mr Ridge</h3>
<br>ORDER: 111114, BUYER: Mr Ridge, VIA: Standard
<h3>Mr Jones</h3>
<br>ORDER: 111115, BUYER: Mr Jones, VIA: Standard
<h3>Mr Doe</h3>
<br>ORDER: 111116, BUYER: Mr Doe, VIA: NextDay
<h3>Mr Pickford</h3>
<br>ORDER: 111117, BUYER: Mr Pickford, VIA: Standard
<h3>Mr Millward</h3>
<br>ORDER: 111118, BUYER: Mr Millward, VIA: NextDay
<h3>Mr Sandland</h3>
<br>ORDER: 111119, BUYER: Mr Sandland, VIA: Expedited
<h3>Mr Rose</h3>
<br>ORDER: 111120, BUYER: Mr Rose, VIA: Standard
<h3>Mr Arrowsmith</h3>
<br>ORDER: 111121, BUYER: Mr Arrowsmith, VIA: NextDay
<h3>Mr Farmer</h3>
<br>ORDER: 111122, BUYER: Mr Farmer, VIA: Standard
<br>ORDER: 111122, BUYER: Mr Farmer, VIA: Standard
<br>ORDER: 111122, BUYER: Mr Farmer, VIA: NextDay
<br>ORDER: 111122, BUYER: Mr Farmer, VIA: Standard
<br>ORDER: 111122, BUYER: Mr Farmer, VIA: NextDay

Open in new window

RidgejpAuthor Commented: 2017-04-20

I'll take a look and come back as soon as poss...

Thanks J.

RidgejpAuthor Commented: 2017-04-20

HI Ray,

Throws the following error for me: -

Fatal error: QUERY FAILURE: ERRNO: 1055 ERROR: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'inventoryManagement.my_table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by QUERY: SELECT * FROM my_table GROUP BY orderid ORDER BY orderid in /var/www/html/temp_ridgejp.php on line 138

LVL 111

Ray PaseurCommented: 2017-04-20

Please post your CREATE TABLE statement and the SELECT query that triggered the error. We may need to adjust the table creation parameters.

Are you running the most up-to-date version of MySQL? If not, please update it and try the query again!

Also, the code and the data still seem to be out of line. In the code to build the PDF I find references to salesrecordnumber and dateimport, but these are nowhere to be found in the test data. So I'm kind of stuck. Either the test data I have is wrong, or the code base for the PDF is wrong.

RidgejpAuthor Commented: 2017-04-21

Let me explain ... you'll be sorry you asked at this rate! :-)

I have 3 independent files (daily) ebay/amazon/own website all of which export sales data in different formats with different column headings.

I didn't realise you would operate on the 'csv test file' which was a quickly modified amazon file to give you a feel for how that 'data' set looked, hence the odd/missing header rows like salesRecordNumber which in that file is orderID. I was just trying to give you a feel for how the data looked prior to entry into the database, as all 3 files go into there own holding table then I run some php to clean them up before appending them into 'master' which is where the data eventually rests.

So I've started over and exported this time from the 'master' and re-did the PDF's as they also had address errors in them so you should find everything you need in this batch.

1. PHP for PDF
2. Export from Master
3. PDF's Generated from PHP using data from Master

J

Ps. MySql = 5.7.17-0 on ubuntu0.16.04.1

Code for PDF

<?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 
			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,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);

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

Print-Run.pdf
Export-from-Master---Data.csv

LVL 111

Ray PaseurCommented: 2017-04-21

Well, I'm going to sign off on this now -- it's gone from being a question to becoming a multilayered application development project, and I don't do those for free -- as a professional developer, I get paid for those. You might want to consider using E-E gigs to get a professional to help you move forward. Best of luck with the project, ~Ray

RidgejpAuthor Commented: 2017-04-21

Ray thanks for all your help I really appreciate it and the best to you too.
J

RidgejpAuthor Commented: 2017-04-21

Got the missing piece of the puzzle - looks fantastic and thanks again for your help ... basically followed this through.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

PHP

From novice to tech pro — start learning today.

IT Administration

By:

Randy Kohler

Certification
Certification: CVA Certified Vulnerability Assessor

i

Members can enroll in this course at no extra cost.

15 lessons
Routers

By:

Carlo Tannoury

Certification
Certification: CCNP Cisco Certified Network Professional - Implemen…

i

Members can enroll in this course at no extra cost.

549 lessons
Programming

By:

Paul Kent

Programming for You: Introduction to C++

i

Members can enroll in this course at no extra cost.

17 lessons
Business Communication

By:

Martin VanDerSchouw

Certification
Certification: PMI RMP Project Management Institute Risk Management…

i

Members can enroll in this course at no extra cost.

113 lessons
Document Management

By:

Nichole Hoskins Goudreau

Introduction to Legal Concepts

i

Members can enroll in this course at no extra cost.

258 lessons
IT Administration

By:

Patrick von Schlag

Certification
Certification: WBT Web Based Training - ITIL Foundation

i

Members can enroll in this course at no extra cost.

366 lessons
Microsoft Applications

By:

Ken Mayer

Certification
Certification: IC3 GS4 Internet Core Competency Global Standard 4…

i

Members can enroll in this course at no extra cost.

135 lessons
Microsoft Applications

By:

Debbie Fierst

Certification
Certification: MOS Microsoft Office Specialist - Excel 2013 Part …

i

Members can enroll in this course at no extra cost.

176 lessons
Microsoft Applications

By:

Tracy Berry

Certification
Certification: MOS Microsoft Office Specialist - Word 2010 Basic

i

Members can enroll in this course at no extra cost.

192 lessons
Anti-Virus Apps

By:

Thomas Zucker-Scharff

How to Prepare for and Navigate a Ransomware Attack

i

Members can enroll in this course at no extra cost.

36 lessons