javascript - jqGrid 查询中的子句

Question

我有一个运行良好的网格。我需要做的是根据某些条件最初从数据库返回某些字段，例如通过执行

SELECT * FROM table WHERE reviewed = 0;

我希望这些结果在加载时显示在网格上，但我也希望能够在网格上使用高级搜索。我是 jqgrid 的新手，我已经研究这个问题有一段时间了。这是我的原始代码。

 //Get the requested page
$page = $_GET['page'];

//Get how many rows we want to have into the grid
$limit = $_GET['rows'];

// get index row - i.e. user click to sort. At first time sortname parameter -
// after that the index from colModel 
$sidx = $_GET['sidx']; 

// sorting order - at first time sortorder 
$sord = $_GET['sord']; 

// if we not pass at first time index use the first column for the index or what you want
if(!$sidx) $sidx =1;

//array to translate the search type
$ops = array(
    'eq'=>'=', //equal
    'ne'=>'<>',//not equal
    'lt'=>'<', //less than
    'le'=>'<=',//less than or equal
    'gt'=>'>', //greater than
    'ge'=>'>=',//greater than or equal
    'bw'=>'LIKE', //begins with
    'bn'=>'NOT LIKE', //doesn't begin with
    'in'=>'LIKE', //is in
    'ni'=>'NOT LIKE', //is not in
    'ew'=>'LIKE', //ends with
    'en'=>'NOT LIKE', //doesn't end with
    'cn'=>'LIKE', // contains
    'nc'=>'NOT LIKE'  //doesn't contain
);
function getWhereClause($col, $oper, $val){
    global $ops;
    if($oper == 'bw' || $oper == 'bn') $val .= '%';
    if($oper == 'ew' || $oper == 'en' ) $val = '%'.$val;
    if($oper == 'cn' || $oper == 'nc' || $oper == 'in' || $oper == 'ni') $val = '%'.$val.'%';
    return " WHERE $col {$ops[$oper]} '$val' ";
}
$where = ""; //if there is no search request sent by jqgrid, $where should be empty
$searchField = isset($_GET['searchField']) ? $_GET['searchField'] : false;
$searchOper = isset($_GET['searchOper']) ? $_GET['searchOper']: false;
$searchString = isset($_GET['searchString']) ? $_GET['searchString'] : false;
if ($_GET['_search'] == 'true') {
    $where = getWhereClause($searchField,$searchOper,$searchString);
}

mysql_query("SET NAMES 'utf8'");

// calculate the number of rows for the query. We need this for paging the result 
$result = mysql_query("SELECT COUNT(*) AS count FROM renal_apptRequest"); 
$row = mysql_fetch_array($result,MYSQL_ASSOC); 
$count = $row['count']; 

// calculate the total pages for the query 
if( $count > 0 && $limit > 0) { 
              $total_pages = ceil($count/$limit); 
} else { 
              $total_pages = 0; 
} 

// if for some reasons the requested page is greater than the total 
// set the requested page to total page 
if ($page > $total_pages) $page=$total_pages;

// calculate the starting position of the rows 
$start = $limit*$page - $limit;

// if for some reasons start position is negative set it to 0 
// typical case is that the user type 0 for the requested page 
if($start <0) $start = 0; 

// the actual query for the grid data 
$SQL = "SELECT * FROM renal_apptRequest".$where." ORDER BY $sidx $sord LIMIT $start , $limit"; 
$result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error()); 
// we should set the appropriate header information. Do not forget this.
header("Content-type: text/xml;charset=utf-8");


$s = "<?xml version='1.0' encoding='utf-8'?>";
$s .=  "<rows>";
$s .= "<page>".$page."</page>";
$s .= "<total>".$total_pages."</total>";
$s .= "<records>".$count."</records>";

// be sure to put text data in CDATA
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $s .= "<row id='". $row['id']."'>";            
    $s .= "<cell>". $row['id']."</cell>";
    $s .= "<cell>". $row['date']."</cell>";
    $s .= "<cell><![CDATA[". $row['referralType']."]]></cell>";
    $s .= "<cell><![CDATA[".$cipher->decryptThis($row['patientName'])."]]></cell>";
    $s .= "<cell><![CDATA[".$cipher->decryptThis($row['patientAddress'])."]]></cell>";
    $s .= "<cell>". $cipher->decryptThis($row['patientDOB'])."</cell>";
    $s .= "<cell><![CDATA[". $row['referralProvider']."]]></cell>";
    $s .= "<cell><![CDATA[".$cipher->decryptThis($row['referralReason'])."]]></cell>";
    $s .= "<cell><![CDATA[". $cipher->decryptThis($row['contactName'])."]]></cell>";
    $s .= "<cell>".$cipher->decryptThis($row['contactPhone'])."</cell>";
    $s .= "<cell><![CDATA[".$cipher->decryptThis($row['contactEmail'])."]]></cell>";
    $s .= "<cell>". $row['contactFax']."</cell>";
    $s .= "<cell><![CDATA[". $row['preferredTime']."]]></cell>";
    $s .= "<cell><![CDATA[". $row['comments']."]]></cell>";
    $s .= "<cell>". $row['reviewed']."</cell>";
    $s .= "</row>";

}
$s .= "</rows>"; 

 echo $s;

//这是我的 jqrid javascript 代码

 $(function () {
            $("#list").jqGrid({
                url:"grid_apptRequest.php",
                datatype: "json",
                mtype: "GET",
                colNames:["ID","Date","referralType","patientName","patientAddress","patientDOB","referralProvider","referralReason","contactName","contactPhone","contactEmail","contactFax","preferredTime","comments","reviewed"],
                colModel: [
            { name: "id",index:'id', width: 55,search:true, formatter:'showlink',formatoptions:{baseLinkUrl:'renal_apptRequest_review.php', target:'_blank'}},
            { name: "date",index:'date',search:true, width: 90 },
            { name: "referralType",index:'referralType',search:true, width: 80},
            { name: "patientName",index:'patientName',search:true, width: 120},
            { name: "patientAddress",index:'patientAddress',search:true, width: 120},
            { name: "patientDOB",index:'patientDOB',search:true, width: 90 },
            { name: "referralProvider",index:'referralProvider',search:true, width: 90 },
            { name: "referralReason",index:'referralReason',search:true, width: 120 },
            { name: "contactName",index:'contactName',search:true, width: 100},
            { name: "contactPhone",index:'contactPhone',search:true, width: 100},
            { name: "contactEmail",index:'contactEmail',search:true, width: 100 },
            { name: "contactFax",index:'contactFax',search:true, width: 80},
            { name: "preferredTime",index:'preferredTime',search:true, width: 30 },
            { name: "comments",index:'comments',search:true, width: 100 },
            { name: "reviewed",index:'reviewed',search:true,hidedlg:true, width: 20, align: "right" }
        ],
            pager: "#pager",
            rowNum: 10,
            rowList: [10,20,30],
            autowidth:true,
            sortname: "id",
            sortorder: "asc",
            viewrecords: true,
            gridview: true,
            autoencode: true,
            caption: "Appointment Request"
                }).navGrid("#pager", {search:true, edit:false,add:false,del:false,searchtext:"Search"});

                       });

Answer 1

这是jqGrid搜索的文件，保存为jqGrid_functions.php...

<?php

$page  = $_GET['page']; // get the requested page
$limit = $_GET['rows']; // get how many rows we want to have into the grid
$sidx  = $_GET['sidx']; // get index row - i.e. user click to sort
$sord  = $_GET['sord']; // get the direction

if(!$sidx) $sidx =1;

function runSQL($rsql)
{
    $db['default']['hostname'] = '**********';
    $db['default']['username'] = '**********';
    $db['default']['password'] = '**********';
    $db['default']['database'] = 'yourTestDb';

    $db['live']['hostname'] = '**********';
    $db['live']['username'] = '**********';
    $db['live']['password'] = '**********';
    $db['live']['database'] = 'yourProdDb';

    $active_group = 'default';

    $base_url = "http://".$_SERVER['HTTP_HOST'];
    $base_url .= str_replace(basename($_SERVER['SCRIPT_NAME']),"",$_SERVER['SCRIPT_NAME']);
    if (strpos($base_url,'yourSite.com')) $active_group = "live";

    $connect = mysql_connect($db[$active_group]['hostname'],$db[$active_group]['username'],$db[$active_group]['password']) or die ("Error: could not connect to database");
    $db = mysql_select_db($db[$active_group]['database']);
    $result = mysql_query($rsql) or die ($rsql.' /// '.mysql_error());
    return $result;
    mysql_close($connect);
}

function Strip($value)
{
    if(get_magic_quotes_gpc() != 0)
  {
    if(is_array($value))
      if ( array_is_associative($value) )
      {
        foreach( $value as $k=>$v)
          $tmp_val[$k] = stripslashes($v);
        $value = $tmp_val;
      }
      else
        for($j = 0; $j < sizeof($value); $j++)
              $value[$j] = stripslashes($value[$j]);
        else
            $value = stripslashes($value);
    }
    return $value;
}

function array_is_associative ($array)
{
  if ( is_array($array) && ! empty($array) )
  {
    for ( $iterator = count($array) - 1; $iterator; $iterator-- )
    {
      if ( ! array_key_exists($iterator, $array) ) { return true; }
    }
    return ! array_key_exists(0, $array);
  }
  return false;
}

function constructWhere($s)
{
  $qwery = "";
  //['eq','ne','lt','le','gt','ge','bw','bn','in','ni','ew','en','cn','nc']
  $qopers = array(
        'eq'=>" = ",
        'ne'=>" <> ",
        'lt'=>" < ",
        'le'=>" <= ",
        'gt'=>" > ",
        'ge'=>" >= ",
        'bw'=>" LIKE ",
        'bn'=>" NOT LIKE ",
        'in'=>" IN ",
        'ni'=>" NOT IN ",
        'ew'=>" LIKE ",
        'en'=>" NOT LIKE ",
        'cn'=>" LIKE " ,
        'nc'=>" NOT LIKE " );
  if ($s) {
      $jsona = json_decode($s,true);
      if(is_array($jsona)){
    $gopr = $jsona['groupOp'];
    $rules = $jsona['rules'];
          $i =0;
          foreach($rules as $key=>$val) {
              $field = $val['field'];
              $op = $val['op'];
              $v = $val['data'];
      if($v && $op) {
                $i++;
        // ToSql in this case is absolutley needed
        $v = ToSql($field,$op,$v);
        if ($i == 1) $qwery = " AND ";
        else $qwery .= " " .$gopr." ";
        switch ($op) {
          // in need other thing
            case 'in' :
            case 'ni' :
                $qwery .= $field.$qopers[$op]." (".$v.")";
                break;
          default:
                $qwery .= $field.$qopers[$op].$v;
        }
      }
          }
      }
  }
  return $qwery;
}
function ToSql ($field, $oper, $val) {
    // we need here more advanced checking using the type of the field - i.e. integer, string, float
    switch ($field) {
        case 'id':
            return intval($val);
            break;
        case 'amount':
        case 'tax':
        case 'total':
            return floatval($val);
            break;
        default :
            //mysql_real_escape_string is better
            if($oper=='bw' || $oper=='bn') return "'" . addslashes($val) . "%'";
            else if ($oper=='ew' || $oper=='en') return "'%" . addslashes($val) . "'";
            else if ($oper=='cn' || $oper=='nc') return "'%" . addslashes($val) . "%'";
            else return "'" . addslashes($val) . "'";
    }
}

$wh = "";
if (isset($_REQUEST['_search']))
{
    $searchOn = Strip($_REQUEST['_search']);
    if($searchOn=='true') {
      $searchstr = Strip($_REQUEST['filters']);
      $wh= constructWhere($searchstr);
    }
}
?>

现在将该文件包含在所有执行 jqGrid 查询的主页顶部，看起来像这样......

<?php

require("jqGrid_functions.php");

$result = runSQL("SELECT COUNT(*) AS count FROM yourTable WHERE id = '".$_SESSION['some_id']."' $wh");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];

if( $count >0 ) {
  $total_pages = ceil($count/$limit);
} else {
  $total_pages = 0;
}
if ($page > $total_pages) $page=$total_pages;
$start = $limit*$page - $limit; // do not put $limit*($page - 1)
$SQL = "SELECT tableId, col1, col2, col3 FROM yourTable WHERE id = '".$_SESSION['some_id']."' $wh ORDER BY $sidx $sord LIMIT $start , $limit";
$result = runSQL( $SQL );

$responce = new stdClass();
$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
    $responce->rows[$i]['tableId']=$row['tableId'];
    $responce->rows[$i]['cell']=array($row['col1'],$row['col2'],$row['col3']);
    $i++;
}
echo json_encode($responce);
?>

我过去经常使用jqGrid。我知道这个示例代码有效。这段代码是从测试页快速复制的，显然我删除了一些东西。如果它不起作用或者我忘记包含任何小帮助函数，请告诉我。另外，这不使用绑定(bind)变量，因此请确保您正在检查传入的内容。希望这会有所帮助。