In this tutorial “Display data from MySQL database using PHP, jQuery and DataTable” we are going to learn about how to display the 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 few steps and will get a small quick example.
DataTables helps to enhance HTML tables with the ability to filter and sort. It also provides a comprehensive API. It provides a set of configuration options, allowing us to consume data from virtually any data source.
DataTables.js file is an open source, highly flexible, light weighted and also customizable features like an inline editor, AutoFill, sticky header, responsive, Supports bootstrap and foundation.
Table of Contents
Creating Our Database
Firstly, we have to create a database to store the data, for creating database follow these steps:
- Open phpMyAdmin
- Select database icon and Create a new database with the name after that
- Create the table with the table name
- And then insert the details Or
- After creating a database name, click the SQL and paste the following code
CREATE TABLE `employee` ( `id` int(11) NOT NULL COMMENT 'primary key', `employee_name` varchar(255) NOT NULL COMMENT 'employee name', `employee_salary` 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.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 simple web interface that displays the data records with the table. This page contains all record of data which is available in MySQL database.
Create a PHP file named “index.php” and paste the following code inside of it
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>TechJunkGigs</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"
integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u"
crossorigin="anonymous">
<link rel="stylesheet" href="css/dataTables.bootstrap.min.css">
</head>
<body>
<div class="container" style="margin-top: 20px">
<h1>TechJunkGigs</h1>
<hr></hr>
<div class="row">
<div class="col-md-12">
<table class="table table-bordered table-hover">
<thead>
<tr>
<td>ID</td>
<td>Employee Name</td>
<td>Salary</td>
<td>Age</td>
</tr>
</thead>
<tbody>
<?php
include ('connection.php');
$sql = $conn->query('SELECT * FROM employee');
while($data = $sql->fetch_array()) {
echo '
<tr>
<td>'.$data['id'].'</td>
<td>'.$data['employee_name'].'</td>
<td>'.$data['employee_salary'].'</td>
<td>'.$data['employee_age'].'</td>
</tr>
';
}
?>
</tbody>
</table>
</div>
</div>
</div>
<script
src="http://code.jquery.com/jquery-3.2.1.min.js"
integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4="
crossorigin="anonymous"></script>
<script type="text/javascript" src="js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$(".table").DataTable({
});
});
</script>
</body>
</html>
We need to include online js file of DataTable on our page. we can also get these links from DataTable website
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"
integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u"
crossorigin="anonymous">
<script type="text/javascript"> $(document).ready(function() { $(".table").DataTable({ }); }); </script>
We also have to download a dataTables.bootstrap.min.css, jquery.dataTables.min.js, and dataTables.bootstrap.min.js from jQuery. and include the file in index.php page.
You just need to download the highlighted js file.
Demo Image
This is the normal screen without DataTable js file.
This is the screen with DataTable js file.
You can also check
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 “Display data from MySQL database using PHP, jQuery and DataTable”. 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.