Server Side Scripting with MS SQL: sEcho php error
Server Side Scripting with MS SQL: sEcho php error
Tinydan
Posts: 5Questions: 3Answers: 0
http://debug.datatables.net/egovir
I posted yesterday that I was having trouble getting the server side processing functionality of DataTables working. I have since done a bit more research and found that my PHP environment needed a little tweaking to work. I have since managed to get a response from my database on the server but I'm still having issues.
My page loads correctly and displays the one test column using but when I attempt to sort on a column or increase the number of records being show the server side script returns exactly the same data as the initial request.
I have been relieving this error from my PHP installation:
[14-May-2014 15:14:38 Europe/London] PHP Notice: Undefined index: sEcho in C:\inetpub\wwwroot\dataTables\getRequests.php on line 103
I have done a bit of searching and found that Allan said that sEcho is not required for server side processing here http://datatables.net/forums/discussion/7127/secho-error-from-server-processing-php.
I've checked the validity of the JSON response from the server and it is valid.
Unfortunately I'm working on an intranet so I can't show a working example of my website however I'll post my code here in order for it to be checked.
HTML Code
<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" />
<!--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">
$(document).ready(function () {
$('#example').dataTable({
"processing": true,
"serverSide": true,
"ajax": "getRequests.php"
});
});
</script>
</head>
<body>
<div>
<table id="example" class="display" cellpadding="0" width="100%">
<thead>
<tr>
<th>SR_SOA</th>
</tr>
</thead>
<tfoot>
<tr>
<th>SR_SOA</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
getRequests.php:
(As before the Connection strings have been Omitted)
<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "SR_REQUEST_PK";
/* DB table to use */
$sTable = "SR_SIM_REQUEST_TBL";
/* Database connection information */
$gaSql['user'] = "Testdb_user";
$gaSql['password'] = "******";
$gaSql['db'] = "Testdb";
$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('SR_SOA');
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 );
<?php
>
```
?>
Parameters send with the initial request:
_ 1400076749047
columns[0][data] 0
columns[0][name]
columns[0][orderable] true
columns[0][search][regex] false
columns[0][search][value]
columns[0][searchable] true
draw 1
length 10
order[0][column] 0
order[0][dir] asc
search[regex] false
search[value]
start 0
Parameters sent with after selecting 25 records to display;
_ 1400076749048
columns[0][data] 0
columns[0][name]
columns[0][orderable] true
columns[0][search][regex] false
columns[0][search][value]
columns[0][searchable] true
draw 2
length 25
order[0][column] 0
order[0][dir] asc
```
As you can see the requests are different but the response is the same ten rows. Any input on this would be a big help.
Cheers,
Dan
Answers
Did you look at line 103? That's where you try to access sEcho, which is not in your $_GET array.
Hi tangerine,
I fixed the issue of the Data Table only displaying the first data request. I modified my java script to this:
The only issue with this is that it returns all of the data in one draw. The first X rows as selected by the user are displayed but when clicking on the next page button there message received is "No matching records found" even when there records that should be there.-
Here is the response from the server on one of these draws:
(Please note. I have removed the data from the array for security).
Adding a filter changes the iTotalDisplayReocrds to change as expected:
In both these cases iDisplayStart in the request parameters is set to 0. This displays the data from the first record found.
When moving to the second page the request looks like this:
In this case i have not removed the data from the array. In this request itotalDisplayRecords is set to 10. This should result in the first record being displayed as the 10th returned but it isn't displaying anything.