Here we 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.

Here we will create a web application to generate report from MySQL database using Codeigniter. We will create a table in MySQL database and retrieve the records from the table and display on a view in table format. We 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

Knowledge of PHP & Codeigniter 3
Knowledge of MySQL or any other database

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

We need to create MySQL table and autoload few libraries, helper class etc. Also we need to generate HTML table in view. So to finish all these steps please read the tutorial Generate HTML table from MySQL database using Codeigniter before further proceeding. We will add our additional functionality for generating pdf report from MySQL table here.

We 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 we won’t use tcpdf library directly, instead we 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 we 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 we are overriding the default functionalities of the header and footer. You may add your own implementation in your application for these two applications.

Please make sure you have read Generate HTML table from MySQL database using Codeigniter before reading this tutorial.

Now we have already existing controller file application/controllers/product.php. Therefore  we will just add our 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.

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://www.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');
}

Notice in the above function we have loaded the library Pdf that extended the tcpdf library. We have done necessary configurations for pdf generation and we are 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.

We don’t have any change or modification to the model class, so it remains as it is.

Now we 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 above hyperlink appears just above the HTML table as shown below in the image.

generate pdf report from mysql database using codeigniter

So we have finished coding part. We will now test our application. Hit the URL http://localhost/ci3/index.php 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 report will look something similar to below image.

generate pdf report from mysql database using codeigniter

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

Thanks for reading.

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials Twitter Facebook  Google Plus Linkedin Or Email Me

Leave a Reply

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