Before started to implement the Exporting functionality with Datatable, look files structure:
- export-jquery-datatable-data-to-excel-csv-pdf-copy-print
- include
- constants.php
- DBConnection.php
- Search.php
- templates
- header.php
- footer.php
- include
- index.php
- data.php
- README.md
- assets
- css
- style.css
- css
Features of Exporting functionality with Datatable
- Save as Excel (XLSX)
- Save as CSV
- Save as PDF
- Display a print view
- Copy to clipboard
Step 1: First, Create Database
For this tutorial, you need a MySQL database with the following table:
CREATE TABLE `employees` (
`id` int(11) NOT NULL COMMENT 'primary key',
`name` varchar(255) NOT NULL COMMENT 'Employee Name',
`last_name` varchar(100) DEFAULT NULL,
`email` varchar(255) NOT NULL COMMENT 'Email Address',
`contact_no` varchar(16) DEFAULT NULL,
`address` text,
`salary` float(10,2) NOT NULL COMMENT 'employee salary'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';
INSERT INTO `employees` (`id`, `name`, `last_name`, `email`, `contact_no`, `address`, `salary`) VALUES
(1, 'Ashton', 'Cox', 'cox@techarise.com', '9000000001', 'Tokyo', 300001.00),
(2, 'Bradley', 'Greer', 'greer@techarise.com', '9000000002', 'Landon', 200001.00),
(3, 'Brenden', 'Wagner', 'wagner@techarise.com', '9000000003', 'New York', 500001.00),
(4, 'Brielle', 'Williamson', 'williamson@techarise.com', '9000000004', 'Cape Town', 600001.00),
(5, 'Caesar', 'Vance', 'vance@techarise.com', '9000000005', 'Sydney', 500002.00),
(6, 'Cedric', 'Kelly', 'kelly@techarise.com', '9000000006', 'Wellington', 600003.00),
(7, 'Prescott', 'Bartlett', 'Bartlett@techarise.com', '9000000007', 'Washington DC', 500002.00),
(8, 'Haley', 'Kennedy', 'Kennedy@techarise.com', '9000000013', 'Amsterdam', 678003.00),
(9, 'Charde', 'Marshall', 'marshall@techarise.com', '9000000008', 'Madrid', 509002.00),
(10, 'Jaeeme', 'Khan', 'khan@techarise.com', '9000000014', 'Delhi', 542003.00),
(11, 'Bunty', 'Bably', 'bably@techarise.com', '9000000009', 'Ottawa', 657002.00),
(12, 'Hermione', 'Butler', 'Butler@techarise.com', '9000000015', 'Edinburgh', 987003.00),
(13, 'Sonia', 'Khan', 'sonia@techarise.com', '9000000010', 'Oslo', 345002.00),
(14, 'Herrod', 'Chandler', 'Chandler@techarise.com', '9000000016', 'Abu Dhabi', 603003.00),
(15, 'Roney', 'Rockey', 'rockey@techarise.com', '9000000011', 'Berlin', 321002.00),
(16, 'Howard', 'Hatfield', 'Hatfield@techarise.com', '9000000017', 'Ankara', 123003.00),
(17, 'Gloria', 'Little', 'little@techarise.com', '9000000012', 'Rome', 920002.00),
(18, 'Jackson', 'Bradshaw', 'Bradshaw@techarise.com', '9000000018', 'Lisbon', 690003.00),
(19, 'Quinn', 'Flynn', 'Quinn@techarise.com', '9000000019', 'Antananarivo', 700003.00),
(20, 'Tatyana', 'Fitzpatrick', 'Fitzpatrick@techarise.com', '9000000020', 'Manila', 600001.00),
(21, 'Thor', 'Walton', 'Thor@techarise.com', '9000000021', 'Santiago', 304001.00);
ALTER TABLE `employees`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employees`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', AUTO_INCREMENT=22;
?>
Step 2: Database Configuration (constants.php, DBConnection.php)
The following code is used to connect the database using PHP and MySQL.
date_default_timezone_set('Asia/Kolkata');
$root = "http://" . $_SERVER['HTTP_HOST'];
$currentDir = str_replace(basename($_SERVER['SCRIPT_NAME']), "", $_SERVER['SCRIPT_NAME']);
$root .= $currentDir;
$constants['base_url'] = $root;
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'demo_DB');
define('SITE_URL', $constants['base_url']);
define('HTTP_BOOTSTRAP_PATH', $constants['base_url'] . 'assets/vendor/');
define('HTTP_CSS_PATH', $constants['base_url'] . 'assets/css/');
class DBConnection {
protected $host = DB_SERVER;
protected $dbname = DB_DATABASE;
protected $user = DB_USERNAME;
protected $pass = DB_PASSWORD;
protected $_db;
function __construct() {
try {
$this->_db = new PDO("mysql:host=$this->host;dbname=$this->dbname", $this->user, $this->pass);
}
catch (PDOException $e) {
echo $e->getMessage();
}
}
// return Connection
function returnConnection() {
return $this->_db;
}
// close Connection
public function closeConnection() {
$this->_db = null;
}
}
?>
Step 3: Create class
Create a file like Search.php.
require_once(dirname(__FILE__)."/DBConnection.php");
class Search
{
protected $db;
public function __construct() {
$this->db = new DBConnection();
$this->db = $this->db->returnConnection();
}
// get Blog Info function
public function getBlogInfo() {
$query = $this->db->prepare("SELECT name, last_name, email, contact_no, address, salary FROM employees");
$query->execute();
$result = $query->fetchAll();
return $result;
}
}
?>
Step 4: We will get data from MySQL database and returned as JSON through PHP function
json_encode with Datatable plugin options Create a file like data.php.
function __autoload($class) {
include "include/$class.php";
}
$srch = new Search();
$blogInfo = $srch->getBlogInfo();
$count = count($blogInfo);
$data = array(
'draw'=>1,
'recordsTotal'=>intval($count),
'recordsFiltered'=>intval($count),
'data'=>$blogInfo,
);
//send data as json format
echo json_encode($data);
?>
Step 5: We will include these necessary Datatable plugin files to load Datatable and export data.
We have define HTML table for initialization jQuery Datatable plugin on this page based on id selector:
#render-data.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 excel, csv, pdf, print and copy options.Step 6: Create html file
Create a html file named index.php
include('templates/header.php');
?>
Export to Excel, CSV, PDF, Print and Copy From jQuery Datatables using PHP MySQL and Ajax
First Name
Last Name
Mobile
Address
Salary
First Name
Last Name
Mobile
Address
Salary