Forum
Log In
Home
Topic RSS
11:26
04/07/2011
Offline
Hi
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:
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');
?>
<script type="text/javascript">
jQuery.noConflict();
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>
<table id="list" class="search"></table>
<div id="pager" class="search" style="text-align:center;"></div>
and this is 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' ) );
$host = 'dbhost';
$user = 'xxxxxx';
$password = 'xxxxxxxx';
$database = 'xxxxxx';
$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]) 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>";
// 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)
?>
As you can see I am using a SQL server database for my data.
Any help will be grate,
12:01
Moderators
30/10/2007
Offline
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
For professional UI suites for Java Script and PHP visit us at our commercial products site - guriddo.net - by the very same guys that created jqGrid.
12:20
04/07/2011
Offline
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
13:45
04/07/2011
Offline
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:
//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.
Most Users Ever Online: 715
Currently Online:
49 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
OlegK: 1255
markw65: 179
kobruleht: 144
phicarre: 132
YamilBracho: 124
Renso: 118
Member Stats:
Guest Posters: 447
Members: 11373
Moderators: 2
Admins: 1
Forum Stats:
Groups: 1
Forums: 8
Topics: 10592
Posts: 31289
Newest Members:
, razia, Prankie, psky, praveen neelam, greg.valainis@pa-tech.comModerators: tony: 7721, Rumen[Trirand]: 81
Administrators: admin: 66
