Generate PDF from MySQL Data using FPDF
by Vincy. Last modified on July 15th, 2022.
In this tutorial, we are going to see how to generate PDF from the MySQL table by using the FPDF library. In a previous tutorial, we have seen simple PDF generation from text file content by using this library.
In this example, we read MySQL table data by using PHP. And then we are calling FPDF functions to generate PDF from this MySQL data.
If you want a code to generate PDF from HTML on the client-side, visiting the linked article will be helpful.
FPDF PDF Generation from MySQL using PHP
We have a MySQL table containing the same data that we have in a text file for the previous FPDF-PDF generation tutorial. We read this data into an array and pass it to the FPDF function to generate code.
This screenshot shows the output screen that shows the list of database results with the Generate PDF button. On clicking this button the fpdf library is invoked to build the PDF report with the MySQL result
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$result = $db_handle->runQuery("SELECT * FROM toy");
$header = $db_handle->runQuery("SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='blog_samples'
AND `TABLE_NAME`='toy'");
require('fpdf/fpdf.php');
$pdf = new FPDF();
$pdf->AddPage();
$pdf->SetFont('Arial','B',12);
foreach($header as $heading) {
foreach($heading as $column_heading)
$pdf->Cell(90,12,$column_heading,1);
}
foreach($result as $row) {
$pdf->SetFont('Arial','',12);
$pdf->Ln();
foreach($row as $column)
$pdf->Cell(90,12,$column,1);
}
$pdf->Output();
?>
DBController.php
This file contains the functions to establish a database connection and also the functions to read data from the database. The runQuery() function reads and returns the database results that are used to generate the PDF formatted report.
<?php
class DBController {
private $host = "localhost";
private $user = "root";
private $password = "test";
private $database = "blog_samples";
private static $conn;
function __construct() {
$this->conn = $this->connectDB();
if(!empty($this->conn)) {
$this->selectDB();
}
}
function connectDB() {
$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
return $conn;
}
function selectDB() {
mysqli_select_db($this->conn, $this->database);
}
function runQuery($query) {
$result = mysqli_query($this->conn, $query);
while($row=mysqli_fetch_assoc($result)) {
$resultset[] = $row;
}
if(!empty($resultset))
return $resultset;
}
function numRows($query) {
$result = mysqli_query($this->conn, $query);
$rowcount = mysqli_num_rows($result);
return $rowcount;
}
}
?>
SQL Script
Run this script for the MySQL data.
--
-- Table structure for table `toy`
--
CREATE TABLE IF NOT EXISTS `toy` (
`Name` varchar(55) NOT NULL,
`Type` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `toy`
--
INSERT INTO `toy` (`Name`, `Type`) VALUES
('Ben 10 Watch', 'Battery Toys'),
('Angry Birds Gun', 'Mechanical Toys'),
('Remote Car', 'Remote Toys'),
('Uno Cards', 'Card Game'),
('Keyboard', 'Musical Toys'),
('Jigsaws', 'Board Game');
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.