Export DataTables Data using PHP and MySQL

by Vincy. Last modified on July 12th, 2022.

The DataTables is a feature-packed library that allows us to search, sort, paginate and export DataTables data. Also, it provides a rich tabular interface with intuitive elements to give control over the data management process.

In a previous tutorial, we have seen how to use DataTables library data to display a rich looking responsive DataTables. Also, we have seen how to add column wise search feature in the header or footer of the DataTables. In this tutorial, we are going to see about DataTables export option to take the data backup.

Taking backups from any data source like a database, file storage is a vital facility for any application. Previously, we have seen the code for creating a backup client to export data from the Database.

The backup data will be useful in many ways depends on the need. For example, if any data loss occurs unfortunately due to some crack or hack, the backup will be used indeed. If you want the code in PHP to import backup data into the database the linked tutorial will be useful.

Export DataTables Data using PHP and MySQL

In this example, it will allow the user to export DataTables data in three various formats. Those are CSV, Excel and PDF formats. Also, it will show the option to copy the Datatables data to the clipboard by using the Copy button control.

Landing Page with Buttons to Export DataTables Data

This is the code for the landing page of this example. It contains the DataTables library and includes to initializes it with the options to enable the export feature.

It shows the HTML table markup which will be converted into a DataTable by loading database results. This HTML table is uniquely referred to as an id attribute and this id will be used as a selector to initialize the DataTables.

It is also possible to export HTML tables to PDF using this library.

<!DOCTYPE html>
<head>
    <title> Export Data from Datatables using PHP and MySQL</title>
    <link href="style.css" rel="stylesheet" type="text/css" />

    <script src="vendor/jquery/jquery-3.2.1.min.js" type="text/javascript"></script>
    <link rel="stylesheet"  href="vendor/DataTables/jquery.datatables.min.css">	
    <script src="vendor/DataTables/jquery.dataTables.min.js" type="text/javascript"></script> 

    <link rel="stylesheet"  href="vendor/DataTables/buttons.datatables.min.css">    
    <script src="vendor/DataTables/dataTables.buttons.min.js" type="text/javascript"></script> 
    <script src="vendor/DataTables/jszip.min.js" type="text/javascript"></script> 
    <script src="vendor/DataTables/pdfmake.min.js" type="text/javascript"></script> 
    <script src="vendor/DataTables/vfs_fonts.js" type="text/javascript"></script> 
    <script src="vendor/DataTables/buttons.html5.min.js" type="text/javascript"></script> 

</head>
<body>
    <div class="container">
        <h2>Export Data from Datatables using PHP and MySQL</h2>
        <table name="employeeTable" id="employeeTable" class="display" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Age</th>
                    <th>Salary</th>
                </tr>
            </thead>
        </table>

    </div>
</body>
</html>

The script is to initialize the DataTables library is here in the below code block. In this initialization, the options are specified to enable the DataTables export buttons visibility.

<script>
    $(document).ready(function () {
        var table = $('#employeeTable').DataTable({
            "paging": false,
            "processing": true,
            "serverSide": true,
            'serverMethod': 'post',
            "ajax": "server.php",
            dom: 'Bfrtip',
            buttons: [
                {extend: 'copy', attr: {id: 'allan'}}, 'csv', 'excel', 'pdf'
            ]
        });

    });
</script>

Note: The export action will take the backup of the records that are only displayed in the DataTables. So, we need to select All from the length menu to export entire data.

Database Script

This SQL script contains the CREATE statement and the data dump of the tbl_employee. By importing this script in your database, you can use this data to load into the DataTables.

--
-- Table structure for table `tbl_employee`
--

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_employee`
--

INSERT INTO `tbl_employee` (`id`, `name`, `age`, `salary`) VALUES
(1, 'Jone Thomas', 25, 320800),
(2, 'Tim Cook', 31, 170750),
(3, 'Martha Welington', 32, 86000),
(4, 'Kevin Julius', 33, 433060),
(5, 'Kim Holinshade', 34, 162700),
(6, 'Helen Margrate', 35, 372000),
(7, 'Greata Aurther', 36, 137500),
(104, 'Linda Karoline', 45, 200000),
(103, 'Evita Benjamin', 23, 100000),
(102, 'Mike Rosario', 45, 200000),
(101, 'Jelinda Joel', 23, 100000);

--
-- Indexes for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=105;
COMMIT;

PHP Code to Load DataTable Data using Server-side processing

I have used server-side processing loading the database rows into the DataTables. In a previous tutorial, we have learned how to initiate Datatables server-side processing to load dynamic data.

This is the server.php file is used to get the database results by accessing DataTables SSP class. In this file SSP class’s simple function is called by sending the database configurations, request method type and many parameters.

<?php

// DB table to use
$table = 'tbl_employee';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'name', 'dt' => 0 ),
array( 'db' => 'age', 'dt' => 1 ),
array( 'db' => 'salary', 'dt' => 2 )
);

// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'phppot',
'host' => 'localhost'
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/

require( 'vendor/DataTables/server-side/scripts/ssp.class.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

Export DataTables Data using PHP and MySQL Output

The following screenshot shows the export button controls added above the DataTables. By clicking these buttons the data displayed in the DataTables will be exported into the corresponding format.

In a previous article, we have seen how to export tabular data to a CSV file using PHP. Also, we have seen how to do excel export from HTML table data. If you are searching for such custom PHP codes without using any external libraries like DataTables you can download the source from the linked tutorials.

Download

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Comments to “Export DataTables Data using PHP and MySQL”

Leave a Reply

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

Related Tutorials

↑ Back to Top

Share this page