jQuery provides a very powerful feature which is called datatable. The datatable is an open-source plugin provided by the jQuery. Basically, in a bootstrap table or HTML table, there is no option to sort the data according to the column. No search option and no other options. Simply the table is used to display the data in the form of rows and columns. But, if you want to make your table to more flexible, attractive and the featured then this can be done using the jQuery datatable plugin. So, today, I’m here with a tutorial on the datatable implementation in PHP.
Datatable in PHP
Mainly, the datatable provides various features which are not available in any other tables. It is an open-source library by the jQuery. It is mobile friendly and provides pagination, data sorting, searching, column order, etc. You can apply this jquery plugin on the bootstrap table which must have some records.
Ajax PHP Form Handling Using jQuery – Submit Form Without Refresh
So, here, I’m going to create a project in which I will retrieve data from the database table. I am going to connect my application from the MySQL database. I have some records that I will fetch in a table.
Create Database
Open the phpMyAdmin and under the SQL section enter the below code for creating the database.
CREATE DATABASE data_table;Next, you will have to create a table. Here, I have created a table with the name products
Create Table
CREATE TABLE `products` (
`product_id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) NOT NULL,
`sku` varchar(50) NOT NULL,
`brand` varchar(50) NOT NULL,
`quantity` int(11) NOT NULL,
`price_per_unit` varchar(50) NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Dropdown Selection Filter in PHP Using jQuery Ajax
Dumping Data For Products Table
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`product_id`, `product_name`, `sku`, `brand`, `quantity`, `price_per_unit`, `created_on`, `updated_on`) VALUES
(1, 'Black Tea', 'BT101', 'Red Label', 43, '430', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(2, 'Green Tea', 'BT102', 'Tata Tea', 54, '200', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(3, 'Fruit Tea', 'BT103', 'Tata Tea', 25, '250', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(4, 'Herbal Tea', 'BT104', 'Red Label', 36, '350', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(5, 'White Tea', 'BT105', 'Tata Tea', 17, '453', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(6, 'Oolong Tea', 'BT106', 'Tata Tea', 28, '640', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(7, 'Rooibos Tea', 'BT107', 'Tata Tea', 18, '450', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(8, 'Mad Angles', 'BT109', 'Bingo', 15, '207', '2019-09-08 20:19:25', '2019-09-08 20:19:25'),
(9, 'Black Tea', 'BT101', 'Red Label', 43, '430', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(10, 'Green Tea', 'BT102', 'Tata Tea', 54, '200', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(11, 'Fruit Tea', 'BT103', 'Tata Tea', 25, '250', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(12, 'Herbal Tea', 'BT104', 'Red Label', 36, '350', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(13, 'White Tea', 'BT105', 'Tata Tea', 17, '453', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(14, 'Oolong Tea', 'BT106', 'Tata Tea', 28, '640', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(15, 'Rooibos Tea', 'BT107', 'Tata Tea', 18, '450', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),
(16, 'Mad Angles', 'BT109', 'Bingo', 15, '207', '2019-09-08 20:33:40', '2019-09-08 20:33:40');
Create Database Connection
Before moving to the data part, you will have to create a database connection. Create a new file with the name db-config.php then paste the below code. Now, as per your database name, username, and password don’t forget to replace the credentials.
// db-config.php
<?php
class DBController {
private $hostname = "localhost";
private $username = "root";
private $password = "root";
private $db = "data_table";
//create connection
public function connect() {
$conn = new mysqli($this->hostname, $this->username, $this->password, $this->db)or die("Database connection error." . $conn->connect_error);
return $conn;
}
// close connection
public function close($conn) {
$conn->close();
}
}
?>PHP Import CSV File Data into MySQL Database with Preview
Create a Class File
I am going to follow the object-oriented principle for retrieving the data from the database table. Now, create a new file in your project directory in which, we will be writing the database query.
// product-controller.php
<?php
class ProductsController {
// constructor
function __construct($conn) {
$this->conn = $conn;
}
// retrieving products data
public function index() {
$data = array();
$sql = "SELECT * FROM products";
$result = $this->conn->query($sql);
if($result->num_rows > 0) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
$db->close($conn);
return $data;
}
}
?>Now, create a new file named index.php in which we, will create a bootstrap table. In this table, we’ll display the records which are retrieving through the above class file (product-controller.php).
// index.php
<?php
require_once './config/db-config.php';
require_once './controller/product-controller.php';
$db = new DBController();
$conn = $db->connect();
$dCtrl = new ProductsController($conn);
$products = $dCtrl->index();
?>
<!DOCTYPE html>
<html>
<head>
<title>Datatable Implementation in PHP</title>
<!-- Bootstrap 4 CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-xl-12 col-lg-12 col-md-12 col-12 m-auto">
<table class="table table-bordered table-hovered table-striped" id="productTable">
<thead>
<th> Product ID </th>
<th> Product Name </th>
<th> SKU </th>
<th> Brand </th>
<th> Quantity </th>
<th> Price/Unit </th>
</thead>
<tbody>
<?php
foreach($products as $product) : ?>
<tr>
<td> <?php echo $product['product_id']; ?> </td>
<td> <?php echo $product['product_name']; ?> </td>
<td> <?php echo $product['sku']; ?> </td>
<td> <?php echo $product['brand']; ?> </td>
<td> <?php echo $product['quantity']; ?> </td>
<td> <?php echo $product['price_per_unit']; ?> </td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
</body>
</html>When you will execute the above code, it will show you the result as below. Here, the data are showing in a single page no matter how much data you have in the database table. It will print all the data on a single page.
This would be a cause for the slow speed of the webpage if you will have a large number of data in your database. That’s why to overcome this, we use pagination which breaks the data into several pages.
PHP File Upload Using jQuery and Ajax
Implement DataTable PHP
Now, on the above table, I’m going to implement the datatable PHP. So first of all download the minified version of CSS and jQuery file from the official website https://datatables.net. You can use CDN too. I am going to use CDN here.
<!-- CDN CSS Datatable -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
<!-- CDN jQuery Datatable -->
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
- Add the CSS between the opening and closing head tag of HTML.
- Also, add the jQuery CDN at the bottom before the end of the body tag.
- In the last step, add these two lines of script to apply the datatable in the HTML table.
// apply datatable in the HTML table
<script>
$(document).ready(function() {
$('#productTable').DataTable();
});
</script>In the above code, I have started a script code with jQuery in which I have assigned the datatable in the HTML table with the id attribute.
So, after adding the above script and CDN the index.php the file will become as shown below.
<?php
require_once './config/db-config.php';
require_once './controller/product-controller.php';
$db = new DBController();
$conn = $db->connect();
$dCtrl = new ProductsController($conn);
$products = $dCtrl->index();
?>
<!DOCTYPE html>
<html>
<head>
<title>Datatable Implementation in PHP</title>
<!-- Bootstrap 4 CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-xl-12 col-lg-12 col-md-12 col-12 m-auto">
<table class="table table-bordered table-hovered table-striped" id="productTable">
<thead>
<th> Product ID </th>
<th> Product Name </th>
<th> SKU </th>
<th> Brand </th>
<th> Quantity </th>
<th> Price/Unit </th>
</thead>
<tbody>
<?php
foreach($products as $product) : ?>
<tr>
<td> <?php echo $product['product_id']; ?> </td>
<td> <?php echo $product['product_name']; ?> </td>
<td> <?php echo $product['sku']; ?> </td>
<td> <?php echo $product['brand']; ?> </td>
<td> <?php echo $product['quantity']; ?> </td>
<td> <?php echo $product['price_per_unit']; ?> </td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<!-- CDN jQuery Datatable -->
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>
</body>
</html>
<!-- Script --->
<script>
$(document).ready(function() {
$('#productTable').DataTable();
});
</script>Now, after executing the above code, you can see the datatable has been applied to our existing table.
In the result of datatable, you will have the various features like pagination, the number of records to show, search specific record, the total number of records showing in the table, sorting the data in ascending and descending order.
Check If Email Available in PHP Using Ajax and jQuery
Conclusion
We have successfully implemented the jQuery datatable in PHP. After applying the data table we got the various functions in the normal table. I hope, this will help you to manage the tables having a large number of records. If you need any help regarding this tutorial then please ask by doing comment in the comment section. I will help you there.
Hello! I have a question, What if I already have a Record set and I wanna present this object in a table like the use in this example. I think I had to do a change but, where?? What will be this change? thanks, and sorry my bad english
You can map the data set into the table cell through loop as shown in this demo.
thanks a lot!