PHP to PDF from MySQL Database

Question

I have read up and tried multiple tutorials and each time I run into a block I cannot pass.

I have a database with 59 fields currently, I have a form agents fills in their sales into each day. Each day from 15-50 sales are captured. Currently I have a HTML Template where I have populated with the variables of the database fields and I run it through a loop displaying all those sales each in a separate table, then I print to pdf each sale Table on a separate page. You can imagine how labour intensive this is every day.

I am now trying to convert each loop run which will be a record of a sale into their own pdf and name the pdf file according to a mix of variables from the database table.

I cannot get the php variables to work in the PDF Generator. I can display the Data in the table, and generate the pdf off my template, but as soon as I add the php mysql database field variables I keep getting errors about he variables and the pdf gereator fails.

Here is what I have done:

TCPDF:

<?php

$pdf = new TCPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);

// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Asimegroup');
$pdf->SetTitle('Asime Loan App');
$pdf->SetSubject('Asime Loan Application Form');

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
$pdf->SetMargins('5', '0', '0');


// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, '0');

// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'examples\lang\eng.php')) {
    require_once(dirname(__FILE__).'examples\lang\eng.php');
    $pdf->setLanguageArray($l);
}

// set font
$pdf->SetFont('helvetica', '', 10);

// add a page
$pdf->AddPage();

// Just including 2 rows here for example sake.

$html = '
<body>
<br/>
<table>
  <tr>
    <td height="40px"></td>
  </tr>
  <tr>
    <td align="right" class="heading"  width="175px">Personal</td>
    <td align="left" class="heading" width="210px">Details:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="150px">Relative not</td>
    <td align="left" class="heading" width="170px">living with you:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="185px">Expenses:</td>
    <td width="100px"></td>
  </tr>
  <tr>
    <td class="subheading">SA Citizen?:</td>
    <td class="data"></td>
    <td></td>
    <td class="subheading">Name:</td>
    <td class="data"></td>
    <td></td>
    <td class="subheading">Bond / Rent:</td>
    <td class="data"></td>
  </tr>
  </table>';


  // output the HTML content
  $pdf->writeHTML($html, true, false, true, false, '');

  // reset pointer to the last page
  $pdf->lastPage();

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

  //Close and output PDF document
  $pdf->Output('example_061.pdf', 'F');

?>

PHP Database Loop:

<?php
// server info
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'debtdb';


// connect to the database
$conn = new mysqli($server, $user, $pass, $db);

// show errors (remove this line if on a live site)
mysqli_report(MYSQLI_REPORT_ERROR);

if(!isset($_POST['agentname'])) {
$search_sql="SELECT * FROM daily";
$search_query=mysqli_query($conn, $search_sql);
$search_rs=mysqli_fetch_assoc($search_query);
}

 if(!empty($search_query))   {

  while($search_rs = mysqli_fetch_array($search_query)) {
?>

<body>

// Just including 2 rows here for example sake.

<table>
  <tr>
    <td align="right" class="heading"  width="175px">Personal</td>
    <td align="left" class="heading" width="210px">Details:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="150px">Relative not</td>
    <td align="left" class="heading" width="170px">living with you:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="185px">Expenses:</td>
    <td width="100px"></td>
  </tr>
  <tr>
    <td class="subheading">SA Citizen?:</td>
    <td class="data"><?php echo $search_rs["per_citizen"]; ?></td>
    <td></td>
    <td class="subheading">Name:</td>
    <td class="data"><?php echo $search_rs["rel_name"]; ?></td>
    <td></td>
    <td class="subheading">Bond / Rent:</td>
    <td class="data">R&nbsp;<?php echo $search_rs["exp_bondrent"]; ?></td>
  </tr>
  </table>
  <?php
    }
  }
  ?>

Each of these 2 examples works. The following is where I got stuck when I merge the 2:

<?php
// server info
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'debtdb';


// connect to the database
$conn = new mysqli($server, $user, $pass, $db);

// show errors (remove this line if on a live site)
mysqli_report(MYSQLI_REPORT_ERROR);

if(!isset($_POST['agentname'])) {
$search_sql="SELECT * FROM daily";
$search_query=mysqli_query($conn, $search_sql);
$search_rs=mysqli_fetch_assoc($search_query);
}

 if(!empty($search_query))   {

  while($search_rs = mysqli_fetch_array($search_query)) {


$pdf = new TCPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);

// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Asimegroup');
$pdf->SetTitle('Asime Loan App');
$pdf->SetSubject('Asime Loan Application Form');

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
$pdf->SetMargins('5', '0', '0');


// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, '0');

// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'examples\lang\eng.php')) {
    require_once(dirname(__FILE__).'examples\lang\eng.php');
    $pdf->setLanguageArray($l);
}

// set font
$pdf->SetFont('helvetica', '', 10);

// add a page
$pdf->AddPage();

// Just including 2 rows here for example sake.

$html = '
<table>
  <tr>
    <td align="right" class="heading"  width="175px">Personal</td>
    <td align="left" class="heading" width="210px">Details:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="150px">Relative not</td>
    <td align="left" class="heading" width="170px">living with you:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="185px">Expenses:</td>
    <td width="100px"></td>
  </tr>
  <tr>
    <td class="subheading">SA Citizen?:</td>
    <td class="data"><?php echo $search_rs["per_citizen"]; ?></td>
    <td></td>
    <td class="subheading">Name:</td>
    <td class="data"><?php echo $search_rs["rel_name"]; ?></td>
    <td></td>
    <td class="subheading">Bond / Rent:</td>
    <td class="data">R&nbsp;<?php echo $search_rs["exp_bondrent"]; ?></td>
  </tr>
  </table>';

  // output the HTML content
  $pdf->writeHTML($html, true, false, true, false, '');

  // reset pointer to the last page
  $pdf->lastPage();

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

  //Close and output PDF document
  $pdf->Output('<?php echo $search_rs["agentname"]; ?> - <?php echo $search_rs["dateofsale"]; ?>.pdf', 'F');

    }
  }
  ?>

Gives me :

The localhost page isn’t working

localhost is currently unable to handle this request. HTTP ERROR 500

your trying to create the pdf inside the loop of your mysql data. $pdf = new .. shoube be before the loop, — user6763587
– user6763587, Commented Sep 12, 2016 at 21:11
BTW, your pure html example is wrong too; it may look ok but if you read the source in your browser, you will see you're creating multiple <body> tags, while there should only be 1. — Saskia
– Saskia, Commented Sep 12, 2016 at 21:18

Saskia · Accepted Answer · 2016-09-12 21:13:54Z

0

You have put this line:

$pdf = new TCPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);

in your mysql while loop, which means you are creating a new PDF for each sql result, which is probebly not what you want. The browser will complain cause it cannot handle a request with multiple pdf files. Put stuff that needs to run just once, like creating the pdf variable and author stuff etc, above your while loop...

answered Sep 12, 2016 at 21:13

Saskia

5246 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user6763587 Over a year ago

there's more than this that should be outside the loop

hardbouw Over a year ago

The plan is to create multiple pdfs. I need to create an pdf file of each record in my html template, thats why I thought to ass everything in the loop so that every time the loop executes it will generate an pdf. I dont want the whole loop's output all in one pdf.

Saskia Over a year ago

And what do you expect from the browser when you give him multiple PDF files, have you ever seen a browser show multiple PDF files? No, a browser serves just one file. In that case, safe the PDF files to disk and give the browser a html page containing several links to the PDF files so the user can chose a PDF file by clicking on that link.

Koen. · Accepted Answer · 2016-09-13 05:44:36Z

0

What I often find the easiest for generating PDFs, is simply generating HTML (what you already did) and then feeding that to a tool like wkhtml2pdf, which converts it to PDF.

The only thing you have to add is some CSS to make every table appear on a new page;

table {
  page-break-after: always;
}

Now you can pass the URL to the command;

wkhtml2pdf http://example.com/reports.php reports.pdf

Or save it to a temporary file first, and pass that to the command.

answered Sep 13, 2016 at 5:44

Koen.

27.3k7 gold badges87 silver badges80 bronze badges

7 Comments

hardbouw Over a year ago

Thanks, I see now what that command does, added it, visually I dont see a change but when printing the page with the looped records in tables it splits it into different pages. You saying I must pass the URL tot he pdf printer, the url on my pc is localhost/allclients.php

hardbouw Over a year ago

I did this now in TCPDF: $html = file_get_contents('../../allclients.php'); It just displays the table template without borders. Will try the app you suggest also

hardbouw Over a year ago

Thanks, already busy installing wkhtml2pdf. How will this output seperate PDF Files? I need to automatically output about 50 pdfs a dat named like for example: Application-$agentname-$clientname-$dateofsale.pdf

hardbouw Over a year ago

Thanks, this works, I have ubuntu, installed the wkhtmltopdf, ran /usr/bin/wkhtmltopdf localhost/allclients.php test.pdf and it output all my records on seperate pages in one pdf document. Next step is how do I do them seperately?

Koen. Over a year ago

Outputting them separately is a bit more work. You could invoke wkhtml2pdf from php inside your loop. I can write up an answer using this method if you like.

|

Community · Accepted Answer · 2017-05-23 12:00:56Z

Thanks for the advice and help so far form everyone. I am now able to output a whole table into one pdf using wkhtmltopdf.

I now need modify this code for my second solution to the second step working seperating the pages into seperate files. I found this part on stackexchange now, looks almost right, just need to be able to intergrate it with my mysql database:

PHP TCPDF Create Multiple PDFS With One Command

require_once('eng.php');
require_once('tcpdf.php');
$pageLayout = array(750, 800); 

$content=Array(
 '<html><body>Document A</body></html>',
 '<html><body>Document B</body></html>',
 '<html><body>Document C</body></html>'
);

foreach($content as $i=>$html){
    $pdf =new TCPDF('P', 'pt', $pageLayout, true, 'UTF-8', false);
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->AddPage();
    $pdf->writeHTML($html, true, false, true, false, '');
    $pdf->lastPage();
    $pdf->Output('filename_' . $i . '.pdf', 'D');
}

I need to generate the pdf files each with an Unique name, not display them in the browser.

I need to for example to do something like this:

$pdf->Output('$clientname-$clientsurname-$dateosfale.pdf', 'F');

I can then generate links based on the same pattern and display them in a table to be able to be downloaded.

Correct me if Im wrong, here is the basic structure of my php loop and the tcpdf generating code.

I was thinking of doing it like this. As I generate all at once with wkhtmltopdf I need to be able to do the same with each page, thus I added everything related to the pdf generation inside the loop so that it will make an pdf for each loop., but its not as easy as that I noticed:

<?php
// server info
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'debtdb';


// connect to the database
$conn = new mysqli($server, $user, $pass, $db);

// show errors (remove this line if on a live site)
mysqli_report(MYSQLI_REPORT_ERROR);

if(!isset($_POST['agentname'])) {
$search_sql="SELECT * FROM daily";
$search_query=mysqli_query($conn, $search_sql);
$search_rs=mysqli_fetch_assoc($search_query);
}

 if(!empty($search_query))   {

  while($search_rs = mysqli_fetch_array($search_query)) {

require_once('tcpdf_include.php');

// create new PDF document
$pdf = new TCPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);

// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Asimegroup');
$pdf->SetTitle('Asime Loan App');
$pdf->SetSubject('Asime Loan Application Form');

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
$pdf->SetMargins('5', '0', '0');

// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, '0');

// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
if (@file_exists(dirname(__FILE__).'examples\lang\eng.php')) {
    require_once(dirname(__FILE__).'examples\lang\eng.php');
    $pdf->setLanguageArray($l);
}

// set font
$pdf->SetFont('helvetica', '', 10);

// add a page
$pdf->AddPage();

$html = '
<table>
  <tr>
    <td align="right" class="heading"  width="175px">Personal</td>
    <td align="left" class="heading" width="210px">Details:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="150px">Relative not</td>
    <td align="left" class="heading" width="170px">living with you:</td>
    <td width="10px"></td>
    <td align="right" class="heading" width="185px">Expenses:</td>
    <td width="100px"></td>
  </tr>
  <tr>
    <td class="subheading">SA Citizen?:</td>
    <td class="data"><?php echo $search_rs["per_citizen"]; ?></td>
    <td></td>
    <td class="subheading">Name:</td>
    <td class="data"><?php echo $search_rs["rel_name"]; ?></td>
    <td></td>
    <td class="subheading">Bond / Rent:</td>
    <td class="data">R&nbsp;<?php echo $search_rs["exp_bondrent"]; ?></td>
  </tr>
  <tr>
    <td class="subheading">Name:</td>
    <td class="data"><?php echo $search_rs["per_firstname"]; ?></td>
    <td></td>
    <td class="subheading">Surname:</td>
    <td class="data"><?php echo $search_rs["rel_surn"]; ?></td>
    <td></td>
    <td class="subheading">Rates, Water, Electricity:</td>
    <td class="data">R&nbsp;<?php echo $search_rs["exp_rates"]; ?></td>
  </tr>
</table>';

// output the HTML content
$pdf->writeHTML($html, true, false, true, false, '');

// reset pointer to the last page
$pdf->lastPage();

//Close and output PDF document
$pdf->Output(<?php echo $search_rs["per_firstname"]; ?>-<?php echo $search_rs["per_lastname"]; ?>-<?php echo $search_rs["dateofsale"]; ?>.pdf, 'F');

  }
}
?>

Collectives™ on Stack Overflow

PHP to PDF from MySQL Database

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related