Search button and page section are not working.
Search button and page section are not working.
rainkv
Posts: 8Questions: 2Answers: 0
I use server side processing datatables with sql server to display the data on my page but it seems that the search button, pagination, show entries are not working.
Code Index.php
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Test</title>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<link rel="stylesheet" href="dataTables/jquery.dataTables.min.css">
<link rel="stylesheet" href="dataTables/bootstrap.min.css">
<!--<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">-->
<script src="dataTables/jQuery-3.3.1/jquery-3.3.1.min.js"></script>
<script src="dataTables/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('table.display').dataTable({
"processing": true,
"serverSide": true,
"paging": true,
"ordering": true,
"filter": true,
"ajax": "Processing.php"
})
});
</script>
</head>
<body>
<div>
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
</tr>
</thead>
<tfoot>
<tr>
<th>First name</th>
<th>Last name</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>`
and code Processing.php
<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "Student_Id";
/* DB table to use */
$sTable = "Student";
/* Database connection information */
$gaSql['user'] = "sa";
$gaSql['password'] = "1234567";
$gaSql['db'] = "StudentManager";
$gaSql['server'] = "192.168.1.150";
/*
* 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("NameStudent","ClassStudent");
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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'],"characterSet"=>"UTF-8", "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'] ):25;
$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 get this error message
Notice: Undefined index: sEcho in C:\xampp\htdocs\MyPHP\Processing.php on line 103
{"sEcho":0,"iTotalRecords":68156,"iTotalDisplayRecords":68156,"aaData":[["18000001","NGUY\u1ec4N NG\u1eccC ANH"]
Looking forward to the tutorial, I have just learned about php so maybe I do not understand much. Many thanks!
This discussion has been closed.
Answers
You are using a legacy server-side processing script there. It uses the old hungarian notation parameters rather than the newer camelCase.
Try using the new SSP class. Examples on how to use it here.
Allan
It seems that it is using mysql. And I use Sql Server to get data.
Can someone help me?
Did you use the new code as Allan suggested? What was the result?
Does the code that Allan recommends seem to use mysql? I use sqlsrv to connect, so I don't know how to move from mysql to sqlsrv. Can you help me? Thank you!
The PDO library deals with the DB types. Have you tried it?