Export to Excel, CSV, PDF, Print and Copy From jQuery Datatables using PHP MySQL and Ajax

In this tutorial, We will explain to you Exporting functionality with Datatable, PHP, MySQL, and AJAX. Export data functionality makes your web application user-friendly and helps the user to maintain list data. We will cover this tutorial in easy steps to create a live demo to create Exporting functionality with Datatable, PHP, MySQL, and AJAX. This is a very simple example, you can just copy paste, and change according to your requirement.

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
index.php
data.php
README.md
assets
- css
  - style.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:

<?php
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;
?>

<?php

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.

<?php
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;
    }
}
?>

<?php

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.

<?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;
    }
    
}
?>

<?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.

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

?>

<?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.

  <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.flash.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.html5.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.print.min.js"></script>
  <script type="text/javascript">
    jQuery(document).ready(function() {
      jQuery('#render-data').DataTable( {
          "paging": true,
          "processing": true,
          'serverMethod': 'post', 
          "ajax": "data.php",
          dom: 'lBfrtip',
          buttons: [
               'excel',  'csv', 'pdf', 'print', 'copy',
          ],
          "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]]
      } );
    } );

  </script>

jQuery(document).ready(function() {

jQuery('#render-data').DataTable( {

"paging": true,

"processing": true,

'serverMethod': 'post',

"ajax": "data.php",

dom: 'lBfrtip',

buttons: [

'excel', 'csv', 'pdf', 'print', 'copy',

"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]]

} );

</script>

Step 6: Create html file

Create a html file named index.php

<?php
include('templates/header.php');
?>
 <section class="showcase">
    <div class="container">
        <div class="row padall border-bottom">
            <div class="col-lg-12">
                <h2>Export to Excel, CSV, PDF, Print and Copy From jQuery Datatables using PHP MySQL and Ajax </h2>               
            </div>
        </div>
            <div class="row padall border-bottom">
                <div class="col-lg-12">
                    <table id="render-data" class="display nowrap" style="width:100%">
                        <thead>
                            <tr>
                                <th>First Name</th>
                                <th>Last Name</th>
                                <th>Email</th>
                                <th>Mobile</th>
                                <th>Address</th>
                                <th>Salary</th>
                            </tr>
                        </thead>
                        <tbody>
                            
                        </tbody>
                        <tfoot>
                            <tr>
                                <th>First Name</th>
                                <th>Last Name</th>
                                <th>Email</th>
                                <th>Mobile</th>
                                <th>Address</th>
                                <th>Salary</th>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
        </div>
    </div>
</section>
<?php
include('templates/footer.php');
?>

<?php

include('templates/header.php');

<h2>Export to Excel, CSV, PDF, Print and Copy From jQuery Datatables using PHP MySQL and Ajax </h2>

</div>

<thead>

<tr>

<th>First Name</th>

<th>Email</th>

<th>Mobile</th>

<th>Address</th>

<th>Salary</th>

</tr>

</thead>

<tbody>

</tbody>

<tfoot>

<tr>

<th>First Name</th>

<th>Email</th>

<th>Mobile</th>

<th>Address</th>

<th>Salary</th>

</tr>

</tfoot>

</table>

</div>

</section>

<?php

include('templates/footer.php');

Demo Download