aColumns 就是資料庫欄位，此欄位順序對應到前台html區丟來的資料順序，所以必須跟sSearch對應。
此篇沒有使用排序功能，所以排序就沒差了。若要排序功能，則是在前台傳遞參數加上iSortCol_N的資料。
可以設定一組驗證值，否則無法直接觀看。放在一開始的if條件即可。
<?php
if(true){
//isset($_POST['ej']) or isset($_POST['post'])
/* 排除sql注入 */
foreach($_POST AS $key => $value) { $_POST[$key] = mysql_real_escape_string($value); }
/*
*
* 識別字使用的慣用命名規則
*
* a – array
* b – boolean
* f – float
* fn – function
* i – integer
* n – node (表格的一個節點)
* o – object
* s – string
* aa - Array of Array
*
所需POST資料
dis_st => 設定列出初始值
dis_len => 設定列出範圍
iSortCol_N => 0為判定(isset)是否排序，其餘為欄位量
sSortCol_N => 欄位名稱
sSearch => 搜尋字串（全欄位）
bSearchable_N => 全欄位搜尋後的單欄位是否搜尋判斷
sSearchable_N => 單欄位搜尋值
*/
$aColumns = array( 'p_id','p_name','p_issn', 'p_eissn','p_link', 'p_range' );
$aColumns_title = array( '','名稱','ISSN', 'EISSN','連結', '範圍' );
//設定不出現
$aHidden = array(0,4);
if(isset($_POST['Sort'])){
for ($i=0; $i<Count($aColumns);$i++){
for($j=0; $j<Count($aHidden); $j++){
if($aHidden[$j]==$i){continue;};
if($_POST['Sort'][$i]!=""){
$sSortable[$i] = $_POST['Sort'][$i];
}
}
}
}
if(isset($_POST['Searchable'])){
for ($i=0; $i<Count($aColumns);$i++){
if($_POST['Searchable'][$i]!="" ){
$sSearchable[$i] = $_POST['Searchable'][$i];
}
}
}
//$_POST['dis_st'] = 30;
//$_POST['dis_len'] = 20;
$_POST['sSearch'] = isset($_POST['sSearch'])?$_POST['sSearch']:"";
$_POST['dis_st'] = isset($_POST['dis_st'])?$_POST['dis_st']:0;
/* 接受值轉換 */
$sql_option = array(
"dis_st" => $_POST['dis_st'],
"dis_len" => $_POST['dis_len'],
"iSortingCols" => Count($aColumns),
"sSearch" => $_POST['sSearch'],
);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = $aColumns[0];
/* 資料庫資訊 */
$setSql['user'] = "root";
$setSql['password'] = "";
$setSql['db'] = "ej";
$setSql['server'] = "localhost";
/* 資料表 */
$sTable = "ej_periodical";
/*
* MySQL 連線
*/
$setSql['link'] = mysql_pconnect( $setSql['server'], $setSql['user'], $setSql['password'] ) or
die( '無法連線到伺服器' );
mysql_select_db( $setSql['db'], $setSql['link'] ) or
die( '無法選擇資料表 '. $setSql['db'] );
/*
* 頁數切換 dis_st起始 dis_len範圍
*/
$sLimit = "";
if ( isset( $sql_option['dis_st'] ) && $sql_option['dis_len'] != '-1' )
{
if($sql_option['dis_len'] !== 0){
$sLimit = "LIMIT ".mysql_real_escape_string( $sql_option['dis_st'] ).", ".
mysql_real_escape_string( $sql_option['dis_len'] );
}
}
/*
* Ordering 排序設定
*/
$sOrder = "";
if ( isset( $sql_option['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $sql_option['iSortingCols'] ) ; $i++ )
{
if ( isset($_POST['iSortCol_'.$i]) && $_POST['iSortCol_'.$i]!="")
{
$sOrder .= $aColumns[ intval( $_POST['iSortCol_'.$i] ) ]."
".mysql_real_escape_string( $_POST['sSortDir_'.$i] ) .", ";
}
}
//替換後方兩字，也就是替換", "為""，若沒有排序則將ORDER BY取消
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
/*
* Filtering 全資料搜尋功能（過濾）
*/
$sWhere = "";
if ( $sql_option['sSearch'] != "" or $sql_option['sSearch'] != null)
{
$sWhere = "WHERE (";
for ( $i=1 ; $i<count($aColumns) ; $i++ )//主鍵不搜尋
{
$sWhere .= $aColumns[$i]." LIKE '%".$sql_option['sSearch']."%' OR ";
}
//將最後三字元替換為""，也就是刪除"OR "
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/*
* Individual column filtering 單欄資料搜尋功能（過濾）
*/
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_POST['sSearch_'.$i]) && $_POST['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_POST['sSearch_'.$i])."%' ";
}
}
/*
* SQL queries 執行SQL
* 取得資料傳至$rResult
*/
//這邊一樣是將最後一筆 , 拿掉，implode是將陣列組合為字串，str_replace是將符合的替換掉。
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $setSql['link'] ) or die(mysql_error());
/* Data set length after filtering
* 設定在查詢（過濾）後的資料筆數
*/
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $setSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* 資料總筆數 */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $setSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* 輸出
*/
$sOutput = '{';
$sOutput .= '"dis_st": '.$_POST['dis_st'].', ';
$sOutput .= '"dis_len": '.$_POST['dis_len'].', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] != ' ' )
{
/* General output */
$sOutput .= '"'.str_replace('"', '\"', $aRow[ $aColumns[$i] ]).'",';
}
}
/*
* Optional Configuration:
* If you need to add any extra columns (add/edit/delete etc) to the table, that aren't in the
* database - you can do it here
*/
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';
echo $sOutput;
}else{
echo "此頁面不能直接訪問！";
}
?>
留言列表
