Guriddo » Topic: Intermittent problem with paging

Intermittent problem with paging

Guriddo
Intermittent problem with paging

This topic has 6 replies, 2 voices, and was last updated 7 years, 3 months ago by guriddo.support.

Creator

Topic
December 18, 2017 at 4:23 pm #126495

sagana
Participant

In several grids I have the issue where the first page of say 20 items are correctly displayed but on going to the the next page I am presented with an empty alert box and ‘Loading’ message on screen. I click OK on the alert and click for the next page and get page 3 now having missed page 2. I can go to the last page and back to the beginning without a problem. Code as follows:-

<?php

require 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);

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;

// the actual query for the grid data
$grid->SelectCommand = “SELECT organisation.OrganisationID,
organisation.Name,
organisation.Active,
organisation.PostCode,
organisation.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 “;

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

?>
Creator

Topic

Viewing 6 replies - 1 through 6 (of 6 total)

Author

Replies
December 18, 2017 at 5:17 pm #126497

sagana
Participant

This appears to be happening when a column contains text with a character 92 in it as in Brown’s 4272 6F77 6E92 73. It is the apostrophe created by Apple and Word.

December 18, 2017 at 6:05 pm #126498

sagana
Participant

Same issue if Notes contains

“This is a test of cr

to see how the import handles it

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

etc.”

December 18, 2017 at 6:29 pm #126499

sagana
Participant

The Notes field is utf8mb4_unicode_ci

December 19, 2017 at 12:20 pm #126502

guriddo.support
Participant

Hello,

You may want to look at this documentation

Another possible solution is to use the grid option autoencode set to true, but you should take care in this case since:

When set to true the option encodes (html encode) the incoming (from server) and posted data (from editing modules). By example < will be converted and posted to <

Guriddo Support Team

December 19, 2017 at 1:48 pm #126505

sagana
Participant

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?

December 19, 2017 at 3:07 pm #126506

guriddo.support
Participant

I have not tested this, but could you please try the following two approaches

First one. Change

... $conn->query("SET NAMES utf8");

1
2

...
$conn->query("SET NAMES utf8");

To

... $conn->query("SET NAMES utf8mb4");

1
2

...
$conn->query("SET NAMES utf8mb4");

The Second one (which I think is better set the char set to the connection, something like

... $dbh = new PDO('mysql:charset=utf8mb4...');

1
2

...
$dbh = new PDO('mysql:charset=utf8mb4...');

Please let us know if the problem is resolved

Kind Regards,
Will

Guriddo Support Team
Author

Replies