Skip to content

Column Settings FAQs

Column Settings FAQs

Q) How to integrate autocomplete function?

Step1: Select ID and Data both in select command. (e.g. client_id, clients.name)

$g->select_command = "SELECT id, invdate, invheader.client_id, clients.name as cid, amount, note FROM invheader
INNER JOIN clients on clients.client_id = invheader.client_id
";

Step2: Place ID field in grid, (optionally hidden)

// field that will be updated by autocomplete
$col = array();
$col["title"] = "client_id";
$col["name"] = "client_id";
$col["width"] = "10";
$col["editable"] = true;
$col["hidden"] = true;
$cols[] = $col;

Step3: Place DATA field in grid, with autocomplete formatter.

// normal textbox, with autocomplete enabled
$col = array();
$col["title"] = "Client";
$col["name"] = "cid";
$col["dbname"] = "clients.name"; // this is required as we need to search in name field, not id
$col["width"] = "100";
$col["align"] = "left";
$col["search"] = true;
$col["editable"] = true;
$col["formatter"] = "autocomplete"; // autocomplete
$col["formatoptions"] = array(  "sql"=>"SELECT client_id, name FROM clients",
                                "search_on"=>"name",
                                "update_field" => "client_id");

It will search in passed SQL for autocomplete, and selection ID will be set in field client_id.

^ Top

Q) How can i get unqiue values in autocomplete?

You can change the input SQL to use GROUP BY clause. For example, to get unique 'release', you can use following query.

$col["formatoptions"] = array(
                                "sql"=>"SELECT * FROM (SELECT id as k, release as v FROM tbl_releases GROUP BY release) o",
                                "search_on"=>"v",
                                "update_field" => "id");

^ Top

Q) How can i show lookup dropdown from other table (i.e. linked with FK data)

First step is to select the table which you want to use in grid, which will include table join.

$g->select_command = "SELECT id, invdate, invheader.client_id, amount, note FROM invheader
                        INNER JOIN clients on clients.client_id = invheader.client_id
                        ";

After that, you need to define column like this.

$col = array();
$col["title"] = "Client";
$col["name"] = "client_id"; // same as aliased name (fk)
$col["dbname"] = "clients.name"; // this is required as we need to search in name field, not id
...
$col["edittype"] = "select"; // render as select

# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients");
$col["editoptions"] = array("value"=>$str);

$col["formatter"] = "select"; // show label in grid, instead of value

$cols[] = $col;

Refer dropdown.php for working demo.

^ Top

Q) How can i set default value to some field?

You can set it by following code.

// This will set textbox with 10 as default value.
$col["editoptions"] = array("size"=>20, "defaultValue"=>'10');

// This will set current date as default value.
$col["editoptions"] = array("size"=>20, "defaultValue"=> date("Y-m-d H:i:s") );

Make sure that column has editable => true, You can make hidden => true if you dont want to show it (like in case of session id data)

^ Top

Q) How to populate other column, based on previous columns?

Solution 1: In following example, text3 will be calculated based on text1 & text2. You can use onblur event and do your own JS code to set value. You can also inspect the ID of elements of form using firebug.

$col = array();
$col["title"] = "Height";
$col["name"] = "height";
$col["editable"] = true;
$col["editoptions"] = array("onblur" => "update_field()");
$cols[] = $col;

$col = array();
$col["title"] = "Width";
$col["name"] = "width";
$col["editable"] = true;
$col["editoptions"] = array("onblur" => "update_field()");
$cols[] = $col;

$col = array();
$col["title"] = "Area";
$col["name"] = "area";
$col["editable"] = true;
$cols[] = $col;

and in html, put following js function to calculate field.

<script>
function update_field()
{
    // for inline (any tag input, textarea or select)
    jQuery('[name="area"].editable').val(
                        parseFloat(jQuery('[name="width"].editable').val()) *
                        parseFloat(jQuery('[name="height"].editable').val())
                        );

    // for dialog (any tag input, textarea or select)
    jQuery('[name="area"].FormElement').val(
                        parseFloat(jQuery('[name="width"].FormElement').val()) *
                        parseFloat(jQuery('[name="height"].FormElement').val())
                        );
}
</script>

You can inspect the field id.using firebug and use jquery functions to disable / enable desired field. To know jquery selector:

http://easycaptures.com/fs/uploaded/1017/9892108434.png
http://easycaptures.com/fs/uploaded/1017/9617668813.png

Solution 2: This is also doable on server side with callback functions: e.g. We want to have sum of field a and b in field c which may or may not be shown on form.

$e["on_update"] = array("update_field", null, true);
$e["on_insert"] = array("update_field", null, true);
$g->set_events($e);

function update_field($data)
{
    $data["params"]["c"] = $data["params"]["a"] + $data["params"]["b"]
}

Whatever value of a & b are posted back they will be summed and used for column c in sql query internally executed by library.

^ Top

Q) How to use ENTER intead of TAB in moving to next form input?

Following JS code will emulate ENTER as TAB. Put this script code before echo $out;

<script>
    var opts = {
        'loadComplete': function () {
            $('body').on('keydown', 'input, select, textarea', function(e) {
                var self = $(this)
                  , form = self.parents('form:eq(0)')
                  , focusable
                  , next
                  ;
                if (e.keyCode == 13) {
                    focusable = form.find('input,a,select,button,textarea').filter(':visible');
                    next = focusable.eq(focusable.index(this)+1);
                    if (next.length) {
                        next.focus();
                    } else {
                        form.submit();
                    }
                    return false;
                }
            });
        }
    };
</script>
...
<div style="margin:10px">
<?php echo $out?>
</div>

^ Top

Q) How to set focus of some other field while adding record?

This JS event enables you to set customization in add/edit forms just after they are displayed. For e.g.

$grid["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#date").focus(); }';
$g->set_options($grid);

^ Top

Q) How to make a field only editable on Add Dialog, otherwise un-editable?

We'll set the "name" column as editable->false by default. Then we'll enable it on add dialog using following grid settings.

$grid["add_options"]["beforeInitData"] = "function(formid) { $('#list1').jqGrid('setColProp','name',{editable:true}); }";
$grid["add_options"]["afterShowForm"] = "function(formid) { $('#list1').jqGrid('setColProp','name',{editable:false}); }";
...
$g->set_options($grid);

Where "list1" is ID of grid passed in render() function, and "name" is the column to make editable on Add Dialog.

^ Top

Q) How to mask a field, for example like this: (NN) NNNN-NNNN ? N stands for number.

You can pick input jquery id selectors, and link it using any lib.

Library 1: For e.g. http://igorescobar.github.io/jQuery-Mask-Plugin/

Step1: Add JS lib

<script src="//cdnjs.cloudflare.com/ajax/libs/jquery.mask/0.9.0/jquery.mask.min.js"></script>

Step2: Set field integration, usually $col['name'] is ID of html element

$opt["add_options"]["afterShowForm"] = 'function(formid) { jQuery("#amount").mask("000.00"); }';
$opt["edit_options"]["afterShowForm"] = 'function(formid) { jQuery("#amount").mask("000.00"); }';
$g->set_options($opt);

For more options, refer: http://igorescobar.github.io/jQuery-Mask-Plugin/

Library 2: For e.g. http://github.com/RobinHerbots/jquery.inputmask

Step1: Add JS lib

<!-- masked fields-->
<script src="js/jquery.inputmask.js"></script>
<script src="js/jquery.inputmask.date.extensions.js" ></script>
<script src="js/jquery.inputmask.numeric.extensions.js"></script>

<!-- OR use github link -->
<script src="//rawgit.com/RobinHerbots/jquery.inputmask/3.x/dist/jquery.inputmask.bundle.js"></script>

Step2: Set field integration, usually $col['name'] is ID of html element

$grid["edit_options"]["afterShowForm"] = 'function(list1) {
    jQuery("#StartDate").inputmask("mm/dd/yyyy", {yearrange: { minyear: 2010, maxyear: 2020 }});
    jQuery("#Worth").inputmask("currency", {prefix:"$ ",groupSeparator:",",alias:"numeric",placeholder:"0",autoGroup:!0,digits:2,digitsOptional:!1,clearMaskOnLostFocus:!1});
 }';

For more options, refer: http://robinherbots.github.io/jquery.inputmask

^ Top

Q) How to change column title at runtime ?

Following JS code will change the column title in grid, where list1 is grid id and 'name` is column name.

jQuery("#list1").setLabel("name","Client Name");

You can also use HTML in column title. (for e.g. setting image as column title)

^ Top

Q) How to GeoNames lib for City, Country, Code autocomplete lookup?

Simple include Geonames JS lib in html

<script src="http://tompi.github.io/jeoquery/jeoquery.js" type="text/javascript"></script>

And use dataInit property for autocomplete lookup:

$col = array();
$col["title"] = "City Name";
$col["name"] = "city";
$col["editable"] = true;
$col["width"] = "40";
$col["editoptions"]["dataInit"] = "function(o){  jQuery(o).jeoCityAutoComplete(); }";
$cols[] = $col;

^ Top

Q) How to use 'select2' comboxbox like function with PHPGrid?

1) Added 'dataInit' line with select column:

$col = array();
$col["title"] = "Client";
$col["name"] = "client_id";
$col["editable"] = true;
$col["edittype"] = "select";
$col["editoptions"]["dataInit"] = "function(){ setTimeout(function(){ $('select[name=client_id]').select2({width:'80%', dropdownCssClass: 'ui-widget ui-jqdialog'}); },200); }";
...
$cols[] = $col;

2) Added select2 JS/CSS files:

<link href="//cdnjs.cloudflare.com/ajax/libs/select2/3.4.6/select2.css" rel="stylesheet"/>
<script src="//cdnjs.cloudflare.com/ajax/libs/select2/3.4.6/select2.min.js"></script>

You can further add 'select2' options by referring it's documentation, and placing param with in dataInit code: .select2({ ... });

^ Top

Q) How to use 'qtip2' with PHPGrid?

1) Include JS/CSS

<link rel="stylesheet" href="//cdn.jsdelivr.net/qtip2/2.2.1/jquery.qtip.min.css">
<script src="//cdn.jsdelivr.net/qtip2/2.2.1/jquery.qtip.min.js"></script>

2) Connect onload event

$grid["loadComplete"] = "function(){ connect_qtip(); }";
$g->set_options($grid);

3) Connect qtip2 e.g. for all title attributes

<style>
.qtip-content{
    font-family: tahoma, helvetica, 'sans-serif';
}
</style>

<script>
function connect_qtip()
{
    jQuery('[title]').qtip({
                                position: {
                                my: 'bottom left',  // Position my top left...
                                at: 'top left' // at the bottom right of...
                                }
                        });
}
</script>

^ Top

Q) How to fill dropdown with different values w.r.t. record?

You can do it using dataInit event handler.

$col["editoptions"]["dataInit"] = "function(){ setTimeout(function(){ load_dd(); },200); }";

Based on some input value (e.g. invdate=2015-02-18) you can change options of dropdown.

<script>
function load_dd()
{
    var grid = $('#list1');
    var selectValues;

    if ($('input[name=invdate]').val() == '2015-02-18')
        selectValues = { "1": "test 1", "2": "test 2" };
    else
        selectValues = { "3": "test 3", "4": "test 4" };

    $('select[name=client_id]').html('');

    $.each(selectValues, function(key, value) {
         $('select[name=client_id]')
              .append($('<option>', { value : key })
              .text(value));
    });
}
</script>

Sample code: http://hastebin.com/eqedajegov.php

^ Top

Q) How to customize the toolbar features of Html editor (CKEditor)?

You can customize buttons in config.js of CK editor. (\lib\js\ckeditor\config.js) CK editor docs: http://docs.ckeditor.com/#!/guide/dev_toolbar

Sample config.js contents:

CKEDITOR.editorConfig = function( config ) {
    // Define changes to default configuration here.
    // For complete reference see:
    // http://docs.ckeditor.com/#!/api/CKEDITOR.config

    // The toolbar groups arrangement, optimized for two toolbar rows.
    config.toolbarGroups = [
        { name: 'clipboard',   groups: [ 'clipboard', 'undo' ] },
        { name: 'editing',     groups: [ 'find', 'selection' ] },
        { name: 'links' },
        { name: 'insert' },
        { name: 'forms' },
        { name: 'tools' },
        { name: 'document',    groups: [ 'document', 'doctools' ] },
        { name: 'others' },
        '/',
        { name: 'basicstyles', groups: [ 'basicstyles', 'cleanup' ] },
        { name: 'paragraph',   groups: [ 'list', 'indent', 'blocks', 'align', 'bidi' ] },
        { name: 'styles' },
        { name: 'colors' },
    ];

    // Remove some buttons provided by the standard plugins, which are
    // not needed in the Standard(s) toolbar.
    config.removeButtons = 'Underline,Subscript,Superscript';

    // Set the most common block elements.
    config.format_tags = 'p;h1;h2;h3;pre';

    // Simplify the dialog windows.
    config.removeDialogTabs = 'image:advanced;link:advanced';

    config.extraPlugins='onchange';
    config.skin = 'bootstrapck';
    // config.width = '300px';
    config.minimumChangeMilliseconds = 100; // 100 milliseconds (default value)
};

^ Top

Q) Frozen column does not work. What are the limitations?

The following limitations tell you when frozen columns can not be set-up

  • When SubGrid is enabled
  • When cellEdit is enabled
  • When inline edit is used - the frozen columns can not be edit.
  • When sortable columns are enabled - grid parameter sortable is set to true or is function
  • When scroll is set to true or 1
  • When Data grouping is enabled
  • When footer row (footerrow paremeter) is enabled

^ Top

Q) How to change language of datepicker control (localization)?

Add following code in html head, after downloading and setting lang file path. e.g.

<script src="//cdn.jsdelivr.net/gh/jquery/[email protected]/ui/i18n/datepicker-ar.js"></script>
<script>
$.datepicker.setDefaults( $.datepicker.regional[ "ar" ] );
</script>

All language files can be downloaded from: https://github.com/jquery/jquery-ui/tree/master/ui/i18n

^ Top

Q) How to set minimum date of datepicker control?

You can set opts array (in formatoptions) which can have these options: http://api.jqueryui.com/datepicker/

$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d.m.Y',
                                "opts" => array("changeYear" => true, "dateFormat"=>'yy-mm-dd', "minDate"=>"15-07-08"));

^ Top