How to implement pagination with server side get api in Datatables?

Today, We want to share with you jquery datatable server side pagination.In this post we will show you DataTables AJAX Pagination with Search and Sort, hear for implementation of Bootstrap DataTable jQuery DataTable server side sorting,pagination and searching using PHP and MySQL we will give you demo and example for implement.In this post, we will learn about Laravel Datatable Pagination Sorting and Search server side processing with an example.

Bootstrap DataTable Using PHP, Mysql, Ajax, Json with server side script

Bootstrap DataTable is just like a table that can easily integrated with Bootstarp using Bootstrap table styling option.

Latest compiled and CSS and JavaScript

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>

//and
<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>


Now we need to create a Mysql Table from which data will be populated.
CREATE TABLE `tbl_datatable`
CREATE TABLE `tbl_datatable` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(250) NOT NULL default '',
`price` varchar(250) NOT NULL default '',
`pcode` varchar(250) NOT NULL default '',
`created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

This is file where datatable will be displayed.
index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">

<meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
<script type="text/javascript" language="javascript" src="js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>
<script type="text/javascript" language="javascript" class="init">

$(document).ready(function() {
$('#example').dataTable( {
"aProcessing": true,
"aServerSide": true,
"ajax": "server-response.php",
} );
} );

</script>
</head>

<body>
<table id="example" cellspacing="0" width="100%">
<thead>
<tr>
<th>First name</th>
<th>Product Price</th>
<th>Product Code</th>
<th>Start Date</th>
</tr>
</thead>
</table>

</body>
</html>

The jquery Code:

<script type="text/javascript" language="javascript" class="init">

$(document).ready(function() {
$('#example').dataTable( {
"aProcessing": true,
"aServerSide": true,
"ajax": "server-response.php",
} );
} );

</script>

PHP Server side Scripting Code

Now Server side Scripting Code start here.

<?php

$aColumns = array( 'name', 'price', 'pcode', 'created_at');


$sIndexColumn = "id";


$sTable = "shop_product";


$gaSql['user'] = "root";
$gaSql['password'] = "[email protected]";
$gaSql['db'] = "all_products";
$gaSql['server'] = "yourhostnamelikelocalhost";


function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}


/*
* MySQL connection
*/
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}

if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}


/*
* Paging
*/
$queryPaging = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$queryPaging = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
$sorting_tbl = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sorting_tbl = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sorting_tbl .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
}
}

$sorting_tbl = substr_replace( $sorting_tbl, "", -2 );
if ( $sorting_tbl == "ORDER BY" )
{
$sorting_tbl = "";
}
}


/*
* Filtering
*/
$extraWhCondition = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$extraWhCondition = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
{
$extraWhCondition .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}
$extraWhCondition = substr_replace( $extraWhCondition, "", -3 );
$extraWhCondition .= ')';
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $extraWhCondition == "" )
{
$extraWhCondition = "WHERE ";
}
else
{
$extraWhCondition .= " AND ";
}
$extraWhCondition .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}


/*
* SQL queries
*/
$sqlQQQQ = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$extraWhCondition
$sorting_tbl
$queryPaging
";
$rResult = mysql_query( $sqlQQQQ, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );

/* Data set length after filtering */
$sqlQQQQ = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sqlQQQQ, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sqlQQQQ = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sqlQQQQ, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];



/*
* Output
*/
/*
$results = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
*/

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special results formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General results */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$results['aaData'][] = $row;
}

echo json_encode( $results );
?>

I hope you get an idea about datatables server-side processing using php with mysql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.