jQuery Grid Plugin - jqGrid - Topic: Search is not responding as expected

gexhi on Search is not responding as expected

Mon, 04 Jul 2011 13:45:58 +0300

hello,

I found a solution thanks to :http://blog.brzezinka.eu/webma.....omment-511

in my case for all those who want to use mssql this is the php:

<?php
//defined('_JEXEC',1) or die('Restricted access');
//defined( '_VALID_MOS' ) or die( 'Restricted access' );

//$host       = trim( $params->get( 'host' ) );
//$database       = trim( $params->get( 'database' ) );
//$user       = trim( $params->get( 'user' ) );
//$password       = trim( $params->get( 'password' ) );

//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);
}
//$host       = trim( $params->get( 'host' ) );
//$database       = trim( $params->get( 'database' ) );
//$user       = trim( $params->get( 'user' ) );
//$password       = trim( $params->get( 'password' ) );
//connection to the database
$host     = 'xxxxxxx';
$user     = 'xxxxxxx';
$password = 'xxxxxxx';
$database = 'xxxxxxx';

$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;

//connect to the database
$db = mssql_connect($host, $user, $password) or die("Connection Error: " . mssql_error());
mssql_select_db($database) or die("Error conecting to db.");

$result = mssql_query("SELECT COUNT(*) AS count FROM [Joomla].[dbo].[DEBITOR_TE_SHKEPUTUR]");
$row = mssql_fetch_array($result,MSSQL_ASSOC);
$count = $row['count'];
$plus1='1';

if( $count >0 ) {
    $totalpages = ceil($count/$limit);
    } else {
    $totalpages = 0;
    }

if ($page > $totalpages) $page=$totalpages;
$start = ($limit*$page - $limit);
$start1=$start+$plus1;
$next=$start+$limit; // do not put $limit*($page - 1)
$SQL = "SELECT * FROM (SELECT row_number() OVER (ORDER BY ".$sidx." ".$sord.") AS rownum ,ID,[EMER_DEBITORI],[NR_CONT] ,[Joomla_Date_Shkeputje],[DEBIA_TOTALE],[TIP_DESC],[EMER_ZONE]
FROM [Joomla].[dbo].[DEBITOR_TE_SHKEPUTUR] ".$where.") AS A
WHERE A.rownum BETWEEN ".$start1." and ".$next;
$result = mssql_query( $SQL ) or die("Couldn t execute query.".mssql_error());

if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
    header("Content-type: application/xhtml+xml;charset=utf-8");
    } else {
            header("Content-type: text/xml;charset=utf-8");
           }

    $et = ">";
    echo "<?xml version='1.0' encoding='utf-8'?$etn";
    echo "<rows>";
    echo "<page>".$page."</page>";
    echo "<total>".$totalpages."</total>";
    echo "<records>".$count."</records>";

    // be sure to put text data in CDATA
    while($row = mssql_fetch_array($result,MSSQL_ASSOC)) {
       echo "<row id='". $row[ID]."'>";
           echo "<cell>". $row[ID]."</cell>";
            echo "<cell>". $row[EMER_DEBITORI]."</cell>";
            echo "<cell>". $row[NR_CONT]."</cell>";
          //echo "<cell>". $row[name]."</cell>";
        // echo "<cell><![CDATA[". $row[Joomla_Date_Shkeputje]."]]></cell>";
           echo "<cell>". $row[Joomla_Date_Shkeputje]."</cell>";
            echo "<cell>". $row[DEBIA_TOTALE]."</cell>";
            echo "<cell>". $row[TIP_DESC]."</cell>";
            echo "<cell>". $row[EMER_ZONE]."</cell>";
     //     echo "<cell>". $row[note]."</cell>";
       //   echo "<cell><![CDATA[". $row[note]."]]></cell>";
        echo "</row>";

   }

    echo "</rows>";

mssql_close($db)

Thank you Tony for your direction.

gexhi on Search is not responding as expected

Mon, 04 Jul 2011 12:20:51 +0300

tony said:

Hello,

Please check your php code.

You do not have any logic for serach.

Serch does not work if there is no logic in your server side code.

Please post only problems related with the grid and not with PHP.

Regards

Thank you Tony for your quick response,

I have seen jqGrid Demos, and I can't find the resolution for the search logic in my php, could you/everyone please help me on this?
Appreciate your help

tony on Search is not responding as expected

Mon, 04 Jul 2011 12:01:02 +0300

Hello,

Please check your php code.

You do not have any logic for serach.

Serch does not work if there is no logic in your server side code.

Please post only problems related with the grid and not with PHP.

Regards

gexhi on Search is not responding as expected

Mon, 04 Jul 2011 11:26:56 +0300

I am having a problem with the search feature, with single field and multiple fields. Always is showing all table records when i click to find a single ID or a name or a date.

this is my code:

Java Script:

<?php // no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );?>
<?php
    $myfirstgridpath=JURI::root().'modules/mod_Nderprerje/';

    $document =& JFactory::getDocument();

    $document->addStyleSheet($myfirstgridpath.'css/ui-lightness/jquery-ui-1.7.2.custom.css');
    $document->addStyleSheet($myfirstgridpath.'css/ui.jqgrid.css');

    $document->addScript($myfirstgridpath.'js/jquery-1.4.2.min.js');
    $document->addScript($myfirstgridpath.'js/i18n/grid.locale-en.js');
    $document->addScript($myfirstgridpath.'js/jquery.jqGrid.min.js');

    jQuery(document).ready(function(){
          jQuery("#list").jqGrid({
              url:'<?php echo $myfirstgridpath.'/datasource.php';?>',
              datatype: "xml",
              mtype:'GET',
              colNames:['ID','Emri','Kontrata', 'Data', 'Debia','Tipi','Zona'],
              colModel:[{name:'ID',index:'ID', width:50},
                  {name:'EMER_DEBITORI',index:'EMER_DEBITORI', width:100},
                  {name:'NR_CONT',index:'NR_CONT', width:60},
                  {name:'Joomla_Date_Shkeputje',index:'Joomla_Date_Shkeputje', width:110},
                  {name:'DEBIA_TOTALE',index:'DEBIA_TOTALE', width:80, align:"right"},
                  {name:'TIP_DESC',index:'TIP_DESC', width:70, align:"right"},
                  {name:'EMER_ZONE',index:'EMER_ZONE', width:80,align:"right"}],
              rowNum:10,
              loadonce:false,
              autowidth:true,
              rowList:[10,20,30,100],
              pager: jQuery('#pager'),
              sortname: 'ID',
              viewrecords: true,
              sortorder: "ASC",
              caption:"Lista e Debitoreve te nderprere",
              loadtext:"Ne kerkim, ju lutem prisni",
              toolbar:[true,"top"]
          });

jQuery("#list").jqGrid('navGrid','#pager',{edit:false,add:false,del:false});

});

</script>

and this is php:

<?php
//defined('_JEXEC',1) or die('Restricted access');
//defined( '_VALID_MOS' ) or die( 'Restricted access' );

$host = 'dbhost';
$user = 'xxxxxx';
$password = 'xxxxxxxx';
$database = 'xxxxxx';

if(!$sidx) $sidx =1;

//connect to the database
$db = mssql_connect($host, $user, $password) or die("Connection Error: " . mssql_error());
mssql_select_db($database) or die("Error conecting to db.");

$result = mssql_query("SELECT COUNT(*) AS count FROM [Joomla].[dbo].[DEBITOR_TE_SHKEPUTUR]");
$row = mssql_fetch_array($result,MSSQL_ASSOC);
$count = $row['count'];
$plus1='1';

if( $count >0 ) {
    $totalpages = ceil($count/$limit);
    } else {
    $totalpages = 0;
    }

if ($page > $totalpages) $page=$totalpages;
$start = ($limit*$page - $limit);
$start1=$start+$plus1;
$next=$start+$limit; // do not put $limit*($page - 1)
$SQL = "SELECT * FROM (SELECT row_number() OVER (ORDER BY ".$sidx." ".$sord.") AS rownum ,ID,[EMER_DEBITORI],[NR_CONT] ,[Joomla_Date_Shkeputje],[DEBIA_TOTALE],[TIP_DESC],[EMER_ZONE] FROM [Joomla].[dbo].[DEBITOR_TE_SHKEPUTUR]) AS A
WHERE A.rownum BETWEEN ".$start1." and ".$next;
$result = mssql_query( $SQL ) or die("Couldn t execute query.".mssql_error());

if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
    header("Content-type: application/xhtml+xml;charset=utf-8");
    } else {
            header("Content-type: text/xml;charset=utf-8");
           }

    $et = ">";
    echo "<?xml version='1.0' encoding='utf-8'?$et\n";
    echo "<rows>";
    echo "<page>".$page."</page>";
    echo "<total>".$totalpages."</total>";
    echo "<records>".$count."</records>";

mssql_close($db)

As you can see I am using a SQL server database for my data.

Any help will be grate,