Introduction
The tutorial, CodeIgniter 4 Generate PDF Report will show you how to fetch data from MySQL database and generate PDF document using CodeIgniter 4 framework. Here I am going to use how to use TCPDF third party library with CodeIgniter 4 framework to put data into pdf document. CodeIgniter framework does not provide any built-in facility to generate pdf document, so I am using third party PHP library TCPDF.
Related Posts:
Advantages of PDF Document
- PDF format reports allows professionals to edit, share, collaborate and ensure the security of the content within digital documents.
- Reports are mostly generated in PDF format because a PDF file is a “read only” document that cannot be altered without leaving an electronic footprint whereas other formats like image, word, excel etc. can easily be altered without leaving an electronic footprint.
- PDF files are compatible across multiple platforms whereas a word document may not be viewable easily in Mac system.
Prerequisites
PHP 7.4.23, CodeIgniter 4.1.4, MySQL Server 8.0.26
Project Directory
It’s assumed that you have setup PHP and CodeIgniter in your system.
Now I will create a project root directory called codeigniter-pdf-report-generation.
Now move all the directories and files from CodeIgniter framework into the project root directory.
I may not mention the project root directory in subsequent sections, and I will assume that I am talking with respect to the project root directory.
MySQL table
First thing is to create a database table in MySQL database. In this example the database table will contain date wise sales information for products. Therefore, I will create a table called product in MySQL database. If you are using MySQL version less than 8 then you need to specify the size of the column value for int data type.
CREATE TABLE `product` (
`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
`sale_price` double COLLATE utf8mb4_unicode_ci NOT NULL,
`sales_count` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
`sale_date` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Run the above table creation script into the MySQL server and a table with the name product will be created. You may have multiple tables with normalization, but here for example purpose, the above table would be enough to show an idea how to generate HTML table from MySQL database using Codeigniter.
In order to test the application, I need some data in the above table. So, insert some dummy data into the products table.
insert into product(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','10-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34100','58','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-10-2021');Load TCPDF Library
TCPDF is a third-party library, and I will use here auto-load feature of CodeIgniter to load the library only once. Later the class from this library will be used from anywhere of this application.
You may check how to load third party library in CodeIgniter 4 or you can check the documentation of CodeIgniter 4.
I have placed the tcpdf library under the folder app/ThirdParty/tcpdf, where tcpdf folder has been created under the app/ThirdParty folder.
Edit the config file app/Config/Autoload.php and update the $psr4 section as follows:
public $psr4 = [
APP_NAMESPACE => APPPATH, // For custom app namespace
'Config' => APPPATH . 'Config',
"App" => APPPATH,
"TCPDF" => "ThirdParty/tcpdf"
];Next map the class of the tcpdf library so that application performance increases slightly by loading the appropriate class during library loading:
public $classmap = [
"TCPDF" => APPPATH . "ThirdParty/tcpdf/tcpdf.php"
];You may delete examples and tools directories from tcpdf directory. These directories are not required.
Custom Library
Now to get advantage of tcpdf library I won’t use tcpdf library directly, instead I will create a new library class that will extend the tcpdf functionality into the application. So, create a new file inside app/Libraries/PdfLibrary.php and create class PdfLibrary that extends TCPDF.
<?php
namespace App\Libraries;
use TCPDF;
/**
* Description of Pdf Library
*
* @author https://roytuts.com
*/
class PdfLibrary extends TCPDF {
function __construct() {
parent::__construct();
}
//Page header
public function Header() {
// Set font
$this->SetFont('helvetica', 'B', 20);
// Title
$this->Cell(0, 15, 'Sales Information for Products', 0, false, 'C', 0, '', 0, false, 'M', 'M');
}
// Page footer
public function Footer() {
// Position at 15 mm from bottom
$this->SetY(-15);
// Set font
$this->SetFont('helvetica', 'I', 8);
// Page number
$this->Cell(0, 10, 'Page '.$this->getAliasNumPage().'/'.$this->getAliasNbPages(), 0, false, 'C', 0, '', 0, false, 'T', 'M');
}
}In the library class above, I have overridden the header and footer of the pdf document which will be generated using CodeIgniter and TCPDF library.
The default header and footer functions basically print tcpdf logo, author information etc in the generated pdf files. So, you may not like to have that information into your generated pdf files. That’s why, I am overriding the default functionalities of the header and footer. You may add your own implementation in your application for these two functions.
I have set font size, margin from bottom, title of the header and footer.
Database Configuration
You need to setup database connection in order to fetch or write data to the table. The following configuration is done in the file app/Config/Database.php under the default group of database setting. Make sure you change or update the configuration according to yours. You can also setup your database using the .env file.
The main properties I have shown below, and you can change according to your values:
...
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
...
'charset' => 'utf8mb4',
'DBCollat' => 'utf8mb4_unicode_ci',
...Model Class
The model class is responsible for interacting with database and performing required activities as requested by clients.
The following code is written into the file app/Models/ProductModel.php file:
<?php
namespace App\Models;
use CodeIgniter\Model;
class ProductModel extends Model {
protected $product = 'product';
function get_product_list() {
$query = $this->db->table($this->product)->get();
return $query->getResult();
}
}In the above model class, I have defined a function get_product_list() that just returns all records from the table. I am using CodeIgniter’s get() function to fetch all records from the table followed by getResult() function to return as objects.
Controller Class
The controller class is the entry point for the web application and a controller class handles request and response coming from and going back to clients.
The controller class performs the business logic for the application. The controller class is also responsible for validating, sanitizing, filtering the malformed request data before the data can be processed further for the application requirements.
The code for controller class is written into a file app/Controllers/ProductController.php.
<?php
namespace App\Controllers;
use App\Models\ProductModel;
use App\Libraries\PdfLibrary;
class ProductController extends BaseController {
public function index() {
$model = new ProductModel();
$data['salesinfo'] = $model->get_product_list();
return view('product', $data);
}
public function generate_pdf() {
$pdf = new PdfLibrary(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
// set document information
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('https://roytuts.com');
$pdf->SetTitle('Sales Information for Products');
$pdf->SetSubject('Report generated using Codeigniter and TCPDF');
$pdf->SetKeywords('TCPDF, PDF, MySQL, Codeigniter');
// set default header data
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE, PDF_HEADER_STRING);
// set header and footer fonts
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
// set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
// set image scale factor
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
// set font
$pdf->SetFont('times', 'BI', 12);
// ---------------------------------------------------------
//Generate HTML table data from MySQL - start
$table = new \CodeIgniter\View\Table();
$template = array(
'table_open' => '<table border="1" cellpadding="2" cellspacing="1">'
);
$table->setTemplate($template);
$table->setHeading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
$model = new ProductModel();
$salesinfo = $model->get_product_list();
foreach ($salesinfo as $sf):
$table->addRow($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
endforeach;
$html = $table->generate();
//Generate HTML table data from MySQL - end
// add a page
$pdf->AddPage();
// output the HTML content
$pdf->writeHTML($html, true, false, true, false, '');
// reset pointer to the last page
$pdf->lastPage();
//Close and output PDF document
$pdf->Output(md5(time()).'.pdf', 'D');
}
}Notice in the above generate_pdf() function I have loaded the library PdfLibrary that extended the tcpdf library. I have done necessary configurations for pdf generation and I am generating pdf using $pdf->Output() function.
I have passed first argument as the name of the pdf file and this is encrypted using time(). The second argument is D that tells force download instead of displaying in the browser. If you need to display the generated pdf in browser then use I as the second parameter. If you need to save to some location, then use F as the second parameter to the function.
The index() function passes the MySQL data to the view file where data are displayed in tabular format in HTML table using CodeIgniter 4 framework.
View
The view file which will display the data or UI (User Interface) to the end users. The app/Views/product.php displays all sales information for products in HTML tabular format.
I have also created a hyper link using CodeIgniter’s anchor() function. This hyperlink will give end users to generate PDF report. Clicking on this link will give end users to save the generated pdf document.
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Generate PDF Report From MySQL Database Using Codeigniter 4</title>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width:
100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
</head>
<body>
<div style="margin: auto;width: 600px">
<h3>Sales Information</h3>
<?php
echo anchor('productcontroller/generate_pdf', 'Generate PDF Report') . '<p/>';
$table = new \CodeIgniter\View\Table();
$table->setHeading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
foreach ($salesinfo as $sf):
$table->addRow($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
endforeach;
echo $table->generate();
?>
</div>
</body>
</html>Route Configuration
You also need to configure route to point to your own controller file instead of the default controller that comes with the framework.
Search for the line $routes->setDefaultController(‘Home’); and replace it by $routes->setDefaultController(‘ProductController’);.
Search for the line $routes->get(‘/’, ‘Home::index’); and replace it by your controller name, for this example, $routes->get(‘/’, ‘ProductController::index’);.
These route configurations are done on the file app/Config/Routes.php.
Deploying PDF Report Generation Application
I am not going to use any external server but CLI command to run the application. Make sure you start the MySQL database server before you start your application. If you want to use external server to run your application, you can use, for example, Apache HTTP Server. Execute the following command on your project root directory to run your application.
php spark serveYour application will be running on localhost and port 8080.
Testing PDF Report Generation Application
When you hit the UTR http://localhost:8080 in the browser, you will see the following page:
Clicking on the Generate PDF Report will give you the generated PDF document and ask for the location for saving it.
The generated PDF will have page 1/1 in the footer section and sales information in the body.