Generate PDF File from MySQL Database Using PHP

This is another PHP pdf export feature tutorial, I will create a simple PHP script to fetch data from MySQL and create a pdf file using PHP. We will use third-party PHP FPDF library.

The FPDF is a very awesome PHP class to generate PDF using PHP from MySQL database. This is open source PHP library to generate pdf file using PHP.

PDF is a very common and popular file format to read, view, and write documents.PDF format is independent of application software, hardware, and operating systems.

FPDF has the following main features

  • Choice of measure unit, page format and margins.
  • Page header and footer management.
  • Automatic page and line break with text justification
  • Image support (JPEG, PNG and GIF).
  • Colors
  • Links
  • TrueType, Type1 and encoding support
  • Page compression

Video Tutorial:

If you are more comfortable in watching a video that explains about using pdf generation, then you should watch this video tutorial.

Generate PDF File Using PDF and PHP

There is one dependency php extension which is Zlib to enable compression and GD for GIF image support. The latest version requires at least PHP 5.1.

export-pdf-mysql-php

Checkout other php export tutorials,

We will follow following steps to Generate PDF

  • Download the FPDF library from fpdf.org
  • We will fetch data from MySQL database into the page.
  • We will use FPDF libs function to generate pdf file with header and footer.

Step 1: We will create employee table into MySQL database.

--
-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;

We will generate some sample data and insert it into the employee table.

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(1, 'Tiger Nixon', 320800, 61),
(2, 'Garrett Winters', 170750, 63),
(3, 'Ashton Cox', 86000, 66),
(4, 'Cedric Kelly', 433060, 22),
(5, 'Airi Satou', 162700, 33),
(6, 'Brielle Williamson', 372000, 61),
(7, 'Herrod Chandler', 137500, 59),
(8, 'Rhona Davidson', 327900, 55),
(9, 'Colleen Hurst', 205500, 39),
(10, 'Sonya Frost', 103600, 23),
(11, 'Jena Gaines', 90560, 30),
(12, 'Quinn Flynn', 342000, 22),
(13, 'Charde Marshall', 470600, 36),
(14, 'Haley Kennedy', 313500, 43),
(15, 'Tatyana Fitzpatrick', 385750, 19),
(16, 'Michael Silva', 198500, 66);

Step 2: Let’s connect MySQL database with PHP. We will create connection.php file and add below code.

Class dbObj{
/* Database connection start */
var $dbhost = "localhost";
var $username = "root";
var $password = "";
var $dbname = "test";
var $conn;
function getConnstring() {
$con = mysqli_connect($this->dbhost, $this->username, $this->password, $this->dbname) or die("Connection failed: " . mysqli_connect_error());

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
$this->conn = $con;
}
return $this->conn;
}
}

The above file is used to connect and select MySQL database using PHP. You need to change $dbhost, $username, $password, and $dbname variable’s value with your database credentials.

Step 3: We will create generate_pdf.php file and add below code.

Image('logo.png',10,-1,70);
    $this->SetFont('Arial','B',13);
    // Move to the right
    $this->Cell(80);
    // Title
    $this->Cell(80,10,'Employee List',1,0,'C');
    // Line break
    $this->Ln(20);
}

// Page footer
function Footer()
{
    // Position at 1.5 cm from bottom
    $this->SetY(-15);
    // Arial italic 8
    $this->SetFont('Arial','I',8);
    // Page number
    $this->Cell(0,10,'Page '.$this->PageNo().'/{nb}',0,0,'C');
}
}

$db = new dbObj();
$connString =  $db->getConnstring();
$display_heading = array('id'=>'ID', 'employee_name'=> 'Name', 'employee_age'=> 'Age','employee_salary'=> 'Salary',);

$result = mysqli_query($connString, "SELECT id, employee_name, employee_age, employee_salary FROM employee") or die("database error:". mysqli_error($connString));
$header = mysqli_query($connString, "SHOW columns FROM employee");

$pdf = new PDF();
//header
$pdf->AddPage();
//foter page
$pdf->AliasNbPages();
$pdf->SetFont('Arial','B',12);
foreach($header as $heading) {
$pdf->Cell(40,12,$display_heading[$heading['Field']],1);
}
foreach($result as $row) {
$pdf->Ln();
foreach($row as $column)
$pdf->Cell(40,12,$column,1);
}
$pdf->Output();
?>

We will include connection and pdf libs file, for customization of the header and footer of pdf files, we will override header and footer methods and define our CSS design.

Step 4: We will create index.php file and add the below code.

<meta charset="UTF-8">
<title>Simple Example of PDF file using PHP and MySQL</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">

<div class="container" style="padding-top:50px">
<h2>Generate PDF file from MySQL Using PHP</h2>

<form class="form-inline" method="post" action="generate_pdf.php">
<button type="submit" id="pdf" name="generate_pdf" class="btn btn-primary"><i class="fa fa-pdf" "="" aria-hidden="true"></i>
Generate PDF</button>
</form>

</div>

We have added an HTML form tag and defined the action value generate_pdf.php file.

Conclusion:

We have generated pdf file using PHP and MySQL database, You can generate pdf using another database as well. You just need to create connections with other types of databases.

You can download the source code and see Demo from the below link.

33 thoughts on “Generate PDF File from MySQL Database Using PHP

  1. I can’t trace the error…….Warning: Invalid argument supplied for foreach() in C:wampwwwPhpbintermgenerate_pdf.php on line 60

  2. hello tnxs a lot worked for me but i want to export image from db
    but image is stored in the folder not in db (only url is stored in the db)
    how can i export the image ?

    • $pdf->Image(‘image_path’,x1,y1,width,height);
      x1 – space from left side
      y1 – top space
      width – image width
      height – image height

      Note – x1, y1, width, height values must be in integer values (value without decimal point)

  3. dbhost, $this->username, $this->password, $this->dbname) or die(“Connection failed: ” . mysqli_connect_error()); /* check connection */ if (mysqli_connect_errno()) { printf(“Connect failed: %s\n”, mysqli_connect_error()); exit(); } else { $this->conn = $con; } return $this->conn; } } ?>
    Fatal error: Uncaught Error: Class ‘dbObj’ not found in C:\xampp\htdocs\generate_pdf.php:34 Stack trace: #0 {main} thrown in C:\xampp\htdocs\generate_pdf.php on line 34

    i got this error

  4. I am getting this below error,could anyone please help me out with solution for this.

    Fatal error: Uncaught Exception: FPDF error: Some data has already been output, can’t send PDF file in D:\XAMPP\htdocs\pdfdemo\libs\fpdf.php:271 Stack trace: #0

  5. Thanks for the code. As I can see $header variables are extra, I changed to this:

    $header = array(‘id’=>’ID’, ’employee_name’=> ‘Name’, ’employee_age’=> ‘Age’,’employee_salary’=> ‘Salary’,);
    $result = mysqli_query($con, “SELECT id, employee_name, employee_age, employee_salary FROM employee”) or die(“database error:”. mysqli_error($con));

    foreach($header as $heading) {
    $pdf->Cell(40,12,$heading,1);
    }

  6. I’m working on a remote course management project and i want to put a image (form/template) under my pdf txt to make it more ergonomic how can you help me??

    • You can add image as like below:

      // Insert a logo in the top-left corner at 300 dpi
      $pdf->Image(‘logo.png’,10,10,-300);
      // Insert a dynamic image from a URL
      $pdf->Image(‘image_url’,60,30,90,0,’PNG’);

  7. can anyone help m with this error?

    Fatal error: Uncaught Error: Class “FPDF” not found in D:\xampp\htdocs\report\generate_pdf.php:6 Stack trace: #0 {main} thrown in D:\xampp\htdocs\report\generate_pdf.php on line 6

  8. Hey, I’m trying to view live demo. But it showing error like this
    Connection failed: Access denied for user ‘phpflowc_test’@’localhost’ (using password: YES)

Leave a Reply

Your email address will not be published. Required fields are marked *