Misc
Q) Performance of large tables in PHPGrid?
Performance of loading data in grid consist of 2 parts.
1) Client side: This is usually due to browser rendering capability of large html table, and JS engine that fill html.
This can be improved by enabling virtual scrolling of grid. It loads data only when it is required by scrolling.
As per docs, When enabled, the pager elements are disabled and we can use the vertical scrollbar to load data.
$opt["scroll"] = true;
$g->set_options($opt);
2) Server side: This depends on database optimization factors, such as indexes, usage of ‘like’ queries etc.
Best approach to optimize is to create indexes of your most searchable fields and avoid ‘%blah%’ contains query
which never use indexes in mysql. After creating indexes, you can try SELECT queries in phpmyadmin and track loading time.
If phpgrid loading time is still slow, drop me a message with your SQL and i’ll check the internals.
Q) How to use phpgrid with concurrent users?
First solution is to use excel-view (cellEdit) mode. In this way, only the changed cell is submitted to server and not the whole row data.
You can refer demos/appearence/excel-view.php for working demo.
Second, If you want application based row level locking in grid:
1) Introduce a new field ‘last_modified’, make it hidden on grid and editable. It will store the timestamp of row update.
2) Implement an on_update event handler and check if:
i) Fetch the edited row db table row, using select query
i) Check if posted back ‘last_modified’ value is different from the one in database against same row id
ii) If Yes, You can show phpgrid_error(‘Already edited’) otherwise, go with the update.
Working demo code: //pastebin.com/Ds4WrD4z
Q) How to show some custom success message after bulk update?
Now you can push custom html messages from bulk edit handler. Instead of ‘records updated’ message,
you can have your custom message. This only works with custom bulk-edit.
function update_data($data)
{
// If bulk operation is requested, (default otherwise)
if ($data["params"]["bulk"] == "set-desc")
{
$selected_ids = $data["id"]; // e.g. the selected values from grid 5,7,14 (where "id" is field name of first col)
$str = $data["params"]["data"];
// here you can code your logic to do bulk processing
mysql_query("UPDATE invheader SET note = '$str' WHERE id IN ($selected_ids)");
phpgrid_msg("Download Zip: <a target='_blank' href='//google.com'>//google.com</a>",0);
die;
}
}
first param is message, second is autoclose (0/1).
Q) Purchasing a license is a one time payment or monthly?
It’s one time fees that includes limited period of subscription for updates and technical support.
If subscription period is expired, product will keep working as before.
However for new updates and technical support you will be required to renew the license.
Q) How to pay with Paypal? I’m always redirected to 2Checkout, and I don’t have a VISA-card.
When you visit 2checkout page, there is paypal option in last step of payment page. See image.
Q) How to set local timezone in date operations?
This can be set by php default function. For more refer php docs.
date_default_timezone_set('Asia/Karachi');
Q) How to convert (db stored) UTC time to Local time?
This can be done by using mysql convert_tz function in query.
// you can provide custom SQL query to display data - convert_tz for local time
$g->select_command = "SELECT i.id, CONVERT_TZ(invdate,'+00:00','+5:00') as invdate , c.name,
i.note, i.total, i.closed FROM invheader i
INNER JOIN clients c ON c.client_id = i.client_id";
Q) How to use multiselect filter?
Step1: Include js/css files
<link rel="stylesheet" href="//www.erichynds.com/examples/jquery-ui-multiselect-widget/jquery.multiselect.css">
<link rel="stylesheet" href="//www.erichynds.com/examples/jquery-ui-multiselect-widget/jquery.multiselect.filter.css">
<script src="//www.erichynds.com/examples/jquery-ui-multiselect-widget/src/jquery.multiselect.js"></script>
<script src="//www.erichynds.com/examples/jquery-ui-multiselect-widget/src/jquery.multiselect.filter.js"></script>
Step2: Set search type for multiselect filter column
// multi-select in search filter
$col["stype"] = "select-multiple";
$col["searchoptions"]["value"] = $str;
Refer: demos/integration/multiselect-filter.php
Q) How to load grid based on $_POST data from other page?
The grid is loaded with 2 server calls.
1) load the columns of grid.
2) do an ajax call, to load data of grid.
Now, if you want to pass data from external form, it is available for step1. But not there in 2nd ajax call, as it is not posted.
Solution is to put the POST variable in session and use it from session for step2.
e.g.
if (!empty($_POST["personid"]))
{
$_SESSION["personid"] = $_POST["personid"];
}
$pid = $_SESSION["personid"];
and use $pid in your SQL.
Q) How to save data in grid using external form?
You can apply following JS function on click event of external form.
You need to change POST data request[‘column_name’] = ‘col_value’; in javascript code, ‘#list1’ is grid id in this example.
function updateData()
{
// call ajax to update date in db
var request = {};
request['oper'] = 'edit';
// data to POST
request['id'] = '1';
request['company'] = 'test company';
var grid = jQuery('#list1');
jQuery.ajax({
url: grid.jqGrid('getGridParam','url'),
dataType: 'html',
data: request,
type: 'POST',
error: function(res, status) {
jQuery.jgrid.info_dialog(jQuery.jgrid.errors.errcap,'<div class="ui-state-error">'+ res.responseText +'</div>',
jQuery.jgrid.edit.bClose,{buttonalign:'right'});
},
success: function( data ) {
// reload grid for data changes
grid.jqGrid().trigger('reloadGrid',[{jqgrid_page:1}]);
}
});
}
Q) How to set custom mesage when no records are found?
You can set following JS before echo $out.
<script>
jQuery.jgrid.defaults.emptyrecords = "There are no records for your selection";
</script>
<div>
<?php echo $out?>
</div>
Grid Appearence
Q) How can I set the width in this grid (even with horizontal scrollbar), i do not want to set screen size width but custom width size.
You need to set following params:
$grid["autowidth"] = false;
$grid["shrinkToFit"] = false; // dont shrink to fit on screen
$grid["width"] = "600";
...
$g->set_options($grid);
If you dont specify the width property, it will be the sum of all columns width.
To make scroll bar visible, the sum of column width must increase grid width.
Q) Change font size & height of cell
Update following css classes to change presentation.
<style>
.ui-jqgrid {font-size:14px; font-family:"tahoma";}
.ui-jqgrid tr.jqgrow td {height: 25px; padding:0 5px;}
</style>
Q) How to do word-wrap (fit) content in grid cells?
Update following css in page to change cell wrapping.
<style>
.ui-jqgrid tr.jqgrow td
{
vertical-align: top;
white-space: normal !important;
padding:2px 5px;
}
</style>
To make word wrapping in view record dialog, add following css:
<style>
.ui-jqdialog-content .CaptionTD
{
vertical-align: top;
}
.ui-jqdialog-content .form-view-data
{
white-space: normal;
}
</style>
Also check this link for frozen columns with cell wrap.
//stackoverflow.com/questions/8686616/how-can-i-get-jqgrid-frozen-columns-to-work-with-word-wrap-on
Q) How can i add caption along with icons of add,edit,delete.
You can set it using following config on jqgrid() object. For example:
$grid->navgrid["param"]["addtext"] = "Add Invoice";
$grid->navgrid["param"]["edittext"] = "Edit Invoice";
$grid->navgrid["param"]["deltext"] = "Delete Invoice";
$grid->navgrid["param"]["searchtext"] = "Search";
$grid->navgrid["param"]["refreshtext"] = "Refresh";
$grid->navgrid["param"]["viewtext"] = "View Invoice";
Altenrate, You need to put following additional JS and CSS code. You can change ‘Add User’ with your desired caption.
<script type="text/javascript">
$.jgrid.nav.addtext = "Add User";
$.jgrid.nav.edittext = "Edit User";
$.jgrid.nav.deltext = "Delete User";
</script>
After adding captions, the alignment got it out, so put this css.
<style type="text/css">
.ui-jqgrid .ui-jqgrid-pager .ui-pg-div
{
float: left;
line-height: 18px;
padding: 2px 2px 2px 0;
position: relative;
}
</style>
Q) How to enable footer summary row?
Please do these changes to enable footer summary row.
Step1: Enable footer row in options
$grid["footerrow"] = true;
$g->set_options($grid);
// set onload js event
$e["js_on_load_complete"] = "grid_onload";
$grid->set_events($e);
Step2: Add custom JS code as mentioned. ‘list1’ is the identifier for grid. In function ‘getCol’, 2nd param ‘field1’ is the name of column, which will show the summary data. Valid options for mathoperation (4th param) are - ‘sum, ‘avg’, ‘count’.
<script>
function grid_onload()
{
var grid = $("#list1"),
sum = grid.jqGrid('getCol', 'field1', false, 'sum');
grid.jqGrid('footerData','set', {field1: 'Total: '+sum});
}
</script>
If one need to show complete table’s total in footer, refer following example.
$g->select_command = "SELECT id,invdate,total,(SELECT sum(total) from invheader) AS table_total FROM invheader";
Define a column with name table_total, and in footer data, use that table_total field.
<script>
function grid_onload()
{
var grid = $("#list1");
sum = grid.jqGrid('getCol', 'table_total');
grid.jqGrid('footerData','set', {total: 'Total: '+sum[0]});
}
</script>
Q) How to remove buttons and text from toolbar?
Do following config to show/remove items in toolbar
$opt["rowList"] = array();
$opt["pgbuttons"] = false;
$opt["pgtext"] = null;
$opt["viewrecords"] = false;
$g->set_options($opt);
$g->set_actions(array(
"add"=>false, // allow/disallow add
"edit"=>false, // allow/disallow edit
"delete"=>false, // allow/disallow delete
"view"=>false, // allow/disallow view
"refresh" => false, // show/hide refresh button
"search" => false, // show single/multi field search condition (e.g. simple or advance)
)
);
Q) How to do grouping on more than 1 field?
It’s not fully stable, but you can try following config.
$grid["grouping"] = true;
$grid["groupingView"] = array();
$grid["groupingView"]["groupField"] = array("field1","field2"); // specify column name to group listing
$grid["groupingView"]["groupDataSorted"] = array(true,true); // show sorted data within group
$grid["groupingView"]["groupSummary"] = array(true,true); // work with summaryType, summaryTpl, see column: $col["name"] = "total";
$grid["groupingView"]["groupColumnShow"] = array(false,false); // either show grouped column in list or not (default: true)
$grid["groupingView"]["groupText"] = array("<b>{0} - {1} Item(s)</b>"); // {0} is grouped value, {1} is count in group
$grid["groupingView"]["groupOrder"] = array("asc"); // show group in asc or desc order
$grid["groupingView"]["groupCollapse"] = true; // Turn true to show group collapse (default: false)
$grid["groupingView"]["showSummaryOnHide"] = true; // show summary row even if group collapsed (hide)
Refer demos/appearence/grouping.php for more help.
Q) How to maintain vertical scroll position after grid reload?
First you need to get the handler for load complete.
$e["js_on_load_complete"] = "do_onload";
$g->set_events($e);
Then in callback, you can have following code.
<script>
function do_onload()
{
if (jQuery(window).data('phpgrid_scroll'))
jQuery('div.ui-jqgrid-bdiv').scrollTop(jQuery(window).data('phpgrid_scroll'));
jQuery('div.ui-jqgrid-bdiv').scroll(function(){
jQuery(window).data('phpgrid_scroll',jQuery('div.ui-jqgrid-bdiv').scrollTop());
});
}
</script>
Q) How to scroll to a particular row value after grid reload?
First you need to get the handler for load complete.
$e["js_on_load_complete"] = "do_onload";
$g->set_events($e);
Then in JS callback, you can have following code. This will iterate all rows and find row with name ‘Victoria Ashworth’.
It then uses row’s first column ‘client_id’ to get id and focus it with scrollTop().
<script>
function do_onload()
{
var rows = $('#list1').getRowData();
for (var i=0;i<rows.length;i++)
if (rows[i].name == 'Victoria Ashworth')
{
var t = jQuery('tr.jqgrow[id='+rows[i].client_id+']').position().top;
jQuery('div.ui-jqgrid-bdiv').scrollTop(t);
}
}
</script>
Q) How to highlight some cell based on row data ?
You can do it onload event of grid. First you need to connect event, and then write your desired logic in JS code.
In Grid config, set event callback.
$e["js_on_load_complete"] = "do_onload";
...
$grid->set_events($e);
In callback function, write your code.
function do_onload()
{
var grid = $("#list1");
var ids = grid.jqGrid('getDataIDs');
for (var i=0;i<ids.length;i++)
{
var id=ids[i];
if (grid.jqGrid('getCell',id,'qty') == '0')
{
grid.jqGrid('setCell',id,'price','','not-editable-cell'); // make cell uneditable
grid.jqGrid('setCell',id,'price','',{'background-color':'lightgray'}); // make bgcolor to gray
}
}
}
Q) How to show horizontal scroll bar in grid (when there are many columns) ?
You can enable horizontal scroll bar within grid by setting these config.
$opt["width"] = 600; // some fixed width
$opt["autowidth"] = false;
$opt["shrinkToFit"] = false;
...
$g->set_options($opt);
Assuming that you have enough columns with defined width, to make scroll bar appear.
Q) How to apply formatting of rows, based on 2 or more fields.
You can use a load complete callback handler, and put conditional formatting JS code in it.
For instance, refer following example, where list1 is grid id.
// PHP part
$e["js_on_load_complete"] = "do_onload";
...
$grid->set_events($e);
// HTML part
<script>
function do_onload(ids)
{
if(ids.rows) jQuery.each(ids.rows,function(i){
// format row when gender is 'male' and company name starts with 'Bl'
if (this.gender.toLowerCase() == 'male' && this.company.indexOf('Bl') != -1)
{
jQuery('#list1 tr.jqgrow:eq('+i+')').css('background','inherit').css({'background-color':'#FBEC88', 'color':'green'});
}
}
}
</script>
Q) How to show dropdown/select in buttons toolbar?
Following JS code, will display dropdown with toolbar buttons. It’s upto your logic to show desired options and onchange function.
Here ‘list1’ is assumed to be grid id.
<script>
jQuery(window).load(function(){
jQuery('#list1_pager_left').append ('<div style="padding-left: 5px; padding-top:2px; float:left"><select><option>None</option></select></div>');
});
</script>
Q) How to retain page number on page refresh?
You can have something like following, to preserve page number.
$grid = new jqgrid();
if (isset($_GET["jqgrid_page"]))
$_SESSION["jqgrid_page"] = $_GET["jqgrid_page"];
$opt["caption"] = "Clients Data";
$opt["page"] = intval($_SESSION["jqgrid_page"]);
$grid->set_options($opt);
Q) How to persist selection, data ordering, column order, page number, selection in grid, expand same subgrid on page refresh?
Include following JS plugins files in your page, and add ‘opts’ setting before ‘echo $out’ as mentioned below:
<script src="//raw.github.com/andris9/jStorage/master/jstorage.js" type="text/javascript"></script>
<script src="//raw.github.com/douglascrockford/JSON-js/master/json2.js" type="text/javascript"></script>
<script src="//bitbucket.org/rpgkaiser/jqgridstate/raw/5e813d0b6c7241705848c37dc068239aac53e3b9/jqGrid.state.js" type="text/javascript"></script>
<script>
var opts = {
"stateOptions": {
storageKey: "gridStateCookie",
columns: true,
filters: false,
selection: true,
expansion: true,
pager: true,
order: true
}
};
</script>
<div style="margin:10px">
<?php echo $out?>
</div>
Q) How to apply more advanced conditional formatting?
You can set onload event in PHP code, and enable ‘reloadedit’ option
// set js onload event
$e["js_on_load_complete"] = "grid_onload";
$g->set_events($e);
// required to format rows after editing
$opt["reloadedit"] = true;
...
$g->set_options($opt);
Then in HTML code, you can have custom conditional logic in javascript handler.
In this code, list1 is grid id. You can also use php’s equivalent ‘$g->id’ to reference it.
<script>
function grid_onload(ids)
{
if(ids.rows)
jQuery.each(ids.rows,function(i)
{
// if col1 = yes and col2 = n/a, format row
if (this.col1.toLowerCase() == 'yes' && this.col2.toLowerCase() == 'n/a')
{
// highlight row
jQuery('#list1 tr.jqgrow:eq('+i+')').css('background-image','inherit').css({'background-color':'#FBEC88', 'color':'red'});
}
// if col3 = 1, format cell. 'aria-describedby=list1_col3' is 'gridid_colname' convention to identify cell.
if (parseInt(this.col3) == 1)
{
// highlight cell
jQuery('#list1 tr.jqgrow:eq('+i+')').css('background-image','inherit');
jQuery('#list1 tr.jqgrow:eq('+i+') td[aria-describedby=list1_col3]').css('background','inherit').css({'background-color':'#FBEC88', 'color':'red'});
}
});
}
</script>
To make non-editable column as gray, e.g. If ‘list1’ is grid id and ‘name’ is non-editable column:
<script>
function grid_onload(ids)
{
if(ids.rows)
jQuery.each(ids.rows,function(i)
{
// if col3 = 1, format cell. 'aria-describedby=list1_col3' is 'gridid_colname' convention to identify cell.
if (jQuery("#list1").getColProp("name").editable == false)
{
// highlight cell
jQuery('#list1 tr.jqgrow:eq('+i+')').css('background-image','inherit');
jQuery('#list1 tr.jqgrow:eq('+i+') td[aria-describedby=list1_name]').css('background','inherit').css({ 'color':'gray'});
}
});
}
</script>
Q) How to make single row selection, keeping multiselect option?
Following code will enable single selection, in multiselect mode.
PHP code config:
$e["js_on_load_complete"] = "do_onload";
$g->set_events($e);
JS code config:
<script>
function do_onload()
{
jQuery(".jqgrow").click(function(){ jQuery("#list1").jqGrid('resetSelection'); this.checked = true; });
}
</script>
Q) How to increase font size of grid?
Following css override will adjust fonts. For any other left area, you can inspect using firebug.
<style>
.ui-jqgrid {
font-family: "tahoma","verdana","sans serif";
font-size: 12px;
}
.ui-jqgrid .ui-pg-table td {
font-size: 12px;
}
</style>
Q) How to stop reload grid after edit submit button?
Following config will stop whole grid reload, and will only update edited row.
$grid["edit_options"]["reloadAfterSubmit"]=false;
...
$g->set_options($grid);
Q) How to attach Keyboard Shortcuts with operations like Add, Search etc?
You can bind your shortcut keys by adding following lib:
<script src="//cdn.jsdelivr.net/jquery.hotkeys/0.8b/jquery.hotkeys.js"></script>
More help on keys are available on its site: //github.com/tzuryby/hotkeys
Next, you will find the ID of button to attach shortcut. Use firebug inspect to hover that button and it would show ID of the element.
e.g. To add ‘insert’ key with ‘+’ button in toolbar
<script>
// insert key to add new row
$(document).bind('keyup', 'insert', function(){
jQuery('#add_list1').click();
});
</script>
Q) How to increase overall Grid & Toolbar font size?
Following on-page styles will increase font-size to 14px (overriding the base css).
This inclues grid font, dialogs and toolbar. This will be helpful when using in mobile devices.
<style>
/* increase font & row height */
.ui-jqgrid *, .ui-widget, .ui-widget input, .ui-widget select, .ui-widget textarea, .ui-widget button { font-size:14px; }
.ui-jqgrid tr.jqgrow td { height:30px; }
/* big toolbar icons */
.ui-pager-control .ui-icon, .ui-custom-icon { zoom: 125%; -moz-transform: scale(1.25); -webkit-zoom: 1.25; -ms-zoom: 1.25; }
.ui-jqgrid .ui-jqgrid-pager .ui-pg-div span.ui-icon { margin: 0px 2px; }
.ui-jqgrid .ui-jqgrid-pager { height: 28px; }
.ui-jqgrid .ui-jqgrid-pager .ui-pg-div { line-height: 25px; }
</style>
Q) How to show animated progress bar instead of text ‘Loading…’?
You can replace text with some string containing html tag.
$grid["loadtext"] = "Loading...";
$g->set_options($grid);
Q) How to make button for next record selection in grid ?
Following code will bind JS code with button. where list1 is grid id.
<input type="button" value="Next" id="btnNext">
<script>
$('#btnNext').click(function () {
var grid = $("#list1").jqGrid();
var selectedRow = grid.getGridParam('selrow');
if (selectedRow == null) return;
var ids = grid.getDataIDs();
var index = grid.getInd(selectedRow);
if (ids.length < 2) return;
index++;
if (index > ids.length)
index = 1;
grid.setSelection(ids[index - 1], true);
});
</script>