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. Today in this tutorial, you will learn how to export jQuery Datatable data to PDF, Excel, CSV, Copy using PHP & MySQL.

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" arary, 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 can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]