What is DataTable server side processing?
Do you want to integrate DataTable server side processing in your website and projects? DataTable add advanced controls to your HTML tables. DataTable server side processing nowadays a very popular for listing of records in the projects. It is an open source and easy to use.
DataTable is a jQuery library used to show the total list of records in an HTML table. DataTables gives both client-side and server-side processing. It combines some features like key search, pagination, and data sorting and filtering. In this article, we show you how to apply DataTable server side processing using PHP and MySQL.
For our tutorial, we will create a table in the database and display table records in the DataTable using server side processing script. The final output will look like as shown below.
To apply DataTable server side processing. I am doing following Steps.
STEPS
We need to follow the following steps below.
Create directory.
Example image for directory.
It is a good practice to create the required folders on your computer so you can proceed and mange it easily.
- CSS folder for custom styling of HTML structure.
- Js folder for DataTable and jQuery libraries and (javascript.js)for writing the scripts.
- Includes has the database connection file with name config.php.
Once you have created the required folders it is time to create your HTML structure and writing the DataTable server side processing script.
- Launch your text editor and create a new file and save it as "index.php" in your main folder.
- Create a new file and save it as "style.css" in your CSS folder.
- Create a new file and save it as "config.php" in your include folder.
- Create a one more new file and save it as "server_processing.php" in your main folder where you have save index.php file.
Create Database and a Table with name employee.
Open your phpMyAdmin and create a database with name (school) and create a table (employee) in it using the below query.
CREATE TABLE `employee` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`emp_name` varchar(80) NOT NULL,
`salary` varchar(20) NOT NULL,
`gender` varchar(10) NOT NULL,
`city` varchar(80) NOT NULL,
`email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
We have created a simple table in database with the columns like id,emp_name, salary, and gender etc. After creating the table in database you need to add some dummy entries in it.
Create Database connection.
Open your config.php file that you have save in includes folder and write a below code for database connection and save it.
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "school"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
Make sure to replace the placeholders with the actual values.
In order to integrate DataTable server side processing into our page. We have to use the below minified version of libraries you can include these libraries with the usual scripts and links element as you would do for any other jquery and css libraries:
<!-- Datatable CSS -->
<link href='css/datatables.min.css' rel='stylesheet' type='text/css'>
<!-- Datatable custom CSS -->
<link href='css/style.css' rel='stylesheet' type='text/css'>
<!-- jQuery Library -->
<script src="js/jquery-3.3.1.min.js"></script>
<!-- Datatable JS -->
<script src="js/datatables.min.js"></script>
<!-- JS script to initialize the server side processing -->
<script src="js/javascript.js"></script>
You can download the above libraries by clicking on below link.
- Download Datatables from here Click on me to Download .
- Include (datatables.min.css) and (datatables.min.js ) in <head> section and also include the jQuery Library.
- You can also use CDN.
Now we are going to create HTML table structure in our index.php file by adding columns name in <thead> section.
<div class="para">
<!-- Table -->
<table id='datatable1' class='display dataTable'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
</tr>
</thead>
</table>
</div>
Here is the complete code after adding the libraries and HTML table structure together.
<html>
<head>
<title>How to Integrate Datatable Server Side Processing Using PHP and MySQL </title>
<!-- Datatable CSS -->
<link href='css/datatables.min.css' rel='stylesheet' type='text/css'>
<!-- Datatable custom CSS -->
<link href='css/style.css' rel='stylesheet' type='text/css'>
<!-- jQuery Library -->
<script src="js/jquery-3.3.1.min.js"></script>
<!-- Datatable JS -->
<script src="js/datatables.min.js"></script>
<!-- server side JS script -->
<script src="js/javascript.js"></script>
</head>
<body>
<div >
<div class="para">
<!-- Table -->
<table id='datatable1' class='display dataTable'>
<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
Open your javascript.php file from js folder and write a below code to initialize the DataTable.
$(document).ready(function(){
$('#datatable1').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'server_processing.php'
},
'columns': [
{ data: 'emp_name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'salary' },
{ data: 'city' },
]
});
});
Now your table look like as shown below.
Now open your (server_processing.php) file and write the below code for server side processing.
<?php
include 'includes/config.php';
## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
## Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " and (emp_name like '%".$searchValue."%' or
email like '%".$searchValue."%' or
city like'%".$searchValue."%' ) ";
}
## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
"emp_name"=>$row['emp_name'],
"email"=>$row['email'],
"gender"=>$row['gender'],
"salary"=>$row['salary'],
"city"=>$row['city']
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
Make sure that field names should be the same in (server_processing.php) response data as defined in columns data during DataTable initialization in (javascript.js) otherwise field value not be read.
Other Related Posts
How to Import CSV File data into MySQL Database
How to Import Excel File Data into MySQL Database
How to integrate DataTable in HTML Table with Example
We have all done here is the final output after applying DataTable server side processing using PHP and MySQL.
Final Output Image.
We hope you may learn how to apply DataTable server side processing using PHP and MySQL. Please share your thoughts and queries in the contact section.
Below is the Download link of above tutorial.
Click here to download full source code