overview:
Hello, today we will talk about how to use the server side in
the Datatables using PHP and MYSQL in simple steps.
files structure:
datatables.sql: import data to mysql database .
index.php: main page .
server_side.php: The main code to handle Datatables server-side.
ssp.class.php: library for dealing with databases compatible with Datatables server side.
Step1 : import my data to database.
datatables.sql:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
-- --------------------------------------------------------
--
-- بنية الجدول `datatables_demo`
--
CREATE TABLE `datatables_demo` (
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- إرجاع أو استيراد بيانات الجدول `datatables_demo`
--
INSERT INTO `datatables_demo` (`firstname`, `lastname`, `email`, `id`) VALUES
('firstname0', 'lastname0', 'email0@gmail.com', 1),
('firstname1', 'lastname1', 'email1@gmail.com', 2),
('firstname2', 'lastname2', 'email2@gmail.com', 3),
('firstname3', 'lastname3', 'email3@gmail.com', 4),
('firstname4', 'lastname4', 'email4@gmail.com', 5),
('firstname5', 'lastname5', 'email5@gmail.com', 6),
('firstname6', 'lastname6', 'email6@gmail.com', 7),
('firstname7', 'lastname7', 'email7@gmail.com', 8),
('firstname8', 'lastname8', 'email8@gmail.com', 9),
('firstname9', 'lastname9', 'email9@gmail.com', 10),
('firstname10', 'lastname10', 'email10@gmail.com', 11),
('firstname11', 'lastname11', 'email11@gmail.com', 12),
('firstname12', 'lastname12', 'email12@gmail.com', 13),
('firstname13', 'lastname13', 'email13@gmail.com', 14),
('firstname14', 'lastname14', 'email14@gmail.com', 15),
('firstname15', 'lastname15', 'email15@gmail.com', 16),
('firstname16', 'lastname16', 'email16@gmail.com', 17),
('firstname17', 'lastname17', 'email17@gmail.com', 18),
('firstname18', 'lastname18', 'email18@gmail.com', 19),
('firstname19', 'lastname19', 'email19@gmail.com', 20),
('firstname20', 'lastname20', 'email20@gmail.com', 21),
('firstname21', 'lastname21', 'email21@gmail.com', 22),
('firstname22', 'lastname22', 'email22@gmail.com', 23),
('firstname23', 'lastname23', 'email23@gmail.com', 24),
('firstname24', 'lastname24', 'email24@gmail.com', 25),
('firstname25', 'lastname25', 'email25@gmail.com', 26),
('firstname26', 'lastname26', 'email26@gmail.com', 27),
('firstname27', 'lastname27', 'email27@gmail.com', 28),
('firstname28', 'lastname28', 'email28@gmail.com', 29),
('firstname29', 'lastname29', 'email29@gmail.com', 30),
('firstname30', 'lastname30', 'email30@gmail.com', 31),
('firstname31', 'lastname31', 'email31@gmail.com', 32),
('firstname32', 'lastname32', 'email32@gmail.com', 33),
('firstname33', 'lastname33', 'email33@gmail.com', 34),
('firstname34', 'lastname34', 'email34@gmail.com', 35),
('firstname35', 'lastname35', 'email35@gmail.com', 36),
('firstname36', 'lastname36', 'email36@gmail.com', 37),
('firstname37', 'lastname37', 'email37@gmail.com', 38),
('firstname38', 'lastname38', 'email38@gmail.com', 39),
('firstname39', 'lastname39', 'email39@gmail.com', 40),
('firstname40', 'lastname40', 'email40@gmail.com', 41),
('firstname41', 'lastname41', 'email41@gmail.com', 42),
('firstname42', 'lastname42', 'email42@gmail.com', 43),
('firstname43', 'lastname43', 'email43@gmail.com', 44),
('firstname44', 'lastname44', 'email44@gmail.com', 45),
('firstname45', 'lastname45', 'email45@gmail.com', 46),
('firstname46', 'lastname46', 'email46@gmail.com', 47),
('firstname47', 'lastname47', 'email47@gmail.com', 48),
('firstname48', 'lastname48', 'email48@gmail.com', 49),
('firstname49', 'lastname49', 'email49@gmail.com', 50),
('firstname50', 'lastname50', 'email50@gmail.com', 51),
('firstname51', 'lastname51', 'email51@gmail.com', 52),
('firstname52', 'lastname52', 'email52@gmail.com', 53),
('firstname53', 'lastname53', 'email53@gmail.com', 54),
('firstname54', 'lastname54', 'email54@gmail.com', 55),
('firstname55', 'lastname55', 'email55@gmail.com', 56),
('firstname56', 'lastname56', 'email56@gmail.com', 57),
('firstname57', 'lastname57', 'email57@gmail.com', 58),
('firstname58', 'lastname58', 'email58@gmail.com', 59),
('firstname59', 'lastname59', 'email59@gmail.com', 60),
('firstname60', 'lastname60', 'email60@gmail.com', 61),
('firstname61', 'lastname61', 'email61@gmail.com', 62),
('firstname62', 'lastname62', 'email62@gmail.com', 63),
('firstname63', 'lastname63', 'email63@gmail.com', 64),
('firstname64', 'lastname64', 'email64@gmail.com', 65),
('firstname65', 'lastname65', 'email65@gmail.com', 66),
('firstname66', 'lastname66', 'email66@gmail.com', 67),
('firstname67', 'lastname67', 'email67@gmail.com', 68),
('firstname68', 'lastname68', 'email68@gmail.com', 69),
('firstname69', 'lastname69', 'email69@gmail.com', 70),
('firstname70', 'lastname70', 'email70@gmail.com', 71),
('firstname71', 'lastname71', 'email71@gmail.com', 72),
('firstname72', 'lastname72', 'email72@gmail.com', 73),
('firstname73', 'lastname73', 'email73@gmail.com', 74),
('firstname74', 'lastname74', 'email74@gmail.com', 75),
('firstname75', 'lastname75', 'email75@gmail.com', 76),
('firstname76', 'lastname76', 'email76@gmail.com', 77),
('firstname77', 'lastname77', 'email77@gmail.com', 78),
('firstname78', 'lastname78', 'email78@gmail.com', 79),
('firstname79', 'lastname79', 'email79@gmail.com', 80),
('firstname80', 'lastname80', 'email80@gmail.com', 81),
('firstname81', 'lastname81', 'email81@gmail.com', 82),
('firstname82', 'lastname82', 'email82@gmail.com', 83),
('firstname83', 'lastname83', 'email83@gmail.com', 84),
('firstname84', 'lastname84', 'email84@gmail.com', 85),
('firstname85', 'lastname85', 'email85@gmail.com', 86),
('firstname86', 'lastname86', 'email86@gmail.com', 87),
('firstname87', 'lastname87', 'email87@gmail.com', 88),
('firstname88', 'lastname88', 'email88@gmail.com', 89),
('firstname89', 'lastname89', 'email89@gmail.com', 90),
('firstname90', 'lastname90', 'email90@gmail.com', 91),
('firstname91', 'lastname91', 'email91@gmail.com', 92),
('firstname92', 'lastname92', 'email92@gmail.com', 93),
('firstname93', 'lastname93', 'email93@gmail.com', 94),
('firstname94', 'lastname94', 'email94@gmail.com', 95),
('firstname95', 'lastname95', 'email95@gmail.com', 96),
('firstname96', 'lastname96', 'email96@gmail.com', 97),
('firstname97', 'lastname97', 'email97@gmail.com', 98),
('firstname98', 'lastname98', 'email98@gmail.com', 99),
('firstname99', 'lastname99', 'email99@gmail.com', 100),
('firstname100', 'lastname100', 'email100@gmail.com', 101),
('firstname101', 'lastname101', 'email101@gmail.com', 102),
('firstname102', 'lastname102', 'email102@gmail.com', 103),
('firstname103', 'lastname103', 'email103@gmail.com', 104),
('firstname104', 'lastname104', 'email104@gmail.com', 105),
('firstname105', 'lastname105', 'email105@gmail.com', 106),
('firstname106', 'lastname106', 'email106@gmail.com', 107),
('firstname107', 'lastname107', 'email107@gmail.com', 108),
('firstname108', 'lastname108', 'email108@gmail.com', 109),
('firstname109', 'lastname109', 'email109@gmail.com', 110),
('firstname110', 'lastname110', 'email110@gmail.com', 111),
('firstname111', 'lastname111', 'email111@gmail.com', 112),
('firstname112', 'lastname112', 'email112@gmail.com', 113),
('firstname113', 'lastname113', 'email113@gmail.com', 114),
('firstname114', 'lastname114', 'email114@gmail.com', 115),
('firstname115', 'lastname115', 'email115@gmail.com', 116),
('firstname116', 'lastname116', 'email116@gmail.com', 117),
('firstname117', 'lastname117', 'email117@gmail.com', 118),
('firstname118', 'lastname118', 'email118@gmail.com', 119),
('firstname119', 'lastname119', 'email119@gmail.com', 120),
('firstname120', 'lastname120', 'email120@gmail.com', 121),
('firstname121', 'lastname121', 'email121@gmail.com', 122),
('firstname122', 'lastname122', 'email122@gmail.com', 123),
('firstname123', 'lastname123', 'email123@gmail.com', 124),
('firstname124', 'lastname124', 'email124@gmail.com', 125),
('firstname125', 'lastname125', 'email125@gmail.com', 126),
('firstname126', 'lastname126', 'email126@gmail.com', 127),
('firstname127', 'lastname127', 'email127@gmail.com', 128),
('firstname128', 'lastname128', 'email128@gmail.com', 129),
('firstname129', 'lastname129', 'email129@gmail.com', 130),
('firstname130', 'lastname130', 'email130@gmail.com', 131),
('firstname131', 'lastname131', 'email131@gmail.com', 132),
('firstname132', 'lastname132', 'email132@gmail.com', 133),
('firstname133', 'lastname133', 'email133@gmail.com', 134),
('firstname134', 'lastname134', 'email134@gmail.com', 135),
('firstname135', 'lastname135', 'email135@gmail.com', 136),
('firstname136', 'lastname136', 'email136@gmail.com', 137),
('firstname137', 'lastname137', 'email137@gmail.com', 138),
('firstname138', 'lastname138', 'email138@gmail.com', 139),
('firstname139', 'lastname139', 'email139@gmail.com', 140),
('firstname140', 'lastname140', 'email140@gmail.com', 141),
('firstname141', 'lastname141', 'email141@gmail.com', 142),
('firstname142', 'lastname142', 'email142@gmail.com', 143),
('firstname143', 'lastname143', 'email143@gmail.com', 144),
('firstname144', 'lastname144', 'email144@gmail.com', 145),
('firstname145', 'lastname145', 'email145@gmail.com', 146),
('firstname146', 'lastname146', 'email146@gmail.com', 147),
('firstname147', 'lastname147', 'email147@gmail.com', 148),
('firstname148', 'lastname148', 'email148@gmail.com', 149),
('firstname149', 'lastname149', 'email149@gmail.com', 150),
('firstname150', 'lastname150', 'email150@gmail.com', 151),
('firstname151', 'lastname151', 'email151@gmail.com', 152),
('firstname152', 'lastname152', 'email152@gmail.com', 153),
('firstname153', 'lastname153', 'email153@gmail.com', 154),
('firstname154', 'lastname154', 'email154@gmail.com', 155),
('firstname155', 'lastname155', 'email155@gmail.com', 156),
('firstname156', 'lastname156', 'email156@gmail.com', 157),
('firstname157', 'lastname157', 'email157@gmail.com', 158),
('firstname158', 'lastname158', 'email158@gmail.com', 159),
('firstname159', 'lastname159', 'email159@gmail.com', 160),
('firstname160', 'lastname160', 'email160@gmail.com', 161),
('firstname161', 'lastname161', 'email161@gmail.com', 162),
('firstname162', 'lastname162', 'email162@gmail.com', 163),
('firstname163', 'lastname163', 'email163@gmail.com', 164),
('firstname164', 'lastname164', 'email164@gmail.com', 165),
('firstname165', 'lastname165', 'email165@gmail.com', 166),
('firstname166', 'lastname166', 'email166@gmail.com', 167),
('firstname167', 'lastname167', 'email167@gmail.com', 168),
('firstname168', 'lastname168', 'email168@gmail.com', 169),
('firstname169', 'lastname169', 'email169@gmail.com', 170),
('firstname170', 'lastname170', 'email170@gmail.com', 171),
('firstname171', 'lastname171', 'email171@gmail.com', 172),
('firstname172', 'lastname172', 'email172@gmail.com', 173),
('firstname173', 'lastname173', 'email173@gmail.com', 174),
('firstname174', 'lastname174', 'email174@gmail.com', 175),
('firstname175', 'lastname175', 'email175@gmail.com', 176),
('firstname176', 'lastname176', 'email176@gmail.com', 177),
('firstname177', 'lastname177', 'email177@gmail.com', 178),
('firstname178', 'lastname178', 'email178@gmail.com', 179),
('firstname179', 'lastname179', 'email179@gmail.com', 180),
('firstname180', 'lastname180', 'email180@gmail.com', 181),
('firstname181', 'lastname181', 'email181@gmail.com', 182),
('firstname182', 'lastname182', 'email182@gmail.com', 183),
('firstname183', 'lastname183', 'email183@gmail.com', 184),
('firstname184', 'lastname184', 'email184@gmail.com', 185),
('firstname185', 'lastname185', 'email185@gmail.com', 186),
('firstname186', 'lastname186', 'email186@gmail.com', 187),
('firstname187', 'lastname187', 'email187@gmail.com', 188),
('firstname188', 'lastname188', 'email188@gmail.com', 189),
('firstname189', 'lastname189', 'email189@gmail.com', 190),
('firstname190', 'lastname190', 'email190@gmail.com', 191),
('firstname191', 'lastname191', 'email191@gmail.com', 192),
('firstname192', 'lastname192', 'email192@gmail.com', 193),
('firstname193', 'lastname193', 'email193@gmail.com', 194),
('firstname194', 'lastname194', 'email194@gmail.com', 195),
('firstname195', 'lastname195', 'email195@gmail.com', 196),
('firstname196', 'lastname196', 'email196@gmail.com', 197),
('firstname197', 'lastname197', 'email197@gmail.com', 198);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `user`
--
ALTER TABLE `datatables_demo`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `datatables_demo`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=199;
COMMIT;
Step2 : We put the main code on this page.
index.php:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="generator" content="Hugo 0.72.0">
<title>server-side-datatable</title>
<!-- datatable css -->
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" />
<!-- Bootstrap core CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/css/bootstrap.min.css" integrity="sha384-r4NyP46KrjDleawBgD5tp8Y7UzmLA05oM1iAEQ17CSuDqnUK2+k9luXQOfXJCJ4I" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/js/bootstrap.min.js" integrity="sha384-oesi62hOLfzrys4LxRF63OJCXdXDipiYWBnvTl9Y9/TRlw5xlKIEHpNyvvDShgf/" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
</head>
<body style="background-color:#fdfdfd;">
<br>
<h1 align="center" style="color:color(srgb 0.52 0.59 0.7);font-weight:bold;">Datatables server side processing with PHP and MYSQL </h1>
<br>
<div class="container">
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
<th>email</th>
</tr>
</thead>
</table>
</div>
<!-- datatable js -->
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable({
"searching": true,
"processing": true,
"serverSide": true,
"ajax": "server_side.php"
});
});
</script>
</body>
</html>
Step3 : code of ssp.class.php libirary.
ssp.class.php:
<?php
/*
* Helper functions for building a DataTables server-side processing SQL query
*
* The static functions in this class are just helper functions to help build
* the SQL used in the DataTables demo server-side processing scripts. These
* functions obviously do not represent all that can be done with server-side
* processing, they are intentionally simple to show how it works. More complex
* server-side processing operations will likely require a custom script.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/
// Please Remove below 4 lines as this is use in Datatatables test environment for your local or live environment please remove it or else it will not work
$file = $_SERVER['DOCUMENT_ROOT'].'/datatables/pdo.php';
if ( is_file( $file ) ) {
include( $file );
}
class SSP {
/**
* Create the data output array for the DataTables rows
*
* @param array $columns Column information array
* @param array $data Data from the SQL get
* @return array Formatted data in a row based format
*/
static function data_output ( $columns, $data )
{
$out = array();
for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
$row = array();
for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
$column = $columns[$j];
// Is there a formatter?
if ( isset( $column['formatter'] ) ) {
if(empty($column['db'])){
$row[ $column['dt'] ] = $column['formatter']( $data[$i] );
}
else{
$row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
}
}
else {
if(!empty($column['db'])){
$row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
}
else{
$row[ $column['dt'] ] = "";
}
}
}
$out[] = $row;
}
return $out;
}
/**
* Database connection
*
* Obtain an PHP PDO connection from a connection details array
*
* @param array $conn SQL connection details. The array should have
* the following properties
* * host - host name
* * db - database name
* * user - user name
* * pass - user password
* @return resource PDO connection
*/
static function db ( $conn )
{
if ( is_array( $conn ) ) {
return self::sql_connect( $conn );
}
return $conn;
}
/**
* Paging
*
* Construct the LIMIT clause for server-side processing SQL query
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @return string SQL limit clause
*/
static function limit ( $request, $columns )
{
$limit = '';
if ( isset($request['start']) && $request['length'] != -1 ) {
$limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
}
return $limit;
}
/**
* Ordering
*
* Construct the ORDER BY clause for server-side processing SQL query
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @return string SQL order by clause
*/
static function order ( $request, $columns )
{
$order = '';
if ( isset($request['order']) && count($request['order']) ) {
$orderBy = array();
$dtColumns = self::pluck( $columns, 'dt' );
for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
// Convert the column index into the column data property
$columnIdx = intval($request['order'][$i]['column']);
$requestColumn = $request['columns'][$columnIdx];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['orderable'] == 'true' ) {
$dir = $request['order'][$i]['dir'] === 'asc' ?
'ASC' :
'DESC';
$orderBy[] = '`'.$column['db'].'` '.$dir;
}
}
if ( count( $orderBy ) ) {
$order = 'ORDER BY '.implode(', ', $orderBy);
}
}
return $order;
}
/**
* Searching / Filtering
*
* Construct the WHERE clause for server-side processing SQL query.
*
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here performance on large
* databases would be very poor
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @param array $bindings Array of values for PDO bindings, used in the
* sql_exec() function
* @return string SQL where clause
*/
static function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );
if ( isset($request['search']) && $request['search']['value'] != '' ) {
$str = $request['search']['value'];
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['searchable'] == 'true' ) {
if(!empty($column['db'])){
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
}
// Individual column filtering
if ( isset( $request['columns'] ) ) {
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
$str = $requestColumn['search']['value'];
if ( $requestColumn['searchable'] == 'true' &&
$str != '' ) {
if(!empty($column['db'])){
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$columnSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
}
// Combine the filters into a single string
$where = '';
if ( count( $globalSearch ) ) {
$where = '('.implode(' OR ', $globalSearch).')';
}
if ( count( $columnSearch ) ) {
$where = $where === '' ?
implode(' AND ', $columnSearch) :
$where .' AND '. implode(' AND ', $columnSearch);
}
if ( $where !== '' ) {
$where = 'WHERE '.$where;
}
return $where;
}
/**
* Perform the SQL queries needed for an server-side processing requested,
* utilising the helper functions of this class, limit(), order() and
* filter() among others. The returned array is ready to be encoded as JSON
* in response to an SSP request, or can be modified if needed before
* sending back to the client.
*
* @param array $request Data sent to server by DataTables
* @param array|PDO $conn PDO connection resource or connection parameters array
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @return array Server-side processing response array
*/
static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::db( $conn );
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
/**
* The difference between this method and the `simple` one, is that you can
* apply additional `where` conditions to the SQL queries. These can be in
* one of two forms:
*
* * 'Result condition' - This is applied to the result set, but not the
* overall paging information query - i.e. it will not effect the number
* of records that a user sees they can have access to. This should be
* used when you want apply a filtering condition that the user has sent.
* * 'All condition' - This is applied to all queries that are made and
* reduces the number of records that the user can access. This should be
* used in conditions where you don't want the user to ever have access to
* particular records (for example, restricting by a login id).
*
* @param array $request Data sent to server by DataTables
* @param array|PDO $conn PDO connection resource or connection parameters array
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @param string $whereResult WHERE condition to apply to the result set
* @param string $whereAll WHERE condition to apply to all queries
* @return array Server-side processing response array
*/
static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
{
$bindings = array();
$db = self::db( $conn );
$localWhereResult = array();
$localWhereAll = array();
$whereAllSql = '';
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
$whereResult = self::_flatten( $whereResult );
$whereAll = self::_flatten( $whereAll );
if ( $whereResult ) {
$where = $where ?
$where .' AND '.$whereResult :
'WHERE '.$whereResult;
}
if ( $whereAll ) {
$where = $where ?
$where .' AND '.$whereAll :
'WHERE '.$whereAll;
$whereAllSql = 'WHERE '.$whereAll;
}
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table` ".
$whereAllSql
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
/**
* Connect to the database
*
* @param array $sql_details SQL server connection details array, with the
* properties:
* * host - host name
* * db - database name
* * user - user name
* * pass - user password
* @return resource Database connection handle
*/
static function sql_connect ( $sql_details )
{
try {
$db = @new PDO(
"mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
$sql_details['user'],
$sql_details['pass'],
array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
);
}
catch (PDOException $e) {
self::fatal(
"An error occurred while connecting to the database. ".
"The error reported by the server was: ".$e->getMessage()
);
}
return $db;
}
/**
* Execute an SQL query on the database
*
* @param resource $db Database handler
* @param array $bindings Array of PDO binding values from bind() to be
* used for safely escaping strings. Note that this can be given as the
* SQL query string if no bindings are required.
* @param string $sql SQL query to execute.
* @return array Result from the query (all rows)
*/
static function sql_exec ( $db, $bindings, $sql=null )
{
// Argument shifting
if ( $sql === null ) {
$sql = $bindings;
}
$stmt = $db->prepare( $sql );
//echo $sql;
// Bind parameters
if ( is_array( $bindings ) ) {
for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
$binding = $bindings[$i];
$stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
}
}
// Execute
try {
$stmt->execute();
}
catch (PDOException $e) {
self::fatal( "An SQL error occurred: ".$e->getMessage() );
}
// Return all
return $stmt->fetchAll( PDO::FETCH_BOTH );
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Internal methods
*/
/**
* Throw a fatal error.
*
* This writes out an error message in a JSON string which DataTables will
* see and show to the user in the browser.
*
* @param string $msg Message to send to the client
*/
static function fatal ( $msg )
{
echo json_encode( array(
"error" => $msg
) );
exit(0);
}
/**
* Create a PDO binding key which can be used for escaping variables safely
* when executing a query with sql_exec()
*
* @param array &$a Array of bindings
* @param * $val Value to bind
* @param int $type PDO field type
* @return string Bound key to be used in the SQL where this parameter
* would be used.
*/
static function bind ( &$a, $val, $type )
{
$key = ':binding_'.count( $a );
$a[] = array(
'key' => $key,
'val' => $val,
'type' => $type
);
return $key;
}
/**
* Pull a particular property from each assoc. array in a numeric array,
* returning and array of the property values from each item.
*
* @param array $a Array to get data from
* @param string $prop Property to read
* @return array Array of property values
*/
static function pluck ( $a, $prop )
{
$out = array();
for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
if(empty($a[$i][$prop])){
continue;
}
//removing the $out array index confuses the filter method in doing proper binding,
//adding it ensures that the array data are mapped correctly
$out[$i] = $a[$i][$prop];
}
return $out;
}
/**
* Return a string from an array or a string
*
* @param array|string $a Array to join
* @param string $join Glue for the concatenation
* @return string Joined string
*/
static function _flatten ( $a, $join = ' AND ' )
{
if ( ! $a ) {
return '';
}
else if ( $a && is_array($a) ) {
return implode( $join, $a );
}
return $a;
}
}
Step4 : put main code of php server_side.php.
server_side.php:
<?php
/*
* DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simple to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
// DB table to use
$table = 'datatables_demo';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'firstname', 'dt' => 0 ),
array( 'db' => 'lastname', 'dt' => 1 ),
array(
'db' => 'email',
'dt' => 2,
),
/** if use date format */
// array(
// 'db' => 'date',
// 'dt' => 3,
// 'formatter' => function( $d, $row ) {
// return '$'.number_format($d);
// }
// )
);
// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'serverside',
'host' => 'localhost'
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
?>
