Export MySql database table to pdf using php

Last updated on February 4th, 2022 at 03:44 pm

Click to rate this tutorial!
[Total: 73 Average: 3.7]

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.

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,Transaction Data,Failed Transactions FROM mydatabase_bank”;

In the SELECt statement above we have the column names from which data needs to be displayed in the final PDF.
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

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.

Click to rate this tutorial!
[Total: 73 Average: 3.7]

2 replies on “Export MySql database table to pdf using php”

Leave a Reply

Your email address will not be published.