asked on

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

Ray Paseur

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.

Ridgejp

ASKER

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

Ridgejp

ASKER

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.

Ridgejp

ASKER

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

Ray Paseur

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.

Ridgejp

ASKER

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

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

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ridgejp

ASKER

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

Ray Paseur

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?

Ridgejp

ASKER

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!

Ridgejp

ASKER

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

Ray Paseur

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?

Ridgejp

ASKER

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

Thanks again.

J
Download---Test-File.csv

Ray Paseur

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

Ridgejp

ASKER

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

Thanks J.

Ridgejp

ASKER

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

Ray Paseur

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.

Ridgejp

ASKER

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

Ray Paseur

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

Ridgejp

ASKER

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

Ridgejp

ASKER

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