jQuery Grid Plugin - jqGrid - Topic: search help needed

tim on search help needed

Fri, 04 Jun 2010 17:08:20 +0300

ploppy on search help needed

Fri, 04 Jun 2010 17:06:55 +0300

thanks ever so much tim. much appreciate your time and effort.

tim on search help needed

Fri, 04 Jun 2010 16:55:20 +0300

So look at your SQL statements:

$result = mysql_query("SELECT COUNT(*) AS count FROM jqgrid");

$SQL = "SELECT * FROM jqgrid ORDER BY $sidx $sord LIMIT $start , $limit";

and compare that to the jqGrid example:

$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id");

Yours has no WHERE clause.

You can see the arguments you need to check for:

$row[id],$row[title],$row[director],$row[year],$row[bond],$row[budget]

and you know how to check for them…

$id= $_GET["id"];

I'm not a php guy so I am not going to start debugging/suggesting your code, but you need to handle the arguments passed into your php script and incorporate them into your SQL statement.

tim

ploppy on search help needed

Fri, 04 Jun 2010 16:34:00 +0300

tim

so i understand you correctly, are you saying code in the new.php file is enough to perform and display the results from the click of the find button? i have included the php file i am using to display data. many thanks for your help.

<?php

// Include the information needed for the connection to
// MySQL data base server.
include("dbconfig.php");
//since we want to use a JSON data we should include
//encoder and decoder for JSON notation
//If you use a php >= 5 this file is not needed
include("JSON.php");

// create a JSON service
$json = new Services_JSON();

// to the url parameter are added 4 parameter
// we shuld get these parameter to construct the needed query
// for the pager
$examp = $_GET["q"];

// get the requested page
$page = $_GET['page'];
// POST how many rows we want to have into the grid
// rowNum parameter in the grid
$limit = $_GET['rows'];
// POST 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
if(!$sidx) $sidx =1;
// connect to the MySQL database server
$db = mysql_connect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());

// select the database
mysql_select_db($database) or die("Error conecting to db.");

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

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

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

// calculate the starting position of the rows
$start = $limit*$page - $limit; // do not put $limit*($page - 1)
// 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 jqgrid ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());

// constructing a JSON
$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $responce->rows[$i]['id']=$row[id];
    $responce->rows[$i]['cell']=array($row[id],$row[title],$row[director],$row[year],$row[bond],$row[budget]);
    $i++;
}
// return the formated data
echo $json->encode($responce);

?>

if that is the case, i should be seeing results? where are the fields being passed searched? ie,

_search	true
nd	1275658385590
page	1
rows	10
searchField	id
searchOper	eq
searchString	5
sidx	id
sord	desc

many thanks

tim on search help needed

Fri, 04 Jun 2010 16:09:43 +0300

np.

> does the search need to be built into one these files?

The jqGrid plugin automatically applies the search parameters to your 'new.php', so you don't need to specifiy a separate url for searching.

> in new.php as above, does that become new.php?q=1

some demo files have q=1 as part of the base url. So it's included in every request for data. Tony uses q=1 just to specify which base query he wants to run for a given grid example.

For you, new.php becomes new.php?searchField=id&searchOper=eq&searchString=27

so new.php will include your search.

> the way i see it, there is no other way to pass the values to say search.php as per the demo files

I'm not sure which example shows 'search.php', I see 'server.php?q=1' , but either way the url name is just a name, and jqGrid will use url:'xxxx.php' the same way no matter what the name is. For ease in understanding I always use 'data' in the url name since it better represents what it's purpose is.

ie. change 'new.php' to 'data.php' just to make life easier

tim

ploppy on search help needed

Fri, 04 Jun 2010 12:48:12 +0300

tim

thanks very much for a detailed explanation. i totally get everything you have said and i know that the passed vars need to be processed by php & mysql. but what i cannot see is where to specify the php page to do this. for example, i have new.php in my sample code that displays the listing. i then have edit.php which handles the add,del etc of records. does the search need to be built into one these files? for example, in new.php as above, does that become new.php?q=1 because i noticed from your sample files that $examp var is expecting this in a $_REQUEST field? if that is the case, do i just build the search criteria into new.php? because the way i see it, there is no other way to pass the values to say search.php as per the demo files? am i getting closer?

many thanks for your time and help tim.

tim on search help needed

Fri, 04 Jun 2010 03:32:46 +0300

I'll do my best, but I don't think I fully understand you.

> What i am failing to see is if both the display and the search is handled by in my case new.php then how are these filters reacting with the db?

The first post made for your grid to request data is on load and has the following argument:

_search false

So you can use PHP or whatever to return your base set of data.

When you fill-out the search box and request filtered data, the new post args are different where

_search   true
nd   1275596107133
page   1
rows   10
searchField   id
searchOper   eq
searchString   27
sidx   id
sord   desc

Knowing search is now true you need to use server side code (like PHP) to grab these arguments and use them to run a new SQL query that obtains the filtered data.

I'm no PHP guy, but the demo PHP example files typically store the results of the SQL query in a variable like $results, which is in turn echoed back as a response to the post, which then loads the new (and different data) into the grid.

> do i need to communicate with these files or are you saying that all display and search results should be as per my script above and search results should show?

Yes. Your new.php script needs to handle the arguments passed from the client and return filtered data should _search = true.

One additional thing I notice is that you have mtype: 'POST'. I generally leave that out so the default is GET as it's intended to return a response.

[Edit] All this being said, if your intent is to send the data to the client once, then let them search within the already loaded data, then use the new local data settings… I believe searching on large client side data is a new feature in 3.7 so you would need to check the docs for details.

tim

ploppy on search help needed

Thu, 03 Jun 2010 23:25:48 +0300

tim

these are filters that are being posted as pre firebug console.

_search   true
nd   1275596107133
page   1
rows   10
searchField   id
searchOper   eq
searchString   27
sidx   id
sord   desc

ordinarily, i will pass these filters to a php page and process them based on some kind of isset function based on _search being set(true). what i am failing to see is if both the display and the search is handled by in my case new.php then how are these filters reacting with the db? in my case all i see after pressing the find icon is loading and my grid stays the same. for example, in the demo files there are 2 files, search.php and server.php which on viewing set the vars based on what is passed, then creates some sort of where clause $wh? and reacts with the db. do i need to communicate with these files or are you saying that all display and search results should be as per my script above and search results should show?if this is the case, why is my script not showing the search results based on what i have posted?

many thanks

tim on search help needed

Thu, 03 Jun 2010 22:42:33 +0300

Your original url:'new.php' handles both the initial load and subsequent loads (includes the search parameters).

Use Firebug or some tool to see the 'filters' posted.

tim

ploppy on search help needed

Thu, 03 Jun 2010 17:24:46 +0300

any further help with this please. thanks

ploppy on search help needed

Wed, 02 Jun 2010 17:36:06 +0300

hello

after many days of trying i am still unable to get the search function to work. i can add,del,edit records, but what i cannot do is search for a record. the search popup appears and whatever i type nothing returns in grid. i have tried using search.php and server.php that come with demo files for 3.5.3 but still no luck. what i am not sure of is how to pass to the results page. for example, i have my grid as this:

jQuery(document).ready(function(){
jQuery("#list").jqGrid({
    url:'new.php',
    datatype: 'json',
    mtype: 'POST',
    colNames:['Id','Title', 'Director', 'Year','Bond','Budget'],
    colModel :[
      {name:'id', hidden:true, search: true, index:'id', width:55},
      {name:'title', index:'title', editable:true, width:90},
      {name:'director', index:'director', editable:true, width:90},
      {name:'year', index:'year', editable:true, width:80},
      {name:'bond', index:'bond', width:80, search:true, editable:true},
      {name:'budget', index:'budget', editable:true, width:80, sortable:false}

    ],
    pager: '#pager',
    rowNum:10,
    rowList:[10,20,30],
   imgpath: 'themes/ui-lightness/images',
    sortname: 'id',
   autowidth: true,
   height:'100%',
   altRows: true,
   search: true,
    sortorder: 'desc',
   editurl: 'edit.php?q=1',
    viewrecords: true,
    caption: 'My first grid'
}).navGrid('#pager',{search: true, add:true, addtext:"Add a record", edittext:"Edit a record", deltext:"delete a record", searchtext:"Find files", refreshtext:"Refresh records", alertcap: "You must select a row to edit"}, //options
{closeAfterEdit: true, jqModal: true, reloadAfterSubmit:true}, // edit options
{closeAfterAdd: true,reloadAfterSubmit:true}, // add options
{reloadAfterSubmit:true}, // del options
{onInitializeSearch : function() {alert('just a test');}} // search options
);
});

as you can see, i have main grid 'new.php' and the editurl 'edit.php' where do i put url to search.php so i can send the vars to display them? or is there some other way? hope you can help because this is driving me crazy. i have read the wiki and the docs and can see how the search would work, but just cannot figure it out. many thanks