 |
Search button and page section are not working. server side processing datatables with sql server |
| |
 |
|
|
 |
 |
|
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
 Quote:<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
Quote:<?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!
Tag : PHP
|
|
|
 |
 |
 |
 |
| Date :
2019-09-08 20:27:59 |
By :
rainkv |
View :
1277 |
Reply :
7 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
Code (PHP)
$sEcho = @$_GET['sEcho'] > 0 ? @$_GET['sEcho'] : 0;
$output = array(
"sEcho" => $sEcho,
//
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
|
| |
|
|
| |
| Date :
2019-09-09 10:38:02 |
By :
mongkon.k |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
Thanks for the reply, I changed the whole pagination script from the link you sent but still did not change.
Quote: <head>
<title>Test</title>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<link href="https://cdn.datatables.net/scroller/2.0.0/css/scroller.dataTables.min.css">
<!--================Script===============================-->
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/scroller/2.0.0/js/dataTables.scroller.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#example').dataTable({
"processing": true,
"serverSide": true,
"ajax": "test2.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>
|
ประวัติการแก้ไข
2019-09-10 14:25:53
2019-09-10 14:26:27
| |
|
|
| |
| Date :
2019-09-10 14:17:18 |
By :
rainkv |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
let me see your test2.php code
|
| |
|
|
| |
| Date :
2019-09-10 16:19:50 |
By :
mongkon.k |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ตอบความคิดเห็นที่ : 5 เขียนโดย : mongkon.k เมื่อวันที่ 2019-09-10 16:19:50
รายละเอียดของการตอบ ::
... ใส่ความคิดเห็นตรงนี้.......
My code test2.php. Thanks verry much!
Quote:<?php
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "Studen_Id";
/* DB table to use */
$sTable = "Student";
/* Database connection information */
$gaSql['user'] = "stud";
$gaSql['password'] = "2345678";
$gaSql['db'] = "stud";
$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("Student_Name","Student_Address");
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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 );
$sEcho = @$_GET['sEcho'] > 0 ? @$_GET['sEcho'] : 0;
$output = array(
"sEcho" => $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 );
?>
|
ประวัติการแก้ไข
2019-09-11 05:38:19
| |
|
|
| |
| Date :
2019-09-11 05:35:43 |
By :
rainkv |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
