Column Options
Column Options
We can also customize each column properties. Following are the parameters, that can be passed to customize column definition on grid.
Caption shown on grid
$col["title"] = "Details";
DB Field name (or alias) in sql query / table
$col["name"] = "view_more";
DB Field Name (table.field)
In case of field name ambiguity b/w tables, set as table.fieldname
$col["dbname"] = "c.id";
Width of the Column
$col["width"] = "20";
// to set exact pixel width
$col["fixed"] = true;
$col["width"] = "20";
Editable (true,false)
$col["editable"] = false;
Viewable (true,false)
When the option is set to false the column does not appear in view Form
$col["viewable"] = false;
Frozen (true,false)
When the option is set to true the column will be freezed while scrolling. Column must be first column of grid OR after another freezed column.
$col["frozen"] = true;
The following limitations tell you when frozen columns can not be set-up:
- When TreeGrid is enabled
- When SubGrid is enabled
- When cellEdit is enabled
- When inline edit is used - the frozen columns can not be editable.
- 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
Allow null (true,false)
If db fields allows null and we want to save (NULL) instead of "". Defaults to false
$col["isnull"] = true;
Has Auto-increment Column (true,false)
First field in columns is by-default skipped from INSERT query (assuming to be primary key and auto increment). If you wish to include it in INSERT sql (non auto increment) you can set:
$col["autoid"] = false;
Resizable (true,false)
$col["resizable"] = true;
Position (numeric)
With position property you can set column order sequence (in numbers) with your desired column. e.g. Following will create a new virtual column on second position of grid. It starts from 0 however setting it 0 (first column) should only be done if the content uniquely identify each row (e.g PK of table). Otherwise edit,delete options may behave unexpected.
$col = array();
$col["title"] = "Report";
$col["name"] = "reportcol";
$col["editable"] = false;
$col["position"] = 2;
$col["template"] = "<a href='#'>Report</a>";
$cols[] = $col;
$g->set_columns($cols,true);
Output: https://i.imgur.com/gaZlysb.png
Edit Type & Options
This option let us select the control we want to render when editing this field. All possible options are in following snippet. Defaults to text. In editoptions we can set all the possible attributes for this field's control.
Render as textarea on edit
$col["edittype"] = "textarea";
$col["editoptions"] = array("rows"=>2, "cols"=>20);
Render as checkbox,
With these values "checked_value:unchecked_value"
$col["edittype"] = "checkbox";
$col["editoptions"] = array("value"=>"Yes:No");
To make checkbox already in checked state while adding record $col["editoptions"] = array("value"=>"Yes:No", defaultValue=> 'Yes');
Render as textbox
With size 20, and initial value in textbox to 10
$col["editoptions"] = array("size"=>20, "defaultValue"=>'10');
R##### ender as password textbox
it should be used with $col["formatter"] = "password"; to hide password in listing
$col["edittype"] = "password";
Render as select (dropdown)
With these values "key:value;key:value;key:value"
$col["edittype"] = "select";
$col["editoptions"] = array("value"=>'10:$10;20:$20;30:$30;40:$40;50:$50');
// For multiselect, you probably need to write custom on_update handler
$col["editoptions"] = array("value"=>'10:$10;20:$20;30:$30;40:$40;50:$50', "multiple" => true);
// In case you want to use different delimiter (:) and separator (;), you can override
$col["editoptions"]["delimiter"] = ":";
$col["editoptions"]["separator"] = ";";
Render as database lookup (dropdown)
Property editoptions indexes are: foreign key table, foreign key id, label to show in dropdown
$col["edittype"] = "lookup";
$col["editoptions"] = array("table"=>"employees", "id"=>"employee_id", "label"=>"concat(first_name,' ',last_name)");
Above will create a query like: select employee_id as k, concat(firstname,' ',last_name) as v from employees. You can also provide your own query for dropdown by:
$cols[$i]["editoptions"]["sql"] = "select employee_id as k, concat(firstname,' ',last_name) as v from employees";
Render as button
$col["edittype"] = "button";
$col["editoptions"] = array("value"=>'Click Me');
Render Radio buttons as edittype
Radio buttons can be shown by custom method.
$col = array();
$col["title"] = "Closing Rate";
$col["name"] = "closed";
$col["width"] = "30";
$col["editable"] = true;
$col["align"] = "center";
$col["editoptions"]["dataInit"] = "function(o){edit_as_radio(o);}";
$cols[] = $col;
... and in html section, we can define custom edit-type display
<script>
function edit_as_radio(o)
{
setTimeout(function(){
jQuery(o).hide();
jQuery(o).parent().append('<input title="0" type="radio" name="rd_closed" value="0" onclick="jQuery(\'#total\').val(0);"/> 0 <input title="5" type="radio" name="rd_closed" value="5" onclick="jQuery(\'#total\').val(5);"/> 5 <input title="10" type="radio" name="rd_closed" value="10" onclick="jQuery(\'#total\').val(10);"/> 10');
},100);
}
</script>
Multiple checkboxes as edittype
$col = array();
$col["title"] = "Closed";
$col["name"] = "closed";
$col["width"] = "50";
$col["editable"] = true;
$col["editoptions"]["dataInit"] = "function(o){edit_as_custom(o);}";
$cols[] = $col;
and in html, add display function that will update the selection in main field (hidden)
<script>
function edit_as_custom(o)
{
setTimeout(function(){
jQuery(o).parent().append('<input type="checkbox" name="rd_closed" value="1" onclick="set_checkbox_value();"/> Option 1 <input type="checkbox" name="rd_closed" value="2" onclick="set_checkbox_value();"/> Option 2 <input type="checkbox" name="rd_closed" value="3" onclick="set_checkbox_value();"/> Option 3');
jQuery(o).css('display','none');
// here you can also read #closed value (if set) and set in checkboxes using jquery for edit mode
},100);
}
function set_checkbox_value()
{
jQuery('#closed').val( jQuery('input[name=rd_closed]:checked').map(function(){return this.value;}).get() );
}
</script>
Readonly Column
You can also set certain column as readonly on edit dialog, and editable on add dialog.
$col["editrules"]["readonly"] = true;
To make column readonly in both add and edit dialog, use following:
// shows defaultValue only on add dialog and readonly
$col["editoptions"] = array("defaultValue"=>"Test Value","readonly"=>"readonly", "style"=>"border:0");
If you need to make a column non-editable when it contain some specific data, you can also put that condition using readonly-when. Refer column-access.php.
$col = array();
$col["title"] = "Gender";
$col["name"] = "gender";
$col["editable"] = true;
$col["editrules"] = array("required"=>true, "readonly"=>true, "readonly-when"=>array("==","male"));
$cols[] = $col;
For advance readonly logic, use callback function
$col["editrules"] = array("required"=>true, "readonly"=>true, "readonly-when"=>"check_client");
// html side .. JS callback function
<script>
// readonly gender conditional function - when return true, field will be readonly
function check_client(formid)
{
client_id = jQuery("input[name=client_id]:last, select[name=client_id]:last",formid).val();
client_id = parseInt(client_id);
if (jQuery.inArray(client_id,[3,6,7,8,9]) != -1)
return true;
}
</script>
Column Form Option
This option is valid only in form editing. The purpose of these options is to reorder the elements in the form and to add some information before and after the editing element.
elmprefix if set, a text or html content appears before the input element
elmsuffix string if set, a text or html content appears after the input element
label string if set, this replace the name from colNames array that appears as label in the form.
rowpos determines the row position of the element (again with the text-label) in the form; the count begins from 1
colpos determines the column position of the element (again with thelabel) in the form beginning from 1
If you plan to use this object in colModel with rowpos and colpos properties it is recommended that all editing fields use these properties.
$col["formoptions"] = array("elmprefix"=>'(*)', "rowpos"=>"1", "colpos"=>"2");
To mark a field as required, you can use
$col["formoptions"] = array("elmsuffix"=>'<font color=red> *</font>');
To auto split dialog columns in 2 column layout or so, set:
$opt["autocolumn"] = 2;
$g->set_options($opt);
Column Formatters
This will format this column as date (and will show date picker control) on add or edit operations.
$col["formatter"] = "date";
$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d/m/Y');
This will format this column as date time (and will show date time picker control) on add or edit operations.
$col["formatter"] = "datetime";
$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d/m/Y');
Complete date formatting shortcode can be found on this link: http://www.php.net/manual/en/function.date.php
By default, datepicker does not perform 'contains' search. If you wish to enable, you need to set following with date column. This will disable datepicker.
// contains search
$col["searchoptions"]["sopt"] = array("cn");
Format of contains search is yyyy-mm-dd, which can be changed using 'dbname' property and mysql format_date function.
// search date in format Jan 23, 2008
$col["dbname"] = "date_format(invdate,'%b %d, %Y')";
To show column as checkbox,
$col["formatter"] = "checkbox";
To display select box (dropdown) label instead of value,
$col["formatter"] = "select";
For password fields,
$col["formatter"] = "password";
You can also set format options for numeric and currency data.
$col["formatter"] = "number";
$col["formatoptions"] = array("thousandsSeparator" => ",",
"decimalSeparator" => ".",
"decimalPlaces" => 2);
$col["formatter"] = "currency";
$col["formatoptions"] = array("prefix" => "$",
"suffix" => '',
"thousandsSeparator" => ",",
"decimalSeparator" => ".",
"decimalPlaces" => 2);
Render as image,
$col["formatter"] = "image";
$col["formatoptions"] = array("src"=>'http://test.com/image.jpg');
For custom formatter, e.g. image display
$col["formatter"] = "function(cellval,options,rowdata){ return '<img src=\"'+cellval+'\" />'; }";
$col["unformat"] = "function(cellval,options,cell){ return $('img', cell).attr('src'); }";
For custom formatter of percentage display
$col["formatter"] = "function(cellval,options,rowdata){ return cellval*100+'%'; }";
$col["unformat"] = "function(cellval,options,cell){ return cellval.replace('%','')/100; }";
For custom formatter of fix height row (having html
content)
$col["formatter"] = "function(cellval,options,rowdata){ return '<div style=\"height:25px; overflow:hidden;\">'+cellval+'</div>'; }";
$col["unformat"] = "function(cellval,options,cell){ return jQuery(cell).children('div').html(); }";
For raw link formatter
$col["formatter"] = "function(cellval,options,rowdata){ return '<a target=\"_blank\" href=\"http://'+cellval+'\">'+cellval+'</a>'; }";
$col["unformat"] = "function(cellval,options,cell){ return $('a', cell).attr('href').replace('http://',''); }";
In unformatter, if you want to use row data you can use getRowData, e.g:
// here list1 is your grid id & total_weight is row column to use
$col["unformat"] = "function(cellval, options, cell) {
var rowdata = $('#list1').getRowData(options.rowId);
return cellval.replace('%','')*rowdata['total_weight']/100;
}";
Date Format
Governs format of editrules {date:true} fields. Determines the expected date format for that column. Uses a PHP-like date formatting. Currently "/", "-", and "." are supported as date separators. Valid formats are: y,Y,yyyy for four digits year YY, yy for two digits year m,mm for months d,dd for days
$col["datefmt"] = "Y-m-d";
Text alignment
Possible values are left, right or center
$col["align"] = "center";
Searchable (true,false)
Is searching allowed on this field. Possible values are true or false.
$col["search"] = false;
Dropdown in Filter Row
We need to set stype and searchoptions to enable dropdown search in autofilter.
// 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["stype"] = "select";
$col["searchoptions"] = array("value" => $str, "separator" => ":", "delimiter" => ";");
Column Search Operators
Optionally set limited search operators (e.g. bw = begins with, 'eq','ne','lt','le','gt','ge','bw','bn','in','ni','ew','en','cn','nc')
This will limit search operators for this columns to begins-with, equal, cotains
$col["searchoptions"]["sopt"] = array("bw","eq","cn");
If you pass single operator, only that will be used in autofilter and override default contains search
$col["searchoptions"]["sopt"] = array("eq");
Sortable (true,false)
Sorting column can be enabled or disabled by setting true or false.
$col["sortable"] = false;
Sort type
Defines the type of sort for column. Only used when grid loads with array
/*
Possible values:
int/integer - for sorting integer
float/number/currency - for sorting decimal numbers
date - for sorting date
text - for text sorting
*/
$col["sorttype"] = int;
Column Content as Hyperlink
We can use exiting db-field value of that row in URL pattern. For e.g. if we have a grid column named 'id', we can insert it's value in URL using {id}. Here we set, http://domain.com?param={id} given that, there is a column with $col["name"] = "id" exist.
$col["template"] = "<a target='_blank' href='http://google.com/?q={id}'>{id}</a>";
There is a limitation that you cannot make first column as hyperlink, as it is usually PK and used in INSERT/UPDATE. Alternate solution could be to select same field 2 times in SQL, and make first as hidden and second as hyperlink.
Static Content
If the column is static data (non database driven), we can set it with default param. We can set custom HTML too, for e.g. <img> or <div> etc.
$col["template"] = "Custom Text";
Dynamic Content
We can also use {field} replacement in default parameter. Here is an example for custom column to show bar graph. Where bar is a column alias from SQL statement.
$col = array();
$col["title"] = "Performance";
$col["name"] = "bar";
$col["width"] = "40";
$col["align"] = "left";
$col["search"] = false;
$col["sortable"] = false;
$col["template"] = "<div style='width:{bar}px; background-color:navy; height:14px'></div>";
$cols[] = $col;
In same way, we can embed dynamic images and other media (flv or swf) in grid.
Conditional Content
We can also provide certain condition, based on which either row data will be displayed. NOTE: Use single quote for condition, and $row will have all columns data, to use in condition.
$col["condition"] = array('$row["total"] < 100', $data1, $data2);
Now if the condition is met, $data1 will be displayed otherwise $data2. You can also {field} replacement in $data1 & $data2. Refer example below.
# no new line in this html, only space. otherwise it may break ui of grid
$buttons_buy = "<a target='_blank' href='http://www.amazon.com?id={id}'>Buy</a>";
$buttons_try = "<a target='_blank' href='http://www.google.com?id={id}'>Try</a>";
$col["condition"] = array('$row["total"] > 10', $buttons_buy, $buttons_try);
For extended conditional data, you can also have callback function, that will allow you to display based on row data. For e.g.
$col["on_data_display"] = array("display_keyword","");
function display_keyword($data)
{
$kw = $data["keyword_name"];
$numKeywords = count(explode("\n",$pass));
if ($numKeywords > 3)
return $numKeywords." Keywords";
else
{
$pass = str_replace("+"," ",$pass);
return $pass;
}
}
Hiding Column
At instance, we don't want to show column in grid (like primary key), and it is equally needed for background operations like update or delete. hidden property can work here.
// don't show this column in list, but in edit/add mode
$col["hidden"] = true;
If hidedlg set to true this column will not appear in the modal dialog (colchooser / showhidecolumn) where users can choose which columns to show or hide.
$col["hidedlg"] = true;
Another scenario is we want to hide it on grid list, and display it on Add or Edit forms.
$col["editrules"] = array("edithidden"=>true);
If you want to enable searching on this hidden column, set
$col["searchoptions"] = array("searchhidden" => true);
You can also customize in one line, on which dialog/section this column will be displayed. Possible options are true or false. This may override the hidden and edithidden settings. See column-access.php
$col["show"] = array("list"=>true, "add"=>true, "edit"=>true, "view"=>true, "bulkedit"=>true);
Row Action Column
When we enable inline edit/delete option, An additional column Action is appended as last column of grid.
We can also specify, by defining a column with name $col["name"] = "act";. After that all changes will be applied on that column.
Customization of Action column width and other properties:
$col = array();
$col["title"] = "Action";
$col["name"] = "act";
$col["width"] = "50";
$cols[] = $col;