This PHP tutorial will help you create jquery Datatable 1.10.16 using a Bootstrap 3.3.7. with pagination. The tutorial is broken into 8 steps.
We will use the following structure for the datatable:
- index.php
- configDB.php
- response.php
Step 1: Create a Bootstrap Html page called index.php and include the scripts and CSS below
CSS
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet"/>
<link href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap.min.css" rel="stylesheet"/>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"
integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.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/1.10.16/js/dataTables.bootstrap.min.js"></script>Step 2: Add the Html below to inside the body tags
<div class="container">
<div class
<div class="header"><h1>Server side DataTable demo in Php,Mysql and Ajax </h1></div>
<div class="container">
<table id="employee-grid" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>Employee name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>
</table>
</div>
</div>Step 3: Instantiate the datatable object in index.php
$(document).ready(function () {
var dataTable = $('#employee-grid').DataTable({
"responsive": true,
"processing": true,
"serverSide": true,
"ajax": {
url: "ajax/response.php", // json datasource
data: {action: 'getEMP'}, // Set the POST variable array and adds action: getEMP
type: 'post', // method , by default get
},
error: function () { // error handling
$(".employee-grid-error").html("");
$("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
$("#employee-grid_processing").css("display", "none");
}
});
});Step 4: Create a database connection file called config.php
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'username');
define('DB_PASSWORD', 'passwordForDB');
define('DB_NAME', 'DatabaseName');
$DBconnect = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
//echo "Connect Successfully. Host info: " . mysqli_get_host_info($DBconnect);
if (!$DBconnect) {
die("Connection failed: " . mysqli_connect_error());
}Step 5: For this example, you will need to load data from the MySQL database, so we need to create a database table to store and retrieve data.
--
-- Table structure for table `employee`
--
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT 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',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=64 ;
--
-- Dumping data for table `employee`
--
INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
(1, 'Tiger Nixon', 320800, 61),
(2, 'Garrett Winters', 170750, 63),
(3, 'Ashton Cox', 86000, 66),
(4, 'Cedric Kelly', 433060, 22),
(5, 'Airi Satou', 162700, 33),
(6, 'Brielle Williamson', 372000, 61),
(7, 'Herrod Chandler', 137500, 59),
(8, 'Rhona Davidson', 327900, 55),
(9, 'Colleen Hurst', 205500, 39),
(10, 'Sonya Frost', 103600, 23),
(11, 'Jena Gaines', 90560, 30),
(12, 'Quinn Flynn', 342000, 22),
(13, 'Charde Marshall', 470600, 36),
(14, 'Haley Kennedy', 313500, 43),
(15, 'Tatyana Fitzpatrick', 385750, 19),
(16, 'Michael Silva', 198500, 66),
(17, 'Paul Byrd', 725000, 64),
(18, 'Gloria Little', 237500, 59),
(19, 'Bradley Greer', 132000, 41),
(20, 'Dai Rios', 217500, 35),
(21, 'Jenette Caldwell', 345000, 30),
(22, 'Yuri Berry', 675000, 40),
(23, 'Caesar Vance', 106450, 21),
(24, 'Doris Wilder', 85600, 23),
(25, 'Angelica Ramos', 1200000, 47),
(26, 'Gavin Joyce', 92575, 42),
(27, 'Jennifer Chang', 357650, 28),
(28, 'Brenden Wagner', 206850, 28),
(29, 'Fiona Green', 850000, 48),
(30, 'Shou Itou', 163000, 20),
(31, 'Michelle House', 95400, 37),
(32, 'Suki Burks', 114500, 53),
(33, 'Prescott Bartlett', 145000, 27),
(34, 'Gavin Cortez', 235500, 22),
(35, 'Martena Mccray', 324050, 46),
(36, 'Unity Butler', 85675, 47),
(37, 'Howard Hatfield', 164500, 51),
(38, 'Hope Fuentes', 109850, 41),
(39, 'Vivian Harrell', 452500, 62),
(40, 'Timothy Mooney', 136200, 37),
(41, 'Jackson Bradshaw', 645750, 65),
(42, 'Olivia Liang', 234500, 64),
(43, 'Bruno Nash', 163500, 38),
(44, 'Sakura Yamamoto', 139575, 37),
(45, 'Thor Walton', 98540, 61),
(46, 'Finn Camacho', 87500, 47),
(47, 'Serge Baldwin', 138575, 64),
(48, 'Zenaida Frank', 125250, 63),
(49, 'Zorita Serrano', 115000, 56),
(50, 'Jennifer Acosta', 75650, 43),
(51, 'Cara Stevens', 145600, 46),
(52, 'Hermione Butler', 356250, 47),
(53, 'Lael Greer', 103500, 21),
(54, 'Jonas Alexander', 86500, 30),
(55, 'Shad Decker', 183000, 51),
(56, 'Michael Bruce', 183000, 29),
(57, 'Donna Snider', 112000, 27);Step 6: Create the response.php and include the connection at the top of the file
if (isset($_POST['action']) && !empty($_POST['action'])) {
$action = $_POST['action'];
switch ($action) {
case 'getEMP' :
getEMP($DBconnect);
break;
case 'getProd' :
getProducts($DBconnect);
break;
// ...etc...
}
}Step 8: Create the function getEMP and pass in the $DBconnect object.
function getEMP($DBconnect)
{
// storing request (ie, get/post) global array to a variable
$requestData = $_REQUEST;
$columns = array(
// datatable column index => database column name
0 => 'employee_name',
1 => 'employee_salary',
2 => 'employee_age'
);
// getting total number records without any search
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql .= " FROM employee";
$query = mysqli_query($DBconnect, $sql) or die("Mysql Mysql Error in getting : get products");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql .= " FROM employee WHERE 1=1";
if (!empty($requestData['search']['value'])) { // if there is a search parameter, $requestData['search']['value'] contains search parameter
$sql .= " AND ( employee_name LIKE '" . $requestData['search']['value'] . "%' ";
$sql .= " OR employee_salary LIKE '" . $requestData['search']['value'] . "%' ";
$sql .= " OR employee_age LIKE '" . $requestData['search']['value'] . "%' )";
}
$query = mysqli_query($DBconnect, $sql) or die("Mysql Mysql Error in getting : get products");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
$sql .= " ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length. */
$query = mysqli_query($DBconnect, $sql) or die("Mysql Mysql Error in getting : get products");
$data = array();
while ($row = mysqli_fetch_array($query)) { // preparing an array
$nestedData = array();
$nestedData[] = $row["employee_name"];
$nestedData[] = $row["employee_salary"];
$nestedData[] = $row["employee_age"];
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval($totalData), // total number of records
"recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
}Links to the Demo and Source code below.