Reading Time: 6 minutes

2,381 Views

Inside this article we will see the concept of PHP & MySQLi How To Add Export Buttons to DataTable. Article contains the classified information about adding export buttons to DataTable.

Export data from datatable is very useful for admin panels. Export button can be of any type – PDF, Excel, CSV, etc. We will use jQuery datatable concept and also the logic to add export buttons.

If you are looking for an article which gives you the detailed concept about adding export buttons to datatable for rows. Then this article will give you a lot of things to understand in a very clear way.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Table we need – employees

CREATE TABLE `employees` (
 `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `email` varchar(50) NOT NULL,
 `mobile` varchar(30) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

This code will create employees table.

Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files inside this application –

Create a folder with name php-mysqli-datatable-export-buttons and create these 2 files into it – dbconfig.php and index.php.

Database Configuration

Open dbconfig.php file from folder. Add these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi How To Add Export Buttons To DataTable Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Application Programming

Open index.php file and write this following code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi How To Add Export Buttons To DataTable Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Include the database file
require 'dbconfig.php';

$query = "SELECT * FROM employees ORDER BY id DESC";

$statement = $conn->prepare($query);
$statement->execute();
$result = $statement->get_result();

$employees = array();

while ($item = $result->fetch_assoc()) {
    $employees[] = $item;
}

?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css" />
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css" />
</head>

<body>

    <div class="container" style="margin-top:30px;">
        <h4 style="text-align: center;">PHP & MySQLi How To Add Export Buttons To DataTable Tutorial by Online Web Tutor</h4>

        <div class="panel panel-primary">
            <div class="panel-heading">
                Employees Report
            </div>
            <div class="panel-body">
                <table class="table table-striped" id="tbl-employees">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Mobile</th>
                        </tr>
                    </thead>
                    <tbody>

                        <?php
                        if (count($employees) > 0) {

                            $count = 1;

                            foreach ($employees as $emp) {
                        ?>
                                <tr>
                                    <td><?= $count++ ?></td>
                                    <td><?= $emp['name'] ?></td>
                                    <td><?= $emp['email'] ?></td>
                                    <td><?= $emp['mobile'] ?></td>
                                </tr>
                        <?php
                            }
                        }
                        ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.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.6.5/js/buttons.html5.min.js"></script>

    <script>
        $(function() {
            $("#tbl-employees").DataTable({
                dom: 'Bfrtip',
                buttons: [
                    'copyHtml5',
                    'excelHtml5',
                    'csvHtml5',
                    'pdfHtml5',
                    'pageLength'
                ]
            });
        })
    </script>

</body>

</html>

Concept

Get data from database table –

$query = "SELECT * FROM employees ORDER BY id DESC";

$statement = $conn->prepare($query);
$statement->execute();
$result = $statement->get_result();

$employees = array();

while ($item = $result->fetch_assoc()) {
    $employees[] = $item;
}

DataTable Plugin and Export button plugin files –

CSS Files

<link rel="stylesheet" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.5/css/buttons.dataTables.min.css" />

JS Files

    <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.6.5/js/dataTables.buttons.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.6.5/js/buttons.html5.min.js"></script>

Usage

$("#tbl-employees").DataTable({
    dom: 'Bfrtip',
    buttons: [
        'copyHtml5',
        'excelHtml5',
        'csvHtml5',
        'pdfHtml5',
        'pageLength'
    ]
});

Application Testing

Now,

Open into browser.

URL: http://localhost/php-mysqli-datatable-export-buttons/index.php

You can see export buttons to download report into – CSV, PDF, Excel, etc

Download Complete Source Code –

Download Source Code

We hope this article helped you to learn PHP & MySQLi How To Add Export Buttons To DataTable Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.