Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Ajax php mysql DataTables example that get data from mysql database using ajax, that calls a php method
<?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());
}
//mysqli_close($DBconnect);
--
-- 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);
<!DOCTYPE html>
<html lang="en">
<head>
<title>Datatable Demo1 | CoderExample</title>
<link href="//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"/>
<!-- scripts -->
<script src="assest/js/jquery-3.3.1.js"></script>
<script src="assest/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>
<script>
$(document).ready(function () {
var dataTable = $('#employee-grid').DataTable({
"responsive": true,
"processing": true,
"serverSide": true,
"ajax": {
url: "ajax/response.php", // json datasource
data: {action: 'getEMP2'},
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");
}
});
})
;
</script>
</head>
<body>
<div class="container">
<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>
</body>
</html>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Datatable Demo1 | CoderExample</title>
<link href="//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"/>
<!-- scripts -->
<script src="assest/js/jquery-3.3.1.js"></script>
<script src="assest/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>
<script>
$(document).ready(function () {
var dataTable = $('#employee-grid').DataTable({
"responsive": true,
"processing": true,
"serverSide": true,
"ajax": {
url: "ajax/response.php", // json datasource
data: {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");
}
});
})
;
</script>
</head>
<body><nav class="navbar navbar-inverse">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand" href="/jquery">Simple AJAX php MySql example page</a>
</div>
<ul class="nav navbar-nav">
<li class="active"><a href="#">Home</a></li>
<li><a href="serverSide.php">Server side example</a></li>
<li><a href="products.php">Products</a></li>
<li><a href="#">Page 3</a></li>
</ul>
</div>
</nav>
<div class="container">
<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>
</body>
</html>
<?php
include '../DB/configDB.php';
/* Database connection start */
// echo "test";
if (isset($_POST['action']) && !empty($_POST['action'])) {
$action = $_POST['action'];
switch ($action) {
case 'getEMP' :
getEMP($DBconnect);
break;
case 'getProd' :
getProducts($DBconnect);
break;
// ...etc...
}
}
//getEMP();
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
}
//getProducts($DBconnect);
function getProducts($DBconnect)
{
// echo "test";
/* Database connection end */
// storing request (ie, get/post) global array to a variable
$requestData = $_REQUEST;
$columns = array(
// datatable column index => database column name
0 => 'product_name',
1 => 'price',
2 => 'category'
);
// getting total number records without any search
$sql = "SELECT product_name, price, category ";
$sql .= " FROM products";
$query = mysqli_query($DBconnect, $sql) or die("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 product_name, price, category ";
$sql .= " FROM products WHERE 1=1";
if (!empty($requestData['search']['value'])) { // if there is a search parameter, $requestData['search']['value'] contains search parameter
$sql .= " AND ( product_name LIKE '" . $requestData['search']['value'] . "%' ";
$sql .= " OR price LIKE '" . $requestData['search']['value'] . "%' ";
$sql .= " OR category LIKE '" . $requestData['search']['value'] . "%' )";
}
$query = mysqli_query($DBconnect, $sql) or die("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 Error in getting : get products");
$data = array();
while ($row = mysqli_fetch_array($query)) { // preparing an array
$nestedData = array();
$nestedData[] = $row["product_name"];
$nestedData[] = $row["price"];
$nestedData[] = $row["category"];
$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
}
@sumonst21
Copy link

is the connection getting closed after using it keeps connected as sleep? I used your codes and found huge connection keeps on sleep.

@ShaneCunn
Copy link
Author

HI Sumont21,

you need to close the connection, I left that commented out
just add the end of the configDB.php:

mysqli_close($DBconnect);

@TeemoCell
Copy link

products.html > line > 54
index.html > line > 43

tag isn't used properly

@ShaneCunn
Copy link
Author

Thanks TeemoCell, I've tided and fixed the error in products and index

@sumonst21
Copy link

Hello @TeemoCell Thanks for the response. I figured that out that time. :)

@TeemoCell
Copy link

No problem, I'm glad that I could help.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment