Skip to content

# 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.

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

### 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.

### 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)

### 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.

### 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>
    <?php echo $out?>
    </div>

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

### 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.

### 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

### 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)

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

### Q) How to use 'select2' comboxbox like function with Grid 4 PHP?

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({ ... });

### Q) How to use 'qtip2' with Grid 4 PHP?

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>

### 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

### 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)
    };

### 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

### 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/jquery-ui@master/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

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