In this tutorial “Export Data from MySQL in PHP Using Data Table” we are going to learn about how to display the data and extract data from database using DataTable.js. The grid view of the table is very important for web component in the modern website.
In every large table, we must need Sorting, searching, and pagination which is not an easy job in HTML tables. There are many grid views are available on the web, DataTable.js is one of the most popular files among them. So, here we will use PHP and MySQL to get data from server side. So we have to simply follow a few steps and will get a small quick example.
Table of Contents
Creating Our Database
Firstly, we have to create a database to store the data, for creating database follow these steps:
1. Open phpMyAdmin
2. Select the database icon and Create a new database with the name after that
3. Create the table with the table name
4. And then insert the details Or
5. After creating a database name, click the SQL and paste the following code
CREATE TABLE `user` ( `id` int(11) NOT NULL COMMENT 'primary key', `first_name` varchar(255) NOT NULL COMMENT 'employee name', `last_name` double NOT NULL COMMENT 'employee salary', `employee_age` int(11) NOT NULL COMMENT 'employee age' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';
Connection Page
Connection.php
This is a connection page, mysqli_connect() function opens connection to the MySQL server .
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employee";
$limit = 10;
$conn = mysqli_connect($servername, $username, $password, $dbname) or
die("Connection failed: " . mysqli_connect_error());
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>
Main Screen
This page will appear whenever a user opens the site, this page contains a simple web interface that displays the data records with the table. This page contains all record of data which is available in MySQL database and there is COPY, CSV, PDF, PRINT button through which user can easily copy all data and paste it, then the user can also extract the data in CSV and PDF format and also print the data using the print button.
Create a PHP file named “index.php” and paste the following code inside of it.
<?php require ('config.php'); ?>
<!DOCTYPE html>
<html>
<head>
<title>Techjunkgigs</title>
<link href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,400,400i,700" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<!-- Content Header (Page header) -->
<!-- Main content -->
<div class="col-md-12">
<!-- general form elements -->
<div class="card card-success">
<div class="card-header">
<h1 class="card-title">TechJunkGigs</h1>
<h2 class="card-title">Display, Copy, Print and extract data in Excel and PDF From MySQL Database Using PHP, jQuery and DataTable </h2>
</div>
<!-- /.card-header -->
<!-- form start -->
<div class="card-body">
<div class="row">
<div class="col-md-12">
<table id="table" class="table table-bordered table-hover">
<thead style="background:#A4D8F9;">
<tr>
<td>ID</td>
<td>First Name</td>
<td>Last Name</td>
<td>Age</td>
<td>Mobile</td>
</tr>
</thead>
<tbody>
<?php
$sql = $con->query('select * from user');
while($data = $sql->fetch_array()) {
echo '
<tr>
<td>'.$data['id'].'</td>
<td>'.$data['first_name'].'</td>
<td>'.$data['last_name'].'</td>
<td>'.$data['age'].'</td>
<td>'.$data['mobile'].'</td>
</tr>
';
}
?>
</tbody>
</table>
</div>
</div>
</div>
<!-- /.card-body -->
</div>
<!-- /.content -->
</div>
<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#table').DataTable({
dom: 'Bfrtip',
buttons: [ 'copy', 'csv', 'excel', 'pdf', 'print']
});
});
</script>
</body>
</html>
For accessing the DataTable Library You just have to include the below .js and .css file link in the script
<link href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,400,400i,700" rel="stylesheet"> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css"> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css"> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script> <script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script> <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script> <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js"></script> <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script> <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script> <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
For extracting data you need to have a button so for the button you have to include these script in your code
<script type="text/javascript">
$(document).ready(function() {
$('#table').DataTable({
dom: 'Bfrtip',
buttons: [ 'copy', 'csv', 'excel', 'pdf', 'print']
});
});
</script>
Image Demo
Table Without DataTable
Table With DataTable
You can also check
Display data from MySQL database using PHP, jQuery and DataTable
Ajax Live Data Search using PHP and MySQL
Insert Data into MySQL database with PHP and AJAX without refreshing page
I hope this article helped you to know “Export Data from MySQL in PHP Using Data Table”. To get the latest news and updates follow us on twitter & facebook, subscribe to our YouTube channel. And If you have any query then please let us know by using the comment form.