Hey technoz, Many times in dynamic websites, we need to generate the report of some data and pdf is the most popular format of document. So, in this tutorial we will see how to create pdf file using php code using mysql database. Portable document files are secure, robust and convenient. Thats why we are going to implement it.
There is no any in-built function in php for generation of pdf files. So, we need to use a third party library for this task. There is an open-source FPDF library, download it from here. After downloading it, make a folder in your working directory as libs and extract the downloaded file contents in it.
Creating Database
First we’ll create a database and table with some data. So create database name phptopdf and run following SQL query in it to create a structure.
CREATE TABLE IF NOT EXISTS `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `student_name` varchar(255) NOT NULL COMMENT 'Student Name', `student_email` varchar(255) NOT NULL COMMENT 'Student Email', `student_attendance` int(11) NOT NULL COMMENT 'Student attendance', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COMMENT='datatable demo table';
We now need to put some records in the structure. Do this by running the following query. It will create upto 16 records in the database, and those will be outputted in the file.
INSERT INTO `employee` (`id`, `student_name`, `student_email`, `student_attendance`) VALUES (1, 'ABC', 'abc@domain.com', 61), (2, 'DEF', 'def@domain.com', 70), (3, 'GHI', 'ghi@domian.com', 66), (4, 'JKL', 'jkl@xyz.com', 50), (5, 'MNO', 'mno@domain.com', 75), (6, 'PQR', 'pqr@domian.com', 61), (7, 'STU', 'stu@domain.com', 80), (8, 'VWX', 'vwx@domain.com', 65), (9, 'YZA', 'yza@domain.com', 70), (10, 'AHT', 'aht@domain.com', 67), (11, 'HVK', 'hvk@domain.com', 80), (12, 'HTG', 'htg@domain.com', 58), (13, 'SKM', 'skm@domain.com', 85), (14, 'SMM', 'smm@domain.com', 87), (15, 'KSK', 'ksk@domain.com', 46), (16, 'KGS', 'kgs@domain.com', 66);
Connect to MySQL database
Now, we have to connect to the database. In order to achieve this, make a new file for that purpose. Name it as connection.php and paste the following code in it. The code will bind our php project to the values’ container. We are using an object oriented approach to make the connection because it is well formatted and prevents SQ injections. We have defined the class “dbObj” with all credentials as variables. They are used in the mysqli_connect() function. The query will display connect failed message with the proper error so that it will be easy to debug .
<?php
Class dbObj{
/* Database connection */
var $dbhost = "localhost";
var $username = "root";
var $password = "";
var $dbname = "phptopdf";
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;
}
}
?>
Now create the main index.php file as follows. It is a simple view with a button. The button will redirect to the post url which then builds a portable document file format with all the values we have entered earlier.
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Generate pdf from mysq database using php</title> <link rel="stylesheet" href="css/bootstrap.min.css"> <link rel="stylesheet" href="css/bootstrap-theme.min.css"> <link rel="stylesheet" href="css/font-awesome.min.css"/> <link rel="stylesheet" href="css/style.css"/> </head> <body> <center> <div class="container" style="padding-top:50px"> <h2 class="text">Generate PDF file from MySQL database Using PHP<h2> <form class="form-inline" method="post" action="get_pdf.php" style="padding-top:30px"> <button type="submit" id="pdf" name="generate_pdf" class="button button1"><i class="fa fa-pdf"" aria-hidden="true"></i> Generate PDF</button> </form> <br/> A Tutorial on <a href="https://technopoints.co.in">Technopoints</a> </fieldset> </div> </center> </body> </html>
For a new fresh look, we have implemented some design using cascaded styles sheet. In order to achieve this, lets code some styling in style.css as a separate file.
.button {
background-color: #8B008B; /* Green */
border: none;
color: white;
padding: 16px 32px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 16px;
margin: 4px 2px;
-webkit-transition-duration: 0.4s; /* Safari */
transition-duration: 0.4s;
cursor: pointer;
}
.button1 {
background-color: white;
color: black;
border: 2px solid #8B008B;
border-radius: 12px;
}
.button1:hover {
background-color: #8B008B;
color: white;
}
.text{
color: purple;
text-shadow: 2px 2px #A9A9A9;
}
The index.php file will look like as image follows.
Create pdf file with php
In order to create pdf file using php (portable document format), create file get_pdf.php with the following code.
<?php
//include connection file
include_once("connection.php");
include_once('libs/fpdf.php');
class PDF extends FPDF
{
// Page header
function Header()
{
// Logo
$this->Image('pic.png',10,7,70);
$this->SetFont('Arial','B',13);
// Move to the right
$this->Cell(80);
// Title
//$this->Cell(80,10,'Students List',1,0,'C');
// Line break
$this->Ln(20);
$this->Cell(65);
$this->Cell(00,00,'Students List');
$this->Ln(10);
}
// 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');
// Position at 2.0 cm from bottom
$this->SetY(-20);
// Arial italic 8
$this->SetFont('Arial','B','I',8);
// Page number
$this->Cell(0,10,'https://technopoints.co.in',0,0,'C');
}
}
$db = new dbObj();
$connString = $db->getConnstring();
$display_heading = array('id'=>'Sr. No.', 'student_name'=> 'Name', 'student_attendance'=> 'Attendance (in %)','student_email'=> 'Email',);
$result = mysqli_query($connString, "SELECT id, student_name, student_email, student_attendance FROM students") or die("database error:". mysqli_error($connString));
$header = mysqli_query($connString, "SHOW columns FROM students");
$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();
?>
In above file, we are including a downloaded library file and extending it’s FPDF class to inherit it’s predefined functions and use them in our file. We have defined a separate functions for header and footer of the file. Then, the further logic shows how to fetch the data from mysql and display it in the file with well formatting.
Output pdf file
That’s it! Finally After clicking on the button, file generation will be done with all available records fetched and displayed as shown in image below.
If you have any doubts, let me know in comment box below. Alternatively, you can to download the source code from following link.
7 thoughts on “Create pdf file using php, mysql database”