Last updated on January 10th, 2023 at 02:08 pm
This tutorial focus on how we can use PHP to generate MySQL table to PDF. Along with simple PHP code we are also using FPDF library. You need to make sure that you Download Library first.
Make sure to modify the database details according to your settings. Also modify the path of fpdf.php file.
Table of Contents
- Connecting your MySQL database and selecting table
- Formatting the output
- How to add Logo to the PDF
- How to add font family and size to PDF
- Add header / footer with some extra data / string to the PDF
- Here is the complete code
Let us jump on to some of the major sections in the code so that it will be easier for your to do the relevant modification as per your requirement.
This is the view of my MySQL table data
mysql> select * from mydatabase_bank;
+----+--------------+------------+-----------------------+---------------------+
| ID | Name | Date | Transaction Data | Failed Transactions |
+----+--------------+------------+-----------------------+---------------------+
| 1 | John Cellar | 2022-01-01 | Pending_Transaction | No Failure Reported |
| 2 | Mike luis | 2022-01-02 | Pending_Transaction | Failure Reported |
| 3 | Aaron butler | 2022-01-02 | Transaction_Completed | Failure Reported |
+----+--------------+------------+-----------------------+---------------------+
3 rows in set (0.00 sec)
Let us break the code in major chunks
Connecting your MySQL database and selecting table
Once you extract the fpdf library put it in a folder, my folder name is fpdf_data and that is the header of the PHP file
require('fpdf_data/fpdf.php');
Once the above step is done go to line number approximately 77 to configure your database credentials
$objConnect = mysqli_connect("localhost","USERNAME","PASS") or die("Error:Please check your database username & password");
$objDB = mysqli_select_db($objConnect,"customer");
$strSQL = "SELECT Name,Date,`Transaction Data`,`Failed Transactions` FROM mydatabase_bank";
$objQuery = mysqli_query($objConnect,$strSQL);
$resultData = array();
for ($i=0;$i<mysqli_num_rows($objQuery);$i++) {
$result = mysqli_fetch_array($objQuery);
array_push($resultData,$result);
}
$strSQL = “SELECT Name,Date,
In the SELECt statement above we have the column names from which data needs to be displayed in the final PDF.Transaction Data,Failed TransactionsFROM mydatabase_bank”;
Note: This should be the same names that we pass to Cells in the BasicTable function discussed below.
Formatting the output
In line 43 we are describing a function named BasicTable
function BasicTable($header,$data)
{
$this->SetFillColor(255,0,0);
$this->SetDrawColor(128,0,0);
$w=array(30,15,20,10,10,10,10,10,15,15,15,15,15);
//Header
for($i=0;$i<count($header);$i++)
$this->Cell(40,10,$header[$i],1,0,'C',true);
$this->Ln();
//Data
foreach ($data as $eachResult)
{ //width
$this->Cell(40,12,$eachResult["Name"],1);
$this->Cell(40,12,$eachResult["Date"],1);
$this->Cell(40,12,$eachResult["Transaction Data"],1);
$this->Cell(40,12,$eachResult["Failed Transactions"],1);
$this->Ln();
}
}
this->Cell is defined like the following, More details can be found in this public documentation http://fpdf.org/en/doc/cell.htm
this->Cell (Width, Height, Text_To_Display, Border)
How to add Logo to the PDF
In line number 102 we are making a image call like below, in which mylogo.jpg is the logo that you are going to add to the header of the PDF.
$pdf->Image('mylogo.jpg',80,8,33);
More details and examples of adding logo and links can be found in this public documentation http://www.fpdf.org/en/doc/image.htm
How to add font family and size to PDF
$pdf->SetFont('Arial','',6);
The above function can be used to choose any font family and size of your choice. More details can be found on this public documentation http://www.fpdf.org/en/doc/addfont.htm
Add header / footer with some extra data / string to the PDF
Apart from data pulled from MySQL we can also use the below function to add some header footer static data.
function Header()
{
//Logo
$name="Testing PDF Creation";
$this->SetFont('Arial','B',15);
//Move to the right
$this->Cell(80);
//Title
$this->Cell(20,40,"Data Generated For $name on ".date('d-m-Y'),0,0,'C');
$this->SetFont('Arial','B',9);
$this->Cell(10,60,"Test Place 1",0,0,'C');
$this->Cell(-10,70,"Test Place 2",0,0,'C');
//Line break
$this->Ln(20);
}
For the footer
//Page footer
function Footer()
{
}
Here is the complete code
You can always go to this online reference manual http://www.fpdf.org/en/doc/ to take a broader look at all the functions available in fpdf .
<?php
require('fpdf_data/fpdf.php');
//most of the entries here are self explanatory, PHP advance scripting knowledge preferred. If any doubts please let us know through your comments in http://mistonline.in/wp/export-mysql-database-table-to-pdf-using-php-2/
//Modified by mistonline team
class PDF extends FPDF
{
function Header()
{
//Logo
$name="Testing PDF Creation";
$this->SetFont('Arial','B',15);
//Move to the right
$this->Cell(80);
//Title
$this->Cell(20,40,"Data Generated For $name on ".date('d-m-Y'),0,0,'C');
$this->SetFont('Arial','B',9);
$this->Cell(10,60,"Test Place 1",0,0,'C');
$this->Cell(-10,70,"Test Place 2",0,0,'C');
//Line break
$this->Ln(20);
}
//Page footer
function Footer()
{
}
//Simple table
function BasicTable($header,$data)
{
$this->SetFillColor(255,0,0);
$this->SetDrawColor(128,0,0);
$w=array(30,15,20,10,10,10,10,10,15,15,15,15,15);
//Header
for($i=0;$i<count($header);$i++)
$this->Cell(40,10,$header[$i],1,0,'C',true);
$this->Ln();
//Data
foreach ($data as $eachResult)
{ //width
$this->Cell(40,12,$eachResult["Name"],1);
$this->Cell(40,12,$eachResult["Date"],1);
$this->Cell(40,12,$eachResult["Transaction Data"],1);
$this->Cell(40,12,$eachResult["Failed Trasactions"],1);
$this->Ln();
}
}
//Better table
}
$pdf=new PDF();
$header=array('Name','Date','Transaction_Data','Failed_Trasactions');
//Data loading
//*** Load MySQL Data ***//
$objConnect = mysqli_connect("localhost","USERNAME","PASS") or die("Error:Please check your database username & password");
$objDB = mysqli_select_db($objConnect,"customer");
$strSQL = "SELECT Name,Date,`Transaction Data`,`Failed Trasactions` FROM mydatabase_bank";
$objQuery = mysqli_query($objConnect,$strSQL);
$resultData = array();
for ($i=0;$i<mysqli_num_rows($objQuery);$i++) {
$result = mysqli_fetch_array($objQuery);
array_push($resultData,$result);
}
//************************//
$pdf->SetFont('Arial','',6);
//*** Table 1 ***//
$pdf->AddPage();
$pdf->Image('mylogo.jpg',80,8,33);
$pdf->Ln(35);
print_r($header);
echo "<hr>";
print_r($resultData);
$pdf->BasicTable($header,$resultData);
$t=time();
$r="_mistonline.in";
$name=$d."_".$t."_".$r.".pdf";
echo "<br>Data generated succesfully. Download it here <a href=".$name.">DOWNLOAD</a>";
$pdf->Output($name,"F");?>
This demo is for education purpose and it shows how the code works. It also displays array details along with variables that are being passed in to PDF .
Demo
If you are experiencing error related to FPDF error: Alpha channel not supported error message, Check this tutorial.
admin
where do we need to save dbtopdf.php and load
Hi Nandish,
You need to save the file. Modify it according to your requirement and then load it. I think the script is well commented.
Thanks
Admin