fezonhead on navigator search does not refresh grid

Fri, 03 Dec 2010 13:19:17 +0200

I've written a very basic ajax module to test out the jqGrid navigator. ADD, DELETE and UPDATE work fine but when I try SEARCH the grid does not refresh and when I quit out of the search modal and try an EDIT, DELETE or UPDATE the changes are made to the database but not reflected on the grid. Also the record navigation does not work. I can only get these working again if I press the REFRESH button.

I also have the same problem with the SEARCH function on the downloadable sample files for NAVIGATOR.

The database I'm using is a simple mySQL with a table called MINGRID with fields id, name, number being integer, text, integer.

Here's my HTML file [mingrid.html]:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

<title>My First Grid</title>

html, body {

margin: 0;

padding: 0;

font-size: 75%;

}

</style>

$(document).ready(function(){

$("#ngrid").jqGrid({

url:'ajaxread.php',

datatype: 'xml',

mtype: 'GET',

colNames:['Id','Name', 'Number'],

colModel :[

{name:'id', index:'id', width:55, search: false},

{name:'name', index:'name', width:90, editable: true},

{name:'number', index:'number', width:80, align:'right', editable: true}

pager: '#pager',

height: 300,

width: 600,

rowNum:20,

rowList:[10,20,40,100],

sortname: 'name',

sortorder: 'desc',

viewrecords: true,

caption: 'MinGrid',

editurl: 'ajaxcud.php'

}).navGrid('#pager');

});

</script>

</head>

<body>

<h1>Testing sandbox</h1>

</div>

</body>

</html>

_____________________________________________________________

and here's my PHP file for the standard AJAX call.... (ajaxread.php)...

<?php

require_once('Connections/dbfn.php'); // dbfn.php also has logcall function to record info to a log file for debugging

// Get basic variables

$page = $_REQUEST['page'];

$limit = $_REQUEST['rows'];

$sidx = $_REQUEST['sidx'];

$sord = $_REQUEST['sord'];

// if no index is passed then use the first column

if(!$sidx) $sidx =1;

// Set the COUNT SQL query depending on whether it's a search call or not

$search=$_REQUEST['_search'];

if ($search == 'true') {

$searchstr=$_REQUEST['searchString'];

$searchfield=$_REQUEST['searchField'];

$searchop=$_REQUEST['searchOper'];

switch ($searchop) {

case eq:

$modop = '=';

break;

case le:

$modop = '<=';

break;

case ne:

$modop = '<>';

break;

case lt:

$modop = '<';

break;

case gt:

$modop = '>';

break;

case ge:

$modop = '>=';

break;

case bw:

$modop = 'LIKE';

break;

case bn:

$modop = 'NOT LIKE';

break;

case in:

$modop = 'LIKE';

break;

case ni:

$modop = 'NOT LIKE';

break;

case ew:

$modop = 'LIKE';

break;

case en:

$modop = 'NOT LIKE';

break;

case cn:

$modop = 'LIKE';

break;

case nc:

$modop = 'NOT LIKE';

break;

default:

$modop = 'LIKE';

}

$sqlcount=sprintf("SELECT COUNT(*) AS count FROM mingrid WHERE %s %s %s", $searchfield, $modop, $searchstr);

}

else {

$sqlcount="SELECT COUNT(*) AS count FROM mingrid";

}

logcall('count string - '.$sqlcount);

// calculate the number of rows for the query. We need this for paging the result

$result = mysql_query($sqlcount);

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

}

logcall('Row count - '.$count.' Total pages - '.$total_pages);

// check start and limit are within bounds

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

// set the SELECT SQL query depending on whether it's a search call or not

if ($search == 'true') {

$sqlcall="SELECT id, name, number FROM mingrid WHERE $searchfield $modop $searchstr ORDER BY $sidx $sord LIMIT $start , $limit";

}

else {

$sqlcall="SELECT id, name, number FROM mingrid ORDER BY $sidx $sord LIMIT $start , $limit";

}

logcall('select query - '.$sqlcall);

// do the SQL query

$result = mysql_query($sqlcall) or die("Couldn't execute query.".mysql_error());

logcall('Result of query - '.$result);

// Set the header information.

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

// set up the XML

while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {

$s .= "<row id='". $row['id']."'>";

$s .= "<cell>". $row['id']."</cell>";

$s .= "<cell>". $row['name']."</cell>";

$s .= "<cell>". $row['number']."</cell>";

$s .= "</row>";

}

$s .= "</rows>";

// record and write the XML

logcall("Returned XML:\n ".$s);

echo $s;

____________________________________________________

...here's the PHP file for the UPDATE, DELETE and ADD function calls... (ajaxcud.php)...

<?php

require_once('Connections/dbfn.php');

// get the parameters

$id=$_REQUEST['id'];

$oper=$_REQUEST['oper'];

$name=$_REQUEST['name'];

$number=$_REQUEST['number'];

// Set the SQL script as per the oper request

switch ($oper){

case edit:

$sqlcmd=sprintf("UPDATE mingrid SET %s='%s', %s='%s' WHERE %s=%u",

"name",$name,

"number",$number,

"id",$id);

break;

case add:

$sqlcmd=sprintf("INSERT INTO mingrid (%s, %s) VALUES ('%s', '%s')",

"name", "number",

$name, $number);

break;

case del:

$sqlcmd=sprintf("DELETE FROM mingrid WHERE %s=%u",

"id",$id);

break;

default:

}

// record query

logcall('ajaxcud executed SQL - '.$sqlcmd);

// execute query

mysql_query($sqlcmd) or die(mysql_error());

________________________________________________

and (finally) my connections PHP (dbfn.php)...

<?php

function logcall($qs){

//write to the log file

$filename = "log.txt";

$fd = fopen ($filename , "r");

$fstring = file_get_contents($filename);

fclose($fd);

$fd = fopen ($filename , "w");

$fstring .= "\n** ";

$fstring .= $qs;

fwrite ($fd , $fstring);

fclose($fd);

}

$dbhost = 'localhost';

$dbuser = 'root';

$dbpassword = '';

$database = 'mingrid';

$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error());

mysql_select_db($database) or die("Error connecting to db.");

_________________________________________________

As you can see, I am logging the activity of the PHP calls and that all seems to be working fine.

I would very much appreciate any advice as I've been working hard on this problem for several days now and it is driving me wild. I can't seem to find anyone else with the same issue so it is obviously me making some simple error.

Thanks in advance for your help.

jQuery Grid Plugin - jqGrid - Topic: navigator search does not refresh grid

fezonhead on navigator search does not refresh grid