The select is correctly returning {“rows”:[{“StartDate”:”2016-12-08 00:00:00″,”Enddate”:”2017-12-08 00:00:00″,”CurrentCredits”:”6″}]} but not displaying it.

Changed to

$cid = jqGridUtils::GetParam(‘CYOTestQuestionID’);
$tType = jqGridUtils::GetParam(‘TestType’);
$grid->setAfterCrudAction(‘edit’, “update tr_cyotestquestion set TestTypeID = ? WHERE CYOTestQuestionID=?”,array($tType,$cid));

as the editable cell being changed is TestType not TestTypeID which is used in the search dialog. Unfortunately still does not produce an UPDATE statement let alone work.

Changed the relevant part to

// This command is executed after edit
$cid = jqGridUtils::GetParam(‘CYOTestQuestionID’);
$tType = jqGridUtils::GetParam(‘TestTypeID’);
$grid->setAfterCrudAction(‘edit’, “update tr_cyotestquestion set TestTypeID = ? WHERE CYOTestQuestionID=?”,array($tType,$cid));

 

but still not firing setAfterCrudAction.

 

Please let me know what I am missing.

Thank you Will, I made a mistake above. The character that is breaking the paging is a hex 92 (not char(92)) being Word’s apostrophe which is not a utf-8 character.

Our MySQL database columns are all utf8mb4_unicode_ci and hold these non utf-8 characters but pulling them into jqGrid breaks the display of records.

Does jqGrid have a method of handling these characters?

I have the search returning the value correctly now but the record that meets the search criteria where LicenceTyepID = 1 is not being displayed. lqGrid.log returns

[1] => Array
(
[time] => 2017-12-19 08:46:57
[query] => SELECT * FROM (SELECT organisation.OrganisationID,
organisation.Name,
organisation.Active,
organisation.PostCode,
replace(organisation.Notes,”’”,”‘”) as Notes,
organisation.DemoAccount,
organisation.LicenceEndDate as LEAfter,
organisation.LicenceEndDate as LEBefore,
licencetype.Name as LicenceType,
organisation.ParentOrganisationID,
organisation.WebServiceID,
” as CandidateEmail,
” as UserName
from organisation
inner join licencetype on organisation.LicenceTypeID = licencetype.LicenceTypeID where 1 = 1 ) gridsearch WHERE (LicenceType = ?) ORDER BY Name asc LIMIT 0, 20
[data] => Array
(
[0] => 1
)

[types] =>
[fields] =>
[primary] =>
[input] =>
)

)

|My code as follows:-

if(!isset($superLogin) ) {
$superLogin = jqGridUtils::GetParam(‘sLogin’,”);
}
$sLogin = $superLogin;

if(!isset($aLicTypes) ) {
$aLicTypes = jqGridUtils::GetParam(‘licType’,”);
}
$licType = $aLicTypes;

if(!isset($aPO) ) {
$aPO = jqGridUtils::GetParam(‘PO’,”);
}
$PO = $aPO;

if(!isset($aWS) ) {
$aWS = jqGridUtils::GetParam(‘WS’,”);
}
$WS = $aWS;

// Create the jqGrid instance
$grid = new jqGridRender($conn);

// enable debugging
$grid->debug = true;

$where = $where.$sOrgs;

// the actual query for the grid data
$grid->SelectCommand = “SELECT organisation.OrganisationID,
organisation.Name,
organisation.Active,
organisation.PostCode,
replace(organisation.Notes,\”’\”,\”‘\”) as Notes,
organisation.DemoAccount,
organisation.LicenceEndDate as LEAfter,
organisation.LicenceEndDate as LEBefore,
licencetype.Name as LicenceType,
organisation.ParentOrganisationID,
organisation.WebServiceID,
” as CandidateEmail,
” as UserName
from organisation
inner join licencetype on organisation.LicenceTypeID = licencetype.LicenceTypeID “.$where;

// set the ouput format to json
$grid->dataType = ‘json’;

// Set the url from where we obtain the data
$grid->setUrl(‘getOrganisations.php?sLogin=’.$superLogin.’&licType=’.$aLicTypes.’&PO=’.$aPO.’&WS=’.$aWS);

// Let the grid create the model
$grid->setColModel(null);

// Set some grid options
$grid->setGridOptions(array(
“caption”=>”Organisations”,
“rowNum”=>20,
“sortname”=>”Name”,
“rowList”=>array(20,100,200),
“height”=>600,
“width”=>960
));

$grid->addCol(array(“name”=>”act”),”last”);
$grid->addCol(array(“name”=>”not”),”last”);
$grid->addCol(array(“name”=>”pog”),”last”);
$grid->addCol(array(“name”=>”lic”),”last”);
$grid->addCol(array(“name”=>”log”),”last”);
$grid->addCol(array(“name”=>”deb”),”last”);
$grid->addCol(array(“name”=>”sup”),”last”);
$grid->addCol(array(“name”=>”sho”),”last”);
$grid->addCol(array(“name”=>”edi”),”last”);
$grid->addCol(array(“name”=>”del”),”last”);

// Change some property of the field(s)
$grid->setColProperty(“OrganisationID”, array(“label”=>”ID”, “width”=>30, “hidden”=>true));
$grid->setColProperty(“Name”, array(“label”=>”Organisation”, “width”=>400, “sorttype”=>”string”, “resizable”=>true, “searchoptions”=>array(“sopt”=>array(“cn”,”nc”,”bw”,”bn”,”eq”,”ne”,”in”,”ni”))));
$grid->setColProperty(“Active”, array(“label”=>”Active”, “width”=>25, “hidden”=>true, “stype”=>”select”,”searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”), “value”=>” : ;0:inactive;1:active”)));
$grid->setColProperty(“PostCode”, array(“label”=>”Post Code “, “width”=>25, “hidden”=>true, “sortable”=>false, “searchoptions”=>array(“searchhidden”=>true)));
$grid->setColProperty(“Notes”, array(“label”=>” “, “width”=>25, “hidden”=>true, “sortable”=>false));
$grid->setColProperty(“act”, array(“label”=>” “, “width”=>25, “sortable”=>false));
$grid->setColProperty(“not”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“pog”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“lic”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“log”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“deb”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“sup”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“sho”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“edi”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“del”, array(“label”=>” “, “width”=>25, “sortable”=>false, “search”=>false));
$grid->setColProperty(“DemoAccount”, array(“label”=>”Demonstration Account”, “width”=>30, “hidden”=>true, “stype”=>”select”,”searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”), “value”=>” : ;0:no;1:yes”)));
$grid->setColProperty(“LEAfter”, array(“label”=>”Licence ends on or after”, “width”=>30, “hidden”=>true, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“ge”))));
$grid->setColProperty(“LEBefore”, array(“label”=>”Licence ends on or before”, “width”=>30, “hidden”=>true, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“le”))));
$grid->setColProperty(“LicenceType”, array(“label”=>”Licence Type”, “width”=>30, “hidden”=>true, “stype”=>”select”, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”), “value”=>”{$licType}”)));
$grid->setColProperty(“ParentOrganisationID”, array(“label”=>”Parent Organisation”, “width”=>30, “hidden”=>true, “stype”=>”select”, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”), “value”=>”{$PO}”)));
$grid->setColProperty(“WebServiceID”, array(“label”=>”WebService”, “width”=>30, “hidden”=>true, “stype”=>”select”, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”), “value”=>”{$WS}”)));
$grid->setColProperty(“CandidateEmail”, array(“label”=>”Candidate Email”, “width”=>30, “hidden”=>true, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”))));
$grid->setColProperty(“UserName”, array(“label”=>”User Name”, “width”=>30, “hidden”=>true, “searchoptions”=>array(“searchhidden”=>true, “sopt”=>array(“eq”))));

$grid->setDatepicker(‘LEAfter’, array(“showOn”=>”focus”), true, true);
$grid->setDatepicker(‘LEBefore’, array(“showOn”=>”focus”), true, true);
$grid->setUserTime(“d/m/Y”);
$grid->SetUserDate(“d/m/Y”);
$grid->setDbDate(‘Y-m-d H:i:s’);
$grid->setDbTime(‘Y-m-d H:i:s’);
$grid->datearray = array(‘LEAfter’,’LEBefore’);

$loadevent = <<<LOADCOMPLETE
function(rowid){
var ids = jQuery(“#organisationlist”).getDataIDs();
for(var i=0;i<ids.length;i++){
var cl = ids;
var rowd = $(“#organisationlist”).getRowData(ids);
var a = rowd.Active;
var n = rowd.Notes;
var p = rowd.ParentOrganisationID;
if (a == 1) {
ac = ‘</ids>’;
} else {
ac = ‘</ids>’;
}
jQuery(“#organisationlist”).setRowData(ids,{act:ac});

if (n != ”) { // has notes
nt = ‘</ids>’;
} else {
nt = ” “;
}
jQuery(“#organisationlist”).setRowData(ids,{not:nt});

if (p == cl) { // is parent organisation
po = ‘</ids>’;
} else {
po = ” “;
}
jQuery(“#organisationlist”).setRowData(ids,{pog:po});

cp = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{lic:cp});

de = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{log:de});

bg = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{deb:bg});

sp = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{sup:sp});

rm = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{sho:rm});

ed = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{edi:ed});

dl = ‘</ids>’;
jQuery(“#organisationlist”).setRowData(ids,{del:dl});

}
}
LOADCOMPLETE;

$grid->setGridEvent(“loadComplete”,$loadevent);

// Enable navigator
$grid->navigator = true;
// Disable some actions
$grid->setNavOptions(‘navigator’, array(“excel”=>true,”add”=>false,”edit”=>false,”del”=>false,”view”=>false,”multipleSearch”=>true,”multipleGroup”=>true,”recreateFilter”=>true));
//$grid->setNavOptions(‘search’,array(“showQuery”=>true));

// Run the script
$grid->renderGrid(‘#organisationlist’,’#organisationpager’,true, null, null,true,true,true);
$conn = null;

What am I doing wrong please.

Same issue if Notes contains

“This is a test of cr

to see how the import handles it

along with characters £$%^&()*&%$

etc.”

Hi Will

First grid code:-

<?php
include_once ‘../includes/jq-config.php’;

// include the jqGrid Class
require_once “../javascript/Guriddo/php/PHPSuito/jqGrid.php”;
// include the PDO driver class
require_once “../javascript/Guriddo/php/PHPSuito/DBdrivers/jqGridPdo.php”;
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

// Create the jqGrid instance
$grid = new jqGridRender($conn);

// enable debugging
$grid->debug = true;

// the actual query for the grid data
$grid->SelectCommand = “SELECT
tr_cyoquestion.CYOQuestionID,
tr_cyoquestion.Title,
tr_cyoquestion.CuratorReference,
” as Category,
” as Type,
” as Subtest1,
” as Subtest2
FROM
tr_cyoquestion”;

// set the ouput format to json
$grid->dataType = ‘json’;

// Let the grid create the model
$grid->setColModel(null);

// Set the url from where we obtain the data
$grid->setUrl(‘getQuestions.php’);

// Set some grid options
$grid->setGridOptions(array(
“caption”=>”Available Questions”,
“rowNum”=>20,
“sortname”=>”Title”,
“altRows”=>true,
“rowList”=>array(20,100,200),
“height”=>250,
“width”=>950
));

$grid->addCol(array(“name”=>”act”),”last”);

// Change some property of the field(s)
$grid->setColProperty(“CYOQuestionID”, array(“label”=>”ID”, “width”=>30));
$grid->setColProperty(“Title”, array(“label”=>”Title”, “width”=>170));
$grid->setColProperty(“CuratorReference”, array(“label”=>”Curator”, “width”=>120));
$grid->setColProperty(“Question”, array(“label”=>”Question”,”width”=>30, “hidden”=>true, “searchoptions”=>array(“searchhidden”=>true)));
$grid->setColProperty(“Category”, array(“label”=>”Category”, “width”=>30, “hidden”=>true));
$grid->setColProperty(“Type”, array(“label”=>”Type”, “width”=>30, “hidden”=>true));
$grid->setColProperty(“Subtest1”, array(“label”=>”Subtest 1”, “width”=>30, “hidden”=>true));
$grid->setColProperty(“Subtest2”, array(“label”=>”Subtest 2”, “width”=>30, “hidden”=>true));
$grid->setColProperty(“act”, array(“label”=>”Actions”, “width”=>50));

//$grid->setSubGrid(“getQuestionText.php”, array(‘Question’), array(860), array(‘left’));

$loadevent = <<<LOADCOMPLETE
function(rowid){
var ids = jQuery(“#qavlist”).getDataIDs();
for(var i=0;i<ids.length;i++){
var cl = ids;
var rowd = $(“#qavlist”).getRowData(ids);
sq = ‘</ids>’;

jQuery(“#qavlist”).setRowData(ids,{act:sq});
}
}
LOADCOMPLETE;

$grid->setGridEvent(“loadComplete”,$loadevent);

$drag = <<< DRAG
jQuery(‘#qavlist’).jqGrid(‘gridDnD’,{connectWith:’#qtlist,#qfolist’});
DRAG;
$grid->setJSCode($drag);

// Enable navigator
$grid->navigator = true;
// Disable some actions
$grid->setNavOptions(‘navigator’, array(“excel”=>true,”add”=>false,”edit”=>false,”del”=>false,”view”=>false));

// Run the script
$grid->renderGrid(‘#qavlist’,’#qavpager’,true, null, null,true,true,true);
//$conn = null;

?>

 

Second grid code:-

<?php

include_once ‘../includes/jq-config.php’;

// include the jqGrid Class
require_once “../javascript/Guriddo/php/PHPSuito/jqGrid.php”;
// include the PDO driver class
require_once “../javascript/Guriddo/php/PHPSuito/DBdrivers/jqGridPdo.php”;

if(!isset($TestID) ) {
$TestID = jqGridUtils::GetParam(‘id’,”);
}
$id = $TestID;
if(!isset($sTypes) ) {
$sTypes = jqGridUtils::GetParam(‘types’,”);
}
$types = $sTypes;
if(!isset($sSubtest1) ) {
$sSubtest1 = jqGridUtils::GetParam(‘subtest1’,”);
}
$subtest1 = $sSubtest1;
if(!isset($sSubtest2) ) {
$sSubtest2 = jqGridUtils::GetParam(‘subtest2’,”);
}
$subtest2 = $sSubtest2;

// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

// Create the jqGrid instance
$grid = new jqGridRender($conn);

// Set the url from where we obtain the data
$grid->setUrl(‘getTestQuestions.php?id=’.$TestID.’&types=’.$sTypes);

// enable debugging
//$grid->debug = true;

// the actual query for the grid data
$grid->SelectCommand = “SELECT
tr_cyotestquestion.CYOTestQuestionID,
tr_cyoquestion.Title,
tr_cyoquestion.CuratorReference,
tr_cyocategory.Title AS Category,
testtype.Name as TestType,
(SELECT
subtest.Name AS Name1
FROM
subtesttype
INNER JOIN subtest ON subtesttype.SubTestTypeID =
subtest.SubTestTypeID
INNER JOIN test ON test.SubTestTypeID = subtesttype.SubTestTypeID
where test.TestID = tr_cyotestquestion.TestID
and subtest.TestSubTestID = tr_cyotestquestion.SubTestID) as SubTest1,
(SELECT
subtest.Name AS Name1
FROM
subtesttype
INNER JOIN subtest ON subtesttype.SubTestTypeID =
subtest.SubTestTypeID
INNER JOIN test ON test.SubTestTypeID = subtesttype.SubTestTypeID
where test.TestID = tr_cyotestquestion.TestID
and subtest.TestSubTestID = tr_cyotestquestion.SubTest2ID) as SubTest2
FROM
tr_cyotestquestion
INNER JOIN tr_cyoquestion ON tr_cyotestquestion.CYOQuestionID = tr_cyoquestion.CYOQuestionID
LEFT JOIN tr_cyoquestioncategory ON tr_cyoquestion.CYOQuestionID = tr_cyoquestioncategory.CYOQuestionID
LEFT JOIN tr_cyocategory ON tr_cyoquestioncategory.CYOCategoryID = tr_cyocategory.CYOCategoryID
INNER JOIN testtype ON tr_cyotestquestion.TestTypeID = testtype.TestTypeID
WHERE
tr_cyotestquestion.FixedOrder IS NULL and
tr_cyotestquestion.TestID = “.$id;

// set the ouput format to json
$grid->dataType = ‘json’;

// Let the grid create the model
$grid->setColModel();

// Set some grid options
$grid->setGridOptions(array(
“caption”=>”Random Order questions for Test “.$id,
“rowNum”=>500,
“sortname”=>”Title”,
“altRows”=>true,
“rowList”=>array(500,1000),
“cellEdit”=>true,
“height”=>200,
“width”=>950
));

$grid->addCol(array(“name”=>”tact”),”last”);

// Change some property of the field(s)
$grid->setColProperty(“CYOTestQuestionID”, array(“label”=>”ID”, “width”=>30));
$grid->setColProperty(“Title”, array(“label”=>”Title”, “width”=>150, “editable”=>false));
$grid->setColProperty(“CuratorReference”, array(“label”=>”Curator”, “width”=>120, “editable”=>true));
$grid->setColProperty(“Category”, array(“label”=>”Category”, “width”=>50, “editable”=>true));
$grid->setColProperty(“TestType”, array(“label”=>”Type”, “width”=>70, “editable”=>true, “edittype”=>”select”, “formatter”=>”select”, “editoptions”=>array(“value”=>”{$types}”)));
$grid->setColProperty(“SubTest1”, array(“label”=>”Subtest 1”, “width”=>60, “editable”=>true, “edittype”=>”select”, “formatter”=>”select”, “editoptions”=>array(“value”=>”{$subtest1}”)));
$grid->setColProperty(“SubTest2”, array(“label”=>”Subtest 2”, “width”=>60, “editable”=>true, “edittype”=>”select”, “formatter”=>”select”, “editoptions”=>array(“value”=>”{$subtest2}”)));
$grid->setColProperty(“tact”, array(“label”=>”Actions”, “width”=>50));

//$grid->setSubGrid(“getQuestionTextNO.php”, array(‘Question’), array(860), array(‘left’));

$loadevent = <<<LOADCOMPLETE
function(rowid){
var ids = jQuery(“#qtlist”).getDataIDs();
for(var i=0;i<ids.length;i++){
var rowd = $(“#qtlist”).getRowData(ids);
var cl = ids;
st = ‘</ids>’;
jQuery(“#qtlist”).setRowData(ids,{tact:st});
}
}
LOADCOMPLETE;

$grid->setGridEvent(“loadComplete”,$loadevent);

$dragdrop = <<< DRAG
jQuery(‘#qavlist’).jqGrid(‘gridDnD’,{connectWith:’#qavlist,#qtlist,#qfolist’});
DRAG;
$grid->setJSCode($dragdrop);

// Enable navigator
$grid->navigator = true;
// Disable some actions
$grid->setNavOptions(‘navigator’, array(“excel”=>true,”add”=>false,”edit”=>false,”del”=>false,”view”=>false));

// Run the script
$grid->renderGrid(‘#qtlist’,’#qtpager’,true, null, null,true,true,true);
$conn = null;

?>

Icons display correctly but when clicking on icon in second grid the icon disappears and the text

appears where 933 is the id of that row’s item.

Clicking on another icon and the text disappears and that rows’s icon reappears and the next icon clicked disappears to be replace with the text. I do not see any duplicate references that may make this happen other than both are calling the same function. Actually changing that so each calls a function of a different name makes no difference.