This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.
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.
The output of the php produces a pdf for a single order (salesRecordNumber = 11408) that has 12 pages in it because the customer purchased 12 individual products. When I run the query currently using my old MsAccess system I'm able to have a sub-group on the form based on the 'salesRecordNumber' that outputs one address on the PDF and within the single page 12 lines containing the product data, which I'm trying to emulate using php.
I've played around with group by functions in MySql and can't seem to return what I'm looking forward in order to condense the results into one page. So I then reasoned whether I needed to have a sub-function within the php based on a select query that returned just the product details.
I came up with the following but not sure if my logic is flawed of just incorrect from a syntax perspective, show below:
Even as I post I can see possible errors in the output but I was hoping to get a steer as to whether I was on the right tracks or not ... thanks in advance.
J
I have a specific problem that I'm struggling with and need some assistance ... ok here we go.
I'm outputting my MySql query into a PDF as follows using the following php.
<?php
// Database Connection File
require_once('latch.php');
// Include the main TCPDF library (search for installation path).
require_once('tcpdf_include.php');
// EXTEND THE TCPDF OBJECT SO WE CAN SUBSTITUTE OUR OWN METHODS
class PDF extends TCPDF
{
// NULLIFY AUTOMATIC HEADER AND FOOTER
public function Header() {}
public function Footer() {}
}
// create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Ridgejp');
$pdf->SetTitle('Print Run');
$pdf->SetSubject('Print Run');
$pdf->SetKeywords('');
// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
// ---------------------------------------------------------
$query = "SELECT salesRecordNumber, dateImport, catID, itemTitle, quantity, userID, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD, salePrice, totalPrice FROM master where salesRecordNumber = '11408' group by salesRecordNumber, dateImport, catID, itemTitle, quantity, userID, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD, salePrice, totalPrice";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
while ($row = $result->fetch_object())
{
// ROW BY ROW PROCESSING IS DONE HERE
// ADD A PAGE TO THE PDF
// INSERT THE INFORMATION INTO THE PDF WITH SOMETHING LIKE THIS
$pdf = myPageFramework($pdf);
$pdf->setXY(10,33);
$pdf->Write(0, $row->buyerFullName);
$pdf->setXY(10,39);
$pdf->Write(0, $row->buyerAddress1);
$pdf->setXY(10,43);
$pdf->Write(0, $row->buyerAddress2);
$pdf->setXY(10,47);
$pdf->Write(0, $row->buyerAddress3);
$pdf->setXY(10,51);
$pdf->Write(0, $row->buyerTown);
$pdf->setXY(10,55);
$pdf->Write(0, $row->buyerCounty);
$pdf->setXY(10,59);
$pdf->Write(0, $row->buyerPCD);
$pdf->setXY(41,102);
$pdf->Write(0, $row->salesRecordNumber);
$pdf->setXY(41,110);
$pdf->Write(0, $row->dateImport);
$pdf->setXY(12,128);
$pdf->Write(0, $row->quantity);
$pdf->setXY(22,128);
$pdf->Write(0, $row->catID);
$pdf->setXY(12,163);
$pdf->Write(0, $row->quantity);
$pdf->setXY(32,163);
$pdf->Write(0, $row->catID);
$pdf->setXY(175,163);
$pdf->Write(0, $row->salePrice);
$pdf->setXY(175,261);
$pdf->Write(0, $row->totalPrice);
// ETC ETC TO FILL OUT THE FORM
}
// ---------------------------------------------------------
// set default font subsetting mode
$pdf->setFontSubsetting(true);
// Set font
// dejavusans is a UTF-8 Unicode font, if you only need to
// print standard ASCII chars, you can use core fonts like
// helvetica or times to reduce file size.
$pdf->SetFont('helvetica', '', 10, '', true);
function myPageFramework(TCPDF $pdf) {
// Add a page
// This method has several options, check the source code documentation for more information.
$pdf->AddPage();
// BOXES FOR POSTAL MARK & DELIVERY ADDRESS
$pdf->Rect( 10, 15, 90, 15);
$pdf->Rect( 10, 30, 90, 35);
// BOXES FOR POSTAL MARK & RETURNS LABEL
$pdf->Rect( 110, 15, 90, 15);
$pdf->Rect( 110, 15, 90, 50);
// BOXES FOR INVOICE No. & DATE
$pdf->Rect( 10, 100, 30, 8);
$pdf->Rect( 40, 100, 50, 8);
$pdf->Rect( 10, 108, 30, 8);
$pdf->Rect( 40, 108, 50, 8);
// BOXES FOR QTY & CAT ID
$pdf->Rect( 10, 125, 190, 10);
$pdf->Rect( 12, 127, 8, 6);
$pdf->Rect( 21, 127, 177, 6);
// LINE FOR MIDPOINT SEPERATOR
$pdf->Rect( 10, 150, 190, 0);
// BOXES FOR QTY & CAT ID & DESCRIPTION
$pdf->Rect( 10, 152, 20, 10);
$pdf->Rect( 30, 152, 130, 10);
$pdf->Rect( 160, 152, 40, 10);
$pdf->Rect( 10, 162, 20, 45);
$pdf->Rect( 30, 162, 130, 45);
$pdf->Rect( 160, 162, 40, 45);
// BOXES FOR VAT & TOTAL AMOUNT
$pdf->Rect( 120, 245, 40, 25);
$pdf->Rect( 160, 245, 40, 25);
$pdf->Rect( 120, 257.5, 80, 0);
// CONTENT FOR FIXED TITLE FIELDS
$pdf->SetFont('helvetica','',10);
$pdf->setXY(111, 33);
$pdf->Write(0, 'RETURNS ADDRESS:');
$pdf->setXY(111, 39);
$pdf->Write(0, 'MyCompany.com');
$pdf->setXY(111, 43);
$pdf->Write(0, 'Unit 5-8 Some Industrial Estate');
$pdf->setXY(111, 47);
$pdf->Write(0, 'The Street');
$pdf->setXY(111, 51);
$pdf->Write(0, 'The City');
$pdf->setXY(111, 55);
$pdf->Write(0, 'The County');
$pdf->setXY(111, 59);
$pdf->Write(0, 'AB1 2CD');
$pdf->setXY(10, 102);
$pdf->Write(0, 'Invoice Number:');
$pdf->setXY(10, 110);
$pdf->Write(0, 'Invoice Date:');
$pdf->setXY(124, 102);
$pdf->Write(0, 'email: customer.services@mycompany.com');
$pdf->setXY(169, 110);
$pdf->Write(0, 'tel: 01234 567890');
$pdf->setXY(11, 155);
$pdf->Write(0, 'Quantity:');
$pdf->setXY(31, 155);
$pdf->Write(0, 'Product Description:');
$pdf->setXY(175, 155);
$pdf->Write(0, 'Unit Price (£)');
$pdf->setXY(121, 249);
$pdf->Write(0, 'VAT Amount (£)');
$pdf->setXY(121, 261);
$pdf->Write(0, 'Total Price (£)');
// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 48HR
$pdf->Image
( $img = 'images/rm48.jpeg' // PATH/TO/IMAGE_FILE
, 11 // $x
, 16 // $y
, 88 // WIDTH
, 13 // HEIGHT
, 'jpeg' // TYPE
)
;
// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 48HR
$pdf->Image
( $img = 'images/rm48.jpeg' // PATH/TO/IMAGE_FILE
, 111 // $x
, 16 // $y
, 88 // WIDTH
, 13 // HEIGHT
, 'jpeg' // TYPE
)
;
// IMAGE SETTINGS FOR TCH Logo
$pdf->Image
( $img = 'images/tch_logo.jpeg' // PATH/TO/IMAGE_FILE
, 109 // $x
, 70 // $y
, 92 // WIDTH
, 0 // HEIGHT
, 'jpeg' // TYPE
)
;
return $pdf;
}
// ---------------------------------------------------------
// Close and output PDF document
// This method has several options, check the source code documentation for more information.
$pdf->Output('Print.pdf', 'I');
//============================================================+
// END OF FILE
//============================================================+
The output of the php produces a pdf for a single order (salesRecordNumber = 11408) that has 12 pages in it because the customer purchased 12 individual products. When I run the query currently using my old MsAccess system I'm able to have a sub-group on the form based on the 'salesRecordNumber' that outputs one address on the PDF and within the single page 12 lines containing the product data, which I'm trying to emulate using php.
I've played around with group by functions in MySql and can't seem to return what I'm looking forward in order to condense the results into one page. So I then reasoned whether I needed to have a sub-function within the php based on a select query that returned just the product details.
I came up with the following but not sure if my logic is flawed of just incorrect from a syntax perspective, show below:
<?php
// Database Connection File
require_once('latch.php');
// Include the main TCPDF library (search for installation path).
require_once('tcpdf_include.php');
// EXTEND THE TCPDF OBJECT SO WE CAN SUBSTITUTE OUR OWN METHODS
class PDF extends TCPDF
{
// NULLIFY AUTOMATIC HEADER AND FOOTER
public function Header() {}
public function Footer() {}
}
// create new PDF document
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Ridgejp');
$pdf->SetTitle('Print Run');
$pdf->SetSubject('Print Run');
$pdf->SetKeywords('');
// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
require_once(dirname(__FILE__).'/lang/eng.php');
$pdf->setLanguageArray($l);
}
// ---------------------------------------------------------
$query = "SELECT salesRecordNumber, dateImport, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD FROM master where salesRecordNumber ='11408' group by salesRecordNumber, dateImport, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
while ($row = $result->fetch_object())
{
// ROW BY ROW PROCESSING IS DONE HERE
// ADD A PAGE TO THE PDF
// INSERT THE INFORMATION INTO THE PDF WITH SOMETHING LIKE THIS
$pdf = myPageFramework($pdf);
$pdf->setXY(10,33);
$pdf->Write(0, $row->buyerFullName);
$pdf->setXY(10,39);
$pdf->Write(0, $row->buyerAddress1);
$pdf->setXY(10,43);
$pdf->Write(0, $row->buyerAddress2);
$pdf->setXY(10,47);
$pdf->Write(0, $row->buyerAddress3);
$pdf->setXY(10,51);
$pdf->Write(0, $row->buyerTown);
$pdf->setXY(10,55);
$pdf->Write(0, $row->buyerCounty);
$pdf->setXY(10,59);
$pdf->Write(0, $row->buyerPCD);
$pdf->setXY(41,102);
$pdf->Write(0, $row->salesRecordNumber);
$pdf->setXY(41,110);
$pdf->Write(0, $row->dateImport);
// ETC ETC TO FILL OUT THE FORM
}
$query = "SELECT catID, itemTitle, quantity, salePrice FROM master where salesRecordNumber ='11408' group by salesRecordNumber, catID, itemTitle, quantity, salePrice";
if (!$result = $mysqli->query($query)) {
$err
= "QUERY FAIL: "
. $query
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
;
trigger_error($err, E_USER_ERROR);
}
while ($row = $result->fetch_object())
{
// ROW BY ROW PROCESSING IS DONE HERE
// ADD A PAGE TO THE PDF
// INSERT THE INFORMATION INTO THE PDF WITH SOMETHING LIKE THIS
$pdf1 = myPageFramework1($pdf1);
$pdf1->setXY(12,128);
$pdf1->Write(0, $row->quantity);
$pdf1->setXY(22,128);
$pdf1->Write(0, $row->catID);
$pdf1->setXY(12,163);
$pdf1->Write(0, $row->quantity);
$pdf1->setXY(32,163);
$pdf1->Write(0, $row->catID);
$pdf1->setXY(175,163);
$pdf1->Write(0, $row->salePrice);
// ETC ETC TO FILL OUT THE FORM
}
// ---------------------------------------------------------
// set default font subsetting mode
$pdf->setFontSubsetting(true);
// Set font
// dejavusans is a UTF-8 Unicode font, if you only need to
// print standard ASCII chars, you can use core fonts like
// helvetica or times to reduce file size.
$pdf->SetFont('helvetica', '', 10, '', true);
function myPageFramework(TCPDF $pdf) {
// Add a page
// This method has several options, check the source code documentation for more information.
$pdf->AddPage();
// BOXES FOR POSTAL MARK & DELIVERY ADDRESS
$pdf->Rect( 10, 15, 90, 15);
$pdf->Rect( 10, 30, 90, 35);
// BOXES FOR POSTAL MARK & RETURNS LABEL
$pdf->Rect( 110, 15, 90, 15);
$pdf->Rect( 110, 15, 90, 50);
// BOXES FOR INVOICE No. & DATE
$pdf->Rect( 10, 100, 30, 8);
$pdf->Rect( 40, 100, 50, 8);
$pdf->Rect( 10, 108, 30, 8);
$pdf->Rect( 40, 108, 50, 8);
{ function myPageFramework1(TCPDF $pdf1) {
// BOXES FOR QTY & CAT ID
$pdf1->Rect( 10, 125, 190, 10);
$pdf1->Rect( 12, 127, 8, 6);
$pdf1->Rect( 21, 127, 177, 6);
// LINE FOR MIDPOINT SEPERATOR
$pdf1->Rect( 10, 150, 190, 0);
// BOXES FOR QTY & CAT ID & DESCRIPTION
$pdf1->Rect( 10, 152, 20, 10);
$pdf1->Rect( 30, 152, 130, 10);
$pdf1->Rect( 160, 152, 40, 10);
$pdf1->Rect( 10, 162, 20, 45);
$pdf1->Rect( 30, 162, 130, 45);
$pdf1->Rect( 160, 162, 40, 45);
}}
// BOXES FOR VAT & TOTAL AMOUNT
$pdf->Rect( 120, 245, 40, 25);
$pdf->Rect( 160, 245, 40, 25);
$pdf->Rect( 120, 257.5, 80, 0);
// CONTENT FOR FIXED TITLE FIELDS
$pdf->SetFont('helvetica','',10);
$pdf->setXY(111, 33);
$pdf->Write(0, 'RETURNS ADDRESS:');
$pdf->setXY(111, 39);
$pdf->Write(0, 'MyCompany.com');
$pdf->setXY(111, 43);
$pdf->Write(0, 'Unit 5-8 Some Industrial Estate');
$pdf->setXY(111, 47);
$pdf->Write(0, 'The Street');
$pdf->setXY(111, 51);
$pdf->Write(0, 'The City');
$pdf->setXY(111, 55);
$pdf->Write(0, 'The County');
$pdf->setXY(111, 59);
$pdf->Write(0, 'AB1 2CD');
$pdf->setXY(10, 102);
$pdf->Write(0, 'Invoice Number:');
$pdf->setXY(10, 110);
$pdf->Write(0, 'Invoice Date:');
$pdf->setXY(124, 102);
$pdf->Write(0, 'email: customer.services@mycompany.com');
$pdf->setXY(169, 110);
$pdf->Write(0, 'tel: 01234 567890');
$pdf->setXY(11, 155);
$pdf->Write(0, 'Quantity:');
$pdf->setXY(31, 155);
$pdf->Write(0, 'Product Description:');
$pdf->setXY(175, 155);
$pdf->Write(0, 'Unit Price (£)');
$pdf->setXY(121, 249);
$pdf->Write(0, 'VAT Amount (£)');
$pdf->setXY(121, 261);
$pdf->Write(0, 'Total Price (£)');
// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 48HR
$pdf->Image
( $img = 'images/rm48.jpeg' // PATH/TO/IMAGE_FILE
, 11 // $x
, 16 // $y
, 88 // WIDTH
, 13 // HEIGHT
, 'jpeg' // TYPE
)
;
// IMAGE SETTINGS FOR ROYAL MAIL INDICIA 48HR
$pdf->Image
( $img = 'images/rm48.jpeg' // PATH/TO/IMAGE_FILE
, 111 // $x
, 16 // $y
, 88 // WIDTH
, 13 // HEIGHT
, 'jpeg' // TYPE
)
;
// IMAGE SETTINGS FOR TCH Logo
$pdf->Image
( $img = 'images/tch_logo.jpeg' // PATH/TO/IMAGE_FILE
, 109 // $x
, 70 // $y
, 92 // WIDTH
, 0 // HEIGHT
, 'jpeg' // TYPE
)
;
return $pdf;
}
// ---------------------------------------------------------
// Close and output PDF document
// This method has several options, check the source code documentation for more information.
$pdf->Output('Print.pdf', 'I');
//============================================================+
// END OF FILE
//============================================================+
Even as I post I can see possible errors in the output but I was hoping to get a steer as to whether I was on the right tracks or not ... thanks in advance.
J
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
"
;
$query
= "
SELECT
salesRecordNumber
, dateImport
, buyerFullName
, buyerAddress1
, buyerAddress2
, buyerAddress3
, buyerTown
, buyerCounty
, buyerPCD
FROM master
WHERE salesRecordNumber ='11408'
LIMIT 1
"
;
$query
= "
SELECT
catID
, itemTitle
, quantity
, salePrice
FROM master
WHERE salesRecordNumber ='11408'
GROUP BY
salesRecordNumber
, catID
, itemTitle
, quantity
, salePrice
"
;
$query
= "
SELECT
catID
, itemTitle
, quantity
, salePrice
FROM master
WHERE salesRecordNumber ='11408'
GROUP BY catID
ORDER BY catID
"
;
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.
New revised seems to work but with a few issue still - it looks like this: -
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
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
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
";
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
//============================================================+
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);
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
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.
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
J
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.
Thanks ... do you have any examples I can review?
The issue caused by the removal of the WHERE statement - any thoughts?
The issue caused by the removal of the WHERE statement - any thoughts?
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
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
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
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
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?
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!
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!
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.
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
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
//============================================================+
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
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?
Apologies I'd uploaded and unfinished file this is the correct data.
Thanks again.
J
Download---Test-File.csv
Thanks again.
J
Download---Test-File.csv
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.
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();
<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
HI Ray,
Throws the following error for me: -
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_b y QUERY: SELECT * FROM my_table GROUP BY orderid ORDER BY orderid in /var/www/html/temp_ridgejp .php on line 138
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.
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.
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
Export-from-Master---Data.csv
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
//============================================================+
Export-from-Master---Data.csv
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial