Problem with Server Side Processing and Paging
Problem with Server Side Processing and Paging
Tinydan
Posts: 5Questions: 3Answers: 0
One again I'm having an issue with my server side processing that i hope you can shed some light on. I've managed to get my DataTable to talk to the server and successfully bring back the rows I'm looking for. The issue now is that the paging of the data is not working. The first ten of around 65,000 rows are being displayed but when clicking the next button expecting the next ten the DataTable displays the message No matching records found despite there being plenty of rows in the database.
HTML:
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<!--Datatables Style-->
<link rel="Stylesheet" type="text/css" href="DataTables-1.10.0/media/css/jquery.dataTables.css" />
<link rel="Stylesheet" type="text/css" href="DataTables-1.10.0/extensions/TableTools/css/dataTables.TableTools.min.css" />
<!--Jquery-->
<script type="text/javascript" src="DataTables-1.10.0/media/js/jquery.js"></script>
<!--Data Tables-->
<script type="text/javascript" src="DataTables-1.10.0/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="DataTables-1.10.0/extensions/TableTools/js/dataTables.tableTools.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#example').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "getRequests.php",
"dom": 'T<"clear">lfrtip',
"tableTools": {
"sSwfPath": "DataTables-1.10.0/extensions/TableTools/swf/copy_csv_xls_pdf.swf"
}
});
});
</script>
</head>
<body>
<div>
<table id="example" class="display" cellpadding="0" width="100%">
<thead>
<tr>
<th>SIM Number</th>
<th>CC</th>
<th>Voice</th>
<th>IMSI</th>
<th>Network</th>
<th>SOA</th>
<th>Customer</th>
<th>Description</th>
<th>Part</th>
<th>Serial</th>
<th>Contract</th>
<th>Issued</th>
<th>Expiry</th>
<th>Status</th>
<th>DataGate</th>
</tr>
</thead>
<tfoot>
<tr>
<th>SIM Number</th>
<th>CC</th>
<th>Voice</th>
<th>IMSI</th>
<th>Network</th>
<th>SOA</th>
<th>Customer</th>
<th>Description</th>
<th>Part</th>
<th>Serial</th>
<th>Contract</th>
<th>Issued</th>
<th>Expiry</th>
<th>Status</th>
<th>DataGate</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
PHP:
<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "PK";
/* DB table to use */
$sTable = "tblViews";
/* Database connection information */
$gaSql['user'] = "db_user";
$gaSql['password'] = "******";
$gaSql['db'] = "db";
$gaSql['server'] = "**********\SQLEXPRESS";
/*
* Columns
* If you don't want all of the columns displayed you need to hardcode $aColumns array with your elements.
* If not this will grab all the columns associated with $sTable
*/
$aColumns = array('simNumber', 'CC', 'Voice', 'IMSI', 'Network', 'SOA', 'Customer', 'Description', 'partNumber', 'serialNumber', 'Contract', 'issueDate','expiryDate', 'status', 'Datagate');
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* ODBC connection
*/
$connectionInfo = array("UID" => $gaSql['user'], "PWD" => $gaSql['password'], "Database"=>$gaSql['db'],"ReturnDatesAsStrings"=>true);
$gaSql['link'] = sqlsrv_connect( $gaSql['server'], $connectionInfo);
$params = array();
$options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
/* Ordering */
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) ) {
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".addslashes( $_GET['sSortDir_'.$i] ) .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" ) {
$sOrder = "";
}
}
/* Filtering */
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
$sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_GET['sSearch'] )."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
if ( $sWhere == "" ) {
$sWhere = "WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".addslashes($_GET['sSearch_'.$i])."%' ";
}
}
/* Paging */
$top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
$limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;
$sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
FROM $sTable
$sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
(
SELECT $sIndexColumn FROM
(
SELECT TOP $top ".implode(",",$aColumns)."
FROM $sTable
$sWhere
$sOrder
)
as [virtTable]
)
$sOrder";
$rResult = sqlsrv_query($gaSql['link'],$sQuery) or die("$sQuery: " . sqlsrv_errors());
$sQueryCnt = "SELECT * FROM $sTable $sWhere";
$rResultCnt = sqlsrv_query( $gaSql['link'], $sQueryCnt ,$params, $options) or die (" $sQueryCnt: " . sqlsrv_errors());
$iFilteredTotal = sqlsrv_num_rows( $rResultCnt );
$sQuery = " SELECT * FROM $sTable ";
$rResultTotal = sqlsrv_query( $gaSql['link'], $sQuery ,$params, $options) or die(sqlsrv_errors());
$iTotal = sqlsrv_num_rows( $rResultTotal );
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
if ( $aColumns[$i] != ' ' ) {
$v = $aRow[ $aColumns[$i] ];
$v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
$row[]=$v;
}
}
If (!empty($row)) { $output['aaData'][] = $row; }
}
echo json_encode( $output );
?>
I hope the information I've provided Helps shed some light one where I might be going wrong.
This discussion has been closed.
Answers
You already posted about this, and you got answers. Firstly, here:
http://datatables.net/forums/discussion/7127/secho-error-from-server-processing-php.
and secondly when I pointed that you are still using sEcho when you had already been told it should not be used with server-side processing. I suggest you take the time to digest what you have already been told.
Ok its all fixed. I feel a bit of a fool for it but I looked at my Sql Profiler to see what queries were running on the database.
Here was the query:
As you can see its looking for PK in another query that doesn't contain PK so the returned result will always be a Null value.
A simple change to include the PK in the table and the values being queried and the paging function is working.
@Tangerine
I made another post as this is regarding the paging of DataTables and not the actual data retrieval problem I was having. While you say sEcho isn't required the functionality I'm expecting from this server side script is working well from the simple change I made and posted top the other thread.