Getting Started

This PHP Grid Control enables functioning of reusable features of CRUD, Search, Sort, Paging etc.
To start with, here is simplest example.

->set_options() function is most of the customization, we’ll be learning.
->table is required, to enable automatic select,add,update,delete operation. Behind the scene it’ll create the DML queries binded with this table. By default all columns of the table are selected on grid. We’ll review how to change it.
->render() will generate the final output, to be displayed in view. It takes Grid ID as input, which should be unique on a page.

Now we will display the generated grid code $out along with few external css/js files. It’s upto you to place external css and js files at appropriate locations.

^ Top

Defining Columns of Grid

By default, when we define the ->table property, it displays all the columns of table. We can pick certain columns to be displayed on grid by using ->set_columns() function

We can have multiple column definitions in that final array which is passed to ->set_columns. In column definition we can set many column specific parameters. We’ll be covering them next.

The first column must be unique, in order to work properly. You can make it hidden in grid if you wish. See hidden property in later section for more.

^ Top

Column Options

Following are the parameters, that can be passed to customize column definition on grid.

Caption shown on grid

DB table field name or alias if used in SQL

DB table.field name in case of conflict in same field names of 2 tables.

Width of column

Editable (true,false)

Viewable (true,false)

When the option is set to false the column does not appear in view Form

If db fields allows null and we want to save (NULL) instead of “”. Defaults to false

Resizable (true,false)

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

Render as checkbox, with these values “checkedvalue:uncheckedvalue”

To make checkbox already in checked state while adding record
$col["editoptions"] = array(“value”=>”Yes:No”, “checked”=>”checked”);

Render as textbox with size 20, and initial value in textbox to 10

Render as password textbox, it should be used with $col["formatter"] = “password”; to hide password in listing

Render as select (dropdown), with these values “key:value;key:value;key:value”

Render as button

Render as image

Render as file upload control

For file upoading help, refer demos/editing/file-upload.php

Edit Rules

We can also specify the validation rules required on that field. Possible options are mentioned below

The data validation will check input against format specified in datefmt option, see datefmt below.

We can also have custom validation function for this column

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 collModel with rowpos and colpos properties it is recommended that all editing fields use these properties.

To mark a field as required, you can use

Column Formatter

This will format this column as date (and will show date picker control) on add or edit operations.

This will format this column as date time (and will show date time picker control) on add or edit operations.

Complete date formatting shortcode can be found on this link: http://docs.jquery.com/UI/Datepicker/formatDate

For password fields,

You can also set format options for numeric and currency data.

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

Text alignment (left,right,center)

Is searching allowed on this field (true,false)

Dropdown in auto filter search

We need to set stype and searchoptions to enable dropdown search in autofilter.

Is sorting allowed on this field (true,false)

Sort type (only work with load with array)

Defines the type of sort for column.

Make the data in column 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?id={id} given that, there is a column with $col["name"] = “id” exist.

linkoptions option is used with link parameter to provide additional attributes.

There is a limitation thatyou 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. or

etc.

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.

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.

Now if the condition is met, $data1 will be displayed otherwise $data2. You can also {field} replacement in $data1 & $data2. Refer example below.

For extended conditional data, you can also have callback function, that will allow you to display based on row data. For e.g.

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.

Another scenario is we want to hide it on grid list, and display it on Add or Edit forms.

Row-wise 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.

^ Top

Grid Options

Custom SQL Query

By default, when we define the ->table property, it fetches all the possible columns of table.
We can provide custom SQL query in ->select_command property to pick columns available for grid.
We can use complex multi-join sub-queries in it.

Misc Settings

You can use following options for ->set_options($grid) function.

Number of records to show on page

Options to show in paging records

To show/remove Paging navigation buttons

To show/remove Paging text e.g. Page 1 of 10

Enable or Disable total records text on grid

If set to true, and resizing the width of a column, the adjacent column (to the right) will resize so that the overall grid width is maintained (e.g., reducing the width of column 2 by 30px will increase the size of column 3 by 30px). In this case there is no horizontal scrolbar. Note: this option is not compatible with shrinkToFit option – i.e if shrinkToFit is set to false, forceFit is ignored.

This option describes the type of calculation of the initial width of each column against with the width of the grid. If the value is true and the value in width option is set then: Every column width is scaled according to the defined option width.

Expand grid to screen width

Show corner (lower-right) resizable option on grid

If set to true the grid initially is hidden. The data is not loaded (no request is sent) and only the caption layer is shown. When the show/hide button is clicked the first time to show grid, the request is sent to the server, the data is loaded, and grid is shown. From this point we have a regular grid. This option has effect only if the caption property is not empty and the hidegrid property (see below) is set to true.

Enables or disables the show/hide grid button, which appears on the right side of the Caption layer. Takes effect only if the caption property is not an empty string.

The height of the grid. Can be set as percentage or any valid measured value

If this option is not set, the width of the grid is a sum of the widths of the columns defined

The text which appear when requesting and sorting data. Defaults to Loading…

This option defines the toolbar of the grid. This is array with two values in which the first value enables the toolbar and the second defines the position relative to body Layer. Possible values “top” or “bottom” or “both”

Allow you to multi-select through checkboxes

This parameter have sense only multiselect option is set to true. The possible values are: shiftKey, altKey, ctrlKey

Set a zebra-striped grid, boolean

Default sort grid by this field, Sort ASC or DESC

Caption of grid

Creates dynamic scrolling grids. When enabled, the pager elements are disabled and we can use the vertical scrollbar to load data. useful for big datasets

Makes grid right to left, for rtl languages e.g. arabic. Default is ltr

Inline cell editing, like spreadsheet

Display Top Pager bar

URL for grid page (for ajax calls), defaults to REQUEST_URI. It works with http & https. Used when passing extra querystring data.

Set Add and Edit form dialog width. This can be used with combination of css customization of dialog forms.

Just like width in dialog options, you can also set other for e.g.

Set Form to position on center of screen

Enable form Prev | Next record navigation

Refer demos/appearence/dialog-layout.php for demo.

Grid Actions

We can also switch actions to enable or disable them on grid. It is controlled by ->set_actions() function.

Possible values are true or false.

add Enable / Disable add operation on grid. Defaults to true.
edit Enable / Disable edit operation on grid. Defaults to true.
delete Enable / Disable delete operation on grid. Defaults to true.
view Enable / Disable view operation on grid. Defaults to true.
clone Enable / Disable clone operation on grid. Defaults to false.
rowactions Enable / Disable inline edit/del/save option. Defaults to true.
export Enable / Disable export to excel option. Defaults to false.
autofilter Enable / Disable autofilter toolbar for search on top. Defaults to true.
showhidecolumns Enable / Disable button to hide certain columns from client side. Defaults to true.
inlineadd Enable / Disable button to perform insertion inline. Defaults to false.
search Search property can have 3 values, simple, advance or false to hide.

Exporting Data

format could be pdf or excel.
heading is used as Heading of pdf file.
orientation is page orientation. Could be landscape or portrait.
paper values could be 4a0,2a0,a0,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,b0,b1,
b2,b3,b4,b5,b6,b7,b8,b9,b10,c0,c1,c2,c3,c4,c5,
c6,c7,c8,c9,c10,ra0,ra1,ra2,ra3,ra4,sra0,sra1,
sra2,sra3,sra4,letter,legal,ledger,tabloid,executive,
folio,commercial #10 envelope,catalog #10 1/2 envelope,
8.5×11,8.5×14,11×17

Setting paged to 1 will only export current page.

Export all data which is fetched by SQL, or export after applying search filters (if any)
Possible values are filtered or all.

Using Subgrid

Setting subGrid to true will enable subgrid. When clicking + icon on parent grid, it will try to load url defined in subgridurl. By default ‘rowid’ (PK) of parent is passed. subgridparams holds comma sep. fields that will be POSTed from parent grid to subgrid. They can be read using $_POST in subgrid.

On subgrid, data can be fetched and passed in SQL

For extra params passed from parent other than rowid (e.g. company), we need some persistent storage in session for ajax calls

Search on Load

Following config will enable search on load. Initialize search with name field equal to eq ‘Client 1′

Master Detail Grid

Following params will enable detail grid, and by default ‘id’ (PK) of parent is passed to detail grid. (see master-detail.php)

In order to invoke multiple detail grid, you can pass grid identifier in this way.

To extra params passed to detail grid, column name comma separated

Grouping

Following setting will enable grouping footer in grid. (see grouping.php)

^ Top

Grouping Headers

Now you can have a grouped headers in phpgrid control.
It would help in categorizing your related columns. (demos/appearence/group-header.php)

^ Top

Conditional Formatting

With conditional formatting, you can specify the CSS of rows or columns based on data in it.

If nothing set in ‘op’ and ‘value’, it will set column formatting for all cell

Finally, you need to call set_conditional_css of grid object to enable formatting.

Refer demos/appearence/conditional-format.php for reference.

^ Top

Debug Mode

Debug mode is enabled by default and it will show the server side failure reason.
When going in production mode, you should disable the debug mode by following config.

If you wish to change the SQL errors, you can turn them off using following setting.

For custom message at server side data validation, refer demos/editing/server-validation.php

See screenshots here

^ Top

Grid Events

For advance solutions, We are not limited to single table operations. We often need to update several tables and execute extra business cases like sending an email or soft delete a record. In all such cases, we can have our own code-behind implementation for ADD, UPDATE or DELETE operations.

The on_insert takes 3 params (, or , )
If you pass last argument as true, functions will act as a data filter and insert/update in ->table will be performed by grid after your function.

In each callbacks, $data is passed to function which contains all posted data. We can print_r() it for further help.

If the 3rd argument is true, the function will behave as a data filter and the final update will be done by grid code. For e.g.

You can also write you custom function for data export (see export-custom.php)
$e["onexport"] = array(“doexport”, null);

You can also set Client side event handlers (e.g. on row select)

Step1: Set JS event handler

Step2: Define JS event handler (where ‘list1′ is grid id and ‘company’ is field name to load)

^ Top

Localization

To enable text labels in your desired language, change source of the local javascript file. Available language packs are stored in this folder js/jqgrid/js/i18n/. Over 39 language packs are in the solution. (see localization.php)

^ Top

ADODB Integration

To enable support for non-mysql databases, we are using ADODB lib. For reference see adodb lib documentation. We can also use it for MySQL databases.

Following code snippet connect PHP Grid Control to SQL Server.

Following code snippet connect PHP Grid Control to Postgres.

^ Top


Updated Thursday, May 02, 2013

 

Leave a Reply

Working Demos

Working Demos

Browse working Examples of Php Grid, most of which are available in full package. You can also check Screenshots
Features

Features

Add, Edit, Del, Search, Sort, Page, Themes, Autofilter, Group, Export, Multiple Databases, Custom Add/Del Calls, Grid-Subgrid View ...
Live Support

Live Support

For Live Support, Add in Gtalk gridphp@gmail.com. For Community Support Visit Support Center
Top