How to add DataTable Export Buttons

Tech Area
April 9, 2024

In this post, We will discuss how to add export buttons in jQuery DataTable in PHP and MySQL. If you want to show fetch data from database and show in table and want to export data, then you can achieve this task using jQuery DataTable export buttons.

Files used in this tutorial:

1- connection.php (database connection file)

2- index.php (fetch data from database and add export buttons)

Below are the step by step process of how to add export buttons in jQuery DataTable using PHP and MySQL.

Step 1: Create a Database connection

In this step, create a new file connection.php to create database connection.

connection.php

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "college_db";
$connection = mysqli_connect("$server","$username","$password");
$select_db = mysqli_select_db($connection, $database);
if(!$select_db)
{
	echo("connection terminated");
}
?>

Step 2: Create a file for fetch data and initialise DataTable export buttons

Now create a new file index.php This is the main file used for fetch data from database. First, We will create an HTML Table.

index.php

First of all include necessary CSS and JavaScript library.

<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/2.0.3/css/dataTables.dataTables.min.css">
<link href="https://cdn.datatables.net/v/bs5/jszip-3.10.1/dt-2.0.3/b-3.0.1/b-html5-3.0.1/b-print-3.0.1/datatables.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="//cdn.datatables.net/2.0.3/js/dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/v/bs5/jszip-3.10.1/dt-2.0.3/b-3.0.1/b-html5-3.0.1/b-print-3.0.1/datatables.min.js"></script>

Now create body for Table.

<body>
<table id="myTable">
<thead>
  <tr>
    <th>Name</th>
    <th>Course</th>
    <th>Country</th>
  </tr>
</thead>
<tbody>
<?php
include('connection.php');
$select_query = mysqli_query($connection,"select * from tbl_student");
while($data = mysqli_fetch_array($select_query)){
?>
  <tr>
    <td><?php echo $data['name']; ?></td>
    <td><?php echo $data['course']; ?></td>
    <td><?php echo $data['country']; ?></td>
  </tr>
<?php } ?>
</tbody>
</table>
</body>

After this, create script for initialise datatable export buttons.

<script>
$(document).ready(function(){
  $('#myTable').DataTable({
    dom: 'Bfrtip',
    buttons: ['copy','excel','csv','print','pdf']
  });
});
</script>

Source Code

Here is the full code that we have written for index.php.

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>jQuery DataTables</title>
  <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/2.0.3/css/dataTables.dataTables.min.css">
  <link href="https://cdn.datatables.net/v/bs5/jszip-3.10.1/dt-2.0.3/b-3.0.1/b-html5-3.0.1/b-print-3.0.1/datatables.min.css" rel="stylesheet">
<style>
table {
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 100%;
}

td, th {
  border: 1px solid #dddddd;
  text-align: left;
  padding: 8px;
}

tr:nth-child(even) {
  background-color: #dddddd;
}
</style>
</head>
<body>
<table id="myTable">
<thead>
  <tr>
    <th>Name</th>
    <th>Course</th>
    <th>Country</th>
  </tr>
</thead>
<tbody>
<?php
include('connection.php');
$select_query = mysqli_query($connection,"select * from tbl_student");
while($data = mysqli_fetch_array($select_query)){
?>
  <tr>
    <td><?php echo $data['name']; ?></td>
    <td><?php echo $data['course']; ?></td>
    <td><?php echo $data['country']; ?></td>
  </tr>
<?php } ?>
</tbody>
</table>
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="//cdn.datatables.net/2.0.3/js/dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/v/bs5/jszip-3.10.1/dt-2.0.3/b-3.0.1/b-html5-3.0.1/b-print-3.0.1/datatables.min.js"></script>
<script>
$(document).ready(function(){
  $('#myTable').DataTable({
    dom: 'Bfrtip',
    buttons: ['copy','excel','csv','print','pdf']
  });
});
</script>
</body>
</html>

Output