Export jQuery Datatable Data To PDF, Excel, CSV & Copy with PHP

No doubt, the jQuery Datatable is a highly flexible jQuery plugin that help to convert HTML table into useful grid layout. The plugin enables to create Data Table into full functional data grid with many features like pagination, instant search, export table data, multi-column ordering etc.

In our previous jQuery Datable tutorial, you have learned Datatable Server Side Processing with PHP & MySQL and Load and Refresh jQuery DataTable with PHP & MySQL. In this tutorial, you will learn how to export jQuery Datatable data to PDF, Excel, CSV, Copy using PHP & MySQL.

Also, read:

• Create Live Editable Table with jQuery, PHP and MySQL
• Inline Editing using PHP MySQL and jQuery Ajax
• Export jQuery Datatable Data To PDF, Excel, CSV & Copy with PHP

In this tutorial, we have used ExportButton Plugin to export Datatable data into in CSV,PDF,Excel etc format as well as Copy the table data into clipboard. Please keep in mind this functionality will work only HTML5 supported browsers.

So let’s start the coding.

Step1: First we will include these necessary Datatable plugin files to load Datable and export data.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/
jquery.min.js"></script>
<link rel="stylesheet" type="text/css" 
href="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,
pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,
b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.css"/> 
<script type="text/javascript" src="https://cdn.datatables.net/
r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,
b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/
datatables.min.js"></script>

Step2: Now we will create Datatable HTML according to jQuery Datatable structure.

<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
</table>

Step3: Here we will handle Datatable functionality using jQuery Datatable plugin by making an ajax request to server side data.php to get data from MySQL database table to load to data table. We have initialized export Button by adding lBfrtip into DOM element. We also managed to add and customize Export Button using button json object here. In “buttons” array, we have passed copy, excel, csv, pdf and print options. These buttons value will be displayed under Export Button. I have also changed default button text to “Export” using “text” properties.

$( document ).ready(function() {	
	$('#example').DataTable({
		 "processing": true,
         "sAjaxSource":"data.php",
		 "pageLength": 5,
		 "dom": 'lBfrtip',
		 "buttons": [
            {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            }
        ]
	});	
});

Step4: Now finally in data.php, we will get data from MySQL database and returned as JSON through PHP function json_encode with Datatable plugin options.

<?php
// initilize all variables
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
//define index of columns
$columns = array( 
	0 =>'id',
	1 =>'employee_name', 
	2 => 'employee_salary'	
);
$where = $sqlTot = $sqlRec = "";
// getting total number records from table without any search
$sql = "SELECT * FROM `employee` ";
$sqlTot .= $sql;
$sqlRec .= $sql;
$sqlRec .=  " ORDER BY employee_name";
$queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));
$totalRecords = mysqli_num_rows($queryTot);
$queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");
// iterate on results row and create new index array of data
while( $row = mysqli_fetch_row($queryRecords) ) { 
	$data[] = $row;
}	
$json_data = array(
		"draw"            => 1,   
		"recordsTotal"    => intval( $totalRecords ),  
		"recordsFiltered" => intval($totalRecords),
		"data"            => $data
		);
// send data as json format
echo json_encode($json_data);  
?>

You may also like:

• Star Rating System with Ajax, PHP and MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Your Own CAPTCHA Script with PHP
• Convert Unix Timestamp To Readable Date Time in PHP
• Inventory Management System with Ajax, PHP & MySQL
• Create Live Editable Table with jQuery, PHP and MySQL
• Live Add Edit Delete datatables Records with Ajax, PHP and MySQL
• Stripe Payment Gateway Integration in PHP
• Export Data to Excel with PHP and MySQL
• Star Rating System with Ajax, PHP and MySQL
• Create Dynamic Bootstrap Tabs with PHP & MySQL
• How To Create Simple REST API in PHP

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download