Introduction

Here you will see how to generate pdf report from MySQL database using Codeigniter. Codeigniter is one of the popular PHP framework for web applications.

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.

Related Posts:

• Generate PDF Report using CodeIgniter 4
• Generate HTML Table Data in CodeIgniter 3

Here I will create a web application to generate report from MySQL database using Codeigniter. I will create a table in MySQL database and retrieve the records from the table and display on a view in table format. I will provide a hyper link to generate the PDF from the table data. You will be given a popup asking to save the generated pdf file. Then you can choose your convenient location for saving the file.

Prerequisites

PHP 7.3.5 – 7.4.23, Apache HTTP Server 2.4 (Optional), CodeIgniter 3.1.9 – 3.1.11, MySQL Server 5.6 – 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;

For MySQL 5.x version, you can use the following structure:

CREATE TABLE `product` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(100) NOT NULL,
	`price` double NOT NULL,
	`sale_price` double NOT NULL,
	`sales_count` int(10) unsigned NOT NULL,
	`sale_date` varchar(10) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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');

Now I will move to Codeigniter part and for this either you may use any editor like notepad or notepad++ or you may use any IDE to develop your application code.

If you want to use Netbeans IDE for working with Codeigniter then please read here how to configure Codeigniter, XAMPP with Netbeans.

Database Configuration

Now configure the database section in order to connect with MySQL database for performing database activities in the file application/config/database.php. Please replace below values with your own values.

The main properties I have shown below, and you can change according to your values:

$db['default']['username'] = 'root';
$db['default']['password'] = 'root';
$db['default']['database'] = 'roytuts';

Auto Load Configuration

Now auto-load database library because this is one time activity, and you may not want to load every time you want to query database. I have also auto-loaded table library in order to generate table from MySQL database using Codeigniter. Edit the appropriate sections in the file application/config/autoload.php.

$autoload['libraries'] = array('database', 'table');
$autoload['helper'] = array('url');

Now I will implement the example on generate pdf report from MySQL database using Codeigniter using the following steps.

Model Class

The model class is responsible for interacting with database and performing required activities as requested by clients.

Now I will create model class in order to fetch data from database table called product. Create a php file product_model.php under application/models directory with below code.

<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');
	
/**
* Description of Product_model
*
* @author https://roytuts.com
*/

class Product_model extends CI_Model {		
	
	private $product = 'product';

	function get_salesinfo() {
		$query = $this->db->get($this->product);

		if ($query->num_rows() > 0) {
			return $query->result();
		}
		
		return NULL;
	}
	
}

/* End of file product_model.php */
/* Location: ./application/models/product_model.php */

In the above model class, I have defined a function get_salesinfo() that just returns all records from the table and if there is no data we just return NULL.

Custom Library

This application will generate pdf report from our MySQL database using Codeigniter but Codeigniter does not provide any built-in functionality for generating pdf report. So we need to use third party library for pdf generation. One such library can be downloaded from the URL tcpdf library.

Now extract the downloaded library and put it into application/libraries/tcpdf folder. tcpdf directory does not exist by default and you have to create it inside application/libraries directory.

Now extract the downloaded tcpdf library and put the extracted files and folder under application/libraries/tcpdf directory. You may delete examples and tools directories from tcpdf directory. These directories are not required.

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 for using into our own application. So create a new file inside application/libraries/Pdf.php and create class Pdf that extends TCPDF.

<?php

if(!defined('BASEPATH')) exit('No direct script access allowed');

require_once dirname(__FILE__) . '/tcpdf/tcpdf.php';

class Pdf 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');
    }
	
}

/* End of file Pdf.php */
/* Location: ./application/libraries/Pdf.php */

In the above file I have overridden the default header and footer functions from the tcpdf library. These default header and footer functions basically print tcpdf logo, author information etc in the generated pdf files. So you may not like to have those 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 applications.

Controller Class

Now I have already existing controller file application/controllers/product.php. Therefore I will just add a new functionality to the existing class file to generate pdf report from MySQL database using Codeigniter. Add below function generate_pdf() to the existing controller. Please do not delete or modify the existing code from the controller.

<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

/**
* Description of product
*
* @author https://roytuts.com
*/

class Product extends CI_Controller {

	function __construct() {
		parent::__construct();
		$this->load->model('product_model');
	}

	public function index() {
		$data['salesinfo'] = $this->product_model->get_salesinfo();
		$this->load->view('product', $data);
	}

	public function generate_pdf() {
		//load pdf library
		$this->load->library('Pdf');
		
		$pdf = new Pdf(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
		$template = array(
			'table_open' => '<table border="1" cellpadding="2" cellspacing="1">'
		);

		$this->table->set_template($template);

		$this->table->set_heading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
		
		$salesinfo = $this->product_model->get_salesinfo();
			
		foreach ($salesinfo as $sf):
			$this->table->add_row($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
		endforeach;
		
		$html = $this->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');
	}
	
}

/* End of file product.php */
/* Location: ./application/controllers/product.php */

Notice in the above function I have loaded the library Pdf that extended the tcpdf library. I have done necessary configurations for pdf generation and I am generating html data from the MySQL table data and then finally we are generating pdf using $pdf->Output() function. We have passed first argument as the name of the pdf file and this is encrypted using time().

The second argument is D that tells to force download instead of displaying on browser. If you need to display the generated pdf on 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.

View File

Now I will add just one hyperlink on which user will click to generate pdf report from MySQL database using Codeigniter. So let’s create hyperlink on the view file.

<?php
	echo anchor('product/generate_pdf', 'Generate PDF Report');
	
	echo '<p/>';

	$this->table->set_heading(...

The complete source code can be downloaded from the Source Code section below.

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.

Now I have to point the Product controller class in application/config/routes.php file otherwise I won’t be able to see the generated table and I will get 404 page not found error.

$route['default_controller'] = 'product';

Deploying the 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 -S localhost:8000

Your application will be running on localhost and port 8000.

Testing PDF Report Generation Application

The above hyperlink appears just above the HTML table as shown below in the image.

So I have finished coding part. I will now test the application by hitting the URL http://localhost:8080 in the browser and click on the hyperlink Generate PDF Report. You will get popup with pdf file asking for saving to a destination directory. The generated PDF will have page 1/1 in the footer section and sales information in the body.

You have successfully generated pdf report from MySQL database using Codeigniter.

Source Code

Download