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 "此頁面不能直接訪問!";
}
?>
