Hi! In this tutorial let's look at the server-side processing of jquery datatables using php, mysql and ajax. In case you don't know, Datatables is an amazing jquery plugin that converts the simple html table into a feature-rich data grid with additional functions like instant search, pagination, multi-column sorting etc. The table works with multiple data sources like DOM, AJAX etc., and supports both client and server side processing. We have already seen about datatables with json data and here we will see about server side processing.
For the server script, we are going to use PHP and MySQL as a data source.
jQuery Datatables Server-side Processing with PHP and MySQL:
Let's see how to fetch records from the server-side using ajax request and list it in the data tables. To use in this example, we need a dummy database. So let's create it first.
Step 1) Create MySQL Database
The following sql will create a mysql database, a table and some sample records in it. Run this script on phpmyadmin to create the database.
CREATE DATABASE `my_demo`;
USE `my_demo`;
CREATE TABLE `customers` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`email` varchar(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
INSERT INTO `customers` (`id`, `name`, `email`) VALUES
(1, 'Jim Connor', 'jimconnor@yahoo.com'),
(2, 'Mark Higgins', 'mark.higgins21@yahoo.com'),
(3, 'Austin Joseph', 'austin.joseph.boston@gmail.com'),
(4, 'Sean Kennedy', 'seankennedy01@gmail.com'),
(5, 'Rose Harris', 'roseharris@gmail.com'),
(6, 'Lilly Whites', 'lillywhites@outlook.com'),
(7, 'Jennifer Winters', 'jennie.winters001@gmail.com'),
(8, 'Michael Bruce', 'michaelbruce78@yahoo.com'),
(9, 'John Alex', 'johnalex@example.com'),
(10, 'Demi Milan', 'demimilan@gmail.com'),
(11, 'Austin Joseph', 'austin.joseph.boston@gmail.com'),
(12, 'Mark Higgins', 'mark.higgins21@yahoo.com'),
(13, 'Sean Kennedy', 'seankennedy.boss@outlook.com');
Step 2) Load the Required CSS and JS Libraries
Next load the CSS and JS files of the datatables plug-in. And you must also include the 'jquery.js' before loading 'jquery.datatables.js' since it is dependent on jquery.
Please note that I have loaded the datatables files from the cdn but you can download and use it from your own server though.
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js" type="text/javascript"></script>
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js" charset="utf8" type="text/javascript"></script>
Step 3) Create HTML Table
Then create html markup for the table. This will act as a placeholder for data table. Just add the appropriate column headers for the table.
<table id="customersTable" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
</table>
Step 4) AJAX Call
Next, make ajax request to the php script to get the data from the server-side. You must also map the table columns with the fields in the database to populate the html table.
The method dataTable() will initialize the datatables and comes with various options. By setting different parameters we can control the way it behaves.
$(document).ready(function() {
$('#customersTable').dataTable({
"processing": true,
"ajax": "fetch_data.php",
"columns": [
{data: 'id'},
{data: 'name'},
{data: 'email'}
]
});
});
Complete index.html File
<!DOCTYPE html>
<html>
<head>
<title>Datatables Example using PHP and MySQL</title>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.css">
</head>
<body>
<h2 style="text-align:center;">Datatables Server-Side Example</h2>
<table id="customersTable" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
</table>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js" type="text/javascript"></script>
<script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js" charset="utf8" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#customersTable').dataTable({
"processing": true,
"ajax": "fetch_data.php",
"columns": [
{data: 'id'},
{data: 'name'},
{data: 'email'}
]
});
});
</script>
</body>
</html>
Step 5) Fetch Records from Database and Return as JSON
Finally the server script. This will communicate with the backend mysql database, retrieve records, encode it to json along with other necessary values and send it back to the front-end.
fetch_records.php
<?php
// db settings
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'my_demo';
// db connection
$con = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($con));
// fetch records
$sql = "select id, name, email from customers";
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($result)) {
$array[] = $row;
}
$dataset = array(
"echo" => 1,
"totalrecords" => count($array),
"totaldisplayrecords" => count($array),
"data" => $array
);
echo json_encode($dataset);
?>
We have all the code in place. Now run the index.html and you can see data grid like this,
You can filter the records using the instant search box at the top this way,
- Autocomplete Textbox from Database in PHP, jQuery and MySQL
- Dynamically Add/Remove Textbox using jQuery, PHP & Bootstrap
That explains about displaying jquery datatables with database records using php and mysql. Although datatables works fairly well with client data sources such as json, JS Array etc., when you want to work with a huge data-set, going with server side processing is the best route. I hope this tutorial is useful for you. Please, share it in your social circle if you like it.
