jQuery Grid Plugin - jqGrid - Topic: Missing Operators From Search List.

4D on Missing Operators From Search List.

Thu, 01 Sep 2011 13:55:42 +0300

Ok getting a little closer to finding out what I've done wrong.

using jquery.jqGrid.src.js

I've found that adding

'lt', 'le', 'gt', 'ge',

stropts : ['eq', 'ne', 'bw', 'bn', 'ew', 'en', 'cn', 'nc', 'nu', 'nn', 'in', 'ni'],

enables the operators in the list.

So I could now do with knowing what stropts and numopts are as the missing ones are listed under numopts.

I'm guessing that it's perhaps to do with data types or something?

4D on Missing Operators From Search List.

Tue, 30 Aug 2011 12:51:26 +0300

I have now re-installed the jqgrid scripts.

Having also re-read the documentation and changed the script that calls the search to

jQuery("#list").jqGrid({pager:'#pager'}).navGrid('#pager',{view:true,add:false,del:false,edit:false},
    {},
    {},
    {},
    {multipleSearch:true, multipleGroup:true, showQuery:true},
    {caption: "Search...",
     Find: "Find",
     Reset: "Reset",
     odata : ['equal', 'not equal', 'less', 'less or equal','greater','greater or equal', 'begins with','does not begin with','is in','is not in','ends with','does not end with','contains','does not contain']
   });

However I'm still not getting 'less', 'less or equal', 'greater', and 'greater or equal' in the drop down.

Got to be missing something somewhere.

/blog/?page_id=393/help/missing-operators-from-search-list/&value=odata&type=1&include=1&search=1&ret=all

4D on Missing Operators From Search List.

Fri, 26 Aug 2011 16:48:05 +0300

Have now tried running it from jquery.jqGrid.src.js file.

No change. Something is stopping > and < operators from showing on the list.

Anyone got any thoughts?

4D on Missing Operators From Search List.

Thu, 25 Aug 2011 16:13:34 +0300

Have now also found

description:"does not contain",operator:"NOT LIKE"},{name:"nu",description:"is null",operator:"IS NULL"},{name:"nn",description:"is not null",operator:"IS NOT NULL"}],numopts:["eq","ne","lt","le","gt","ge","nu","nn","in","ni"],stropts:["eq","ne","bw","bn","ew","en","cn","nc","nu","nn","in","ni"],_gridsopt:[],groupOps:["AND","OR"],operator:"<="},{name:"gt",description:"greater",operator:">"},{name:"ge",description:"greater or equal",operator:">="},{name:"bw",description:"begins with",operator:"LIKE"},{name:"bn",description:"does not begin with",operator:"NOT LIKE"},{name:"in",description:"in",operator:"IN"},{name:"ni",description:"not in",operator:"NOT IN"},{name:"ew",description:"ends with",operator:"LIKE"},{name:"en",description:"does not end with",operator:"NOT LIKE"},{name:"cn",description:"contains",operator:"LIKE"},{name:"nc",ops:[{name:"eq",description:"equal",operator:"="},{name:"ne",description:"not equal",operator:"<>"},{name:"lt",description:"less",operator:"<"},{name:"le",description:"less or equal",

in jquery.jqGrid.min.js, and changes to names here are also reflected.

Must have misconfigured something somewhere.

4D on Missing Operators From Search List.

Thu, 25 Aug 2011 15:55:18 +0300

Looking a bit closer I have

odata : ['equal', 'not equal', 'less', 'less or equal','greater','greater or equal', 'begins with','does not begin with','is in','is not in','ends with','does not end with','contains','does not contain'],

Which features Greater and Greater or Equal, however they are not present, yet changes here reflect in the search box.

Could be due to inconsistency in naming of the operators.

4D on Missing Operators From Search List.

Thu, 25 Aug 2011 15:52:51 +0300

Found someting.

Looks like it's pulling the values from the Language file grid.locale.js

So should that be over-ruled by jquery.searchFilter.js, and if so why is it not?

4D on Missing Operators From Search List.

Wed, 24 Aug 2011 14:44:10 +0300

I've successfully implemented a grid, with a subgrid.
I've also got the advanced search feature running, and it returns data successfully.
However I have noticed I am missing some of the operators in the list. There appears to be no Greater Then or Less Then.

[Image Can Not Be Found]

I have checked the file jquery.SearchFilter.js and the full list appears in there.

    operators: [
        { op: "eq", text: "is equal to" },
        { op: "ne", text: "is not equal to" },
        { op: "lt", text: "is less than" },
        { op: "le", text: "is less or equal to" },
        { op: "gt", text: "is greater than" },
        { op: "ge", text: "is greater or equal to" },
        { op: "in", text: "is in" },
        { op: "ni", text: "is not in" },
        { op: "bw", text: "begins with" },
        { op: "bn", text: "does not begin with" },
        { op: "ew", text: "ends with" },
        { op: "en", text: "does not end with" },
        { op: "cn", text: "contains" },
        { op: "nc", text: "does not contain" }
    ],

I have also tried adding a <script> link to the searchFilter file, with no change.

Grid script:

//This line enables reload and search links on bottom of grid.
jQuery("#list").jqGrid('navGrid',"#pager",{add:false,del:false,edit:false});
jQuery("#list").jqGrid('searchGrid',{multipleSearch:true, multipleGroup:true, showQuery:true});
//jQuery("#list").jqGrid('filterToolbar',{stringResult:true, searchOnEnter:true, multipleSearch:true});

});
</script>

tdashload.php

<?php
//include the information needed for the connection to MySQL data base server.
// we store here username, database and password
require_once('../../Connections/connMeridian.php');

//Strip slashes
   if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
    {
      if (PHP_VERSION < 6) {
        $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
      }

      $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

      switch ($theType) {
        case "text":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;
        case "long":
        case "int":
          $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case "double":
          $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
          break;
        case "date":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;
        case "defined":
          $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
          break;
      }
      return $theValue;
    }
    }

// to the url parameter are added 4 parameters as described in colModel
// we should get these parameters to construct the needed query
// Since we specify in the options of the grid that we will use a GET method
// we should use the appropriate command to obtain the parameters.
// In our case this is $_GET. If we specify that we want to use post
// we should use $_POST. Maybe the better way is to use $_REQUEST, which
// contain both the GET and POST variables. For more information refer to php documentation.
// Get the requested page. By default grid sets this to 1.
$page = $_POST['page'];

// get how many rows we want to have into the grid - rowNum parameter in the grid
$limit = $_POST['rows'];

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

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

$today = date('Y-m-d', time());

// Begin routine to build WHERE clause from search string
$where = "";
$searchOn = $_REQUEST['_search'];
if($searchOn=='true') {
        $searchstr = $_REQUEST['filters'];
        $where = constructWhere($searchstr);
}
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 changed from WHERE to HAVING to allow use of aliases.
                     $qwery = "HAVING ";
                  } 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 "'%" . addcslashes($val) . "'";
                        else if ($oper=='cn' || $oper=='nc') return "'%" . addslashes($val) . "%'";
                        else return "'" . addslashes($val) . "'";
        }
}
//End search routine.

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

// connect to the MySQL database server
$db = mysql_connect($hostname_connMeridian, $username_connMeridian, $password_connMeridian) or die("Connection Error " . mysql_error());

// select the database
mysql_select_db($database_connMeridian) or die("Error connecting to db");

// calculate the number of rows for the query. We need this for paging the result
$result = mysql_query("SELECT COUNT(*) AS count FROM tbldata WHERE active = 1 AND tstampid > '".$today."';");
$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 dat.id AS did, usr.username, dat.hdno, dat.prevWork, dat.prevCon, dat.description, stat.description AS statDesc, app.description AS appDesc FROM tbldata dat INNER JOIN tblusr usr ON dat.usrID = usr.id INNER JOIN tblstatus stat ON dat.status = stat.id INNER JOIN tblapp app ON dat.appgrp = app.id ".$where." ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Could not 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['did']."</cell>";
    $s .= "<cell>". $row['username']."</cell>";
    $s .= "<cell>". $row['hdno']."</cell>";
    $s .= "<cell><![CDATA[". $row['prevWork']."]]></cell>";
    $s .= "<cell><![CDATA[". $row['prevCon']."]]></cell>";
    $s .= "<cell><![CDATA[". $row['description']."]]></cell>";
   $s .= "<cell><![CDATA[". $row['statDesc']."]]></cell>";
   $s .= "<cell><![CDATA[". $row['appDesc']."]]></cell>";
    $s .= "</row>";
}
$s .= "</rows>";

echo $s;
?>

It's very much hacked together, and it works, it's just the missing operators.

Have I missed something?

Cheers.