Archive by Author

Composite Primary Key Support *

* An exclusive feature for phpGrid Enterprise and Universal License

In the past, phpGrid supports database SQL that only has a single primary key. If multiple primary keys exist, the recommended workaround was to create an auto-increment column as an alternative single primary key.

Though most cases the above solution is sufficient, as the database grows and its structure evolves, the need to support composite primary key becomes essential, especially in an enterprise environment.

Starting phpGrid 6 (Enterprise & Universal only), you can pass an array of string as the primary key as the $sql_key in data grid constructor. e.g.

1
array("KEY1", "KEY2", "KEY3"...);
 

Lots of resources devoted into developing this important feature while making it simple at the same time. In stead of passing a single string variable in the constructor as the primary key, you can now pass an array of strings as the composite primary key. For a single primary key, you can still use a string or an array with a single string value. e.g.

Complete composite primary key example:

1
2
3
$dg = new C_DataGrid("SELECT * FROM orderdetails", array("productCode", "orderNumber"), "orderdetails");
$dg->enable_edit("FORM", "CRUD");
$dg->display();

See Live Example!

For single primary key, you can still pass it as string type, or an array with just a single string element.

1
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

- OR -

1
$dg = new C_DataGrid("SELECT * FROM orders", array("orderNumber"), "orders");
Comments ( 0 )

Column Chooser & Sortable Row

The example below demonstrates column chooser and sortable row features. Click on the column chooser icon in the footbar to reveal the column chooser dialog. To test sortable row, drag a row and move it around.

1
2
3
4
5
6
$dg = new C_DataGrid("select * from products", "productCode", "products");

$dg -> enable_columnchooser(true);
$dg -> set_sortablerow(true);

$dg -> display();

See Live Example!

Comments ( 0 )

Multiple Datagrid Instances From the Same Table

You can certain have more than one datagrid instances from the same database table. Just make sure to give each unique name using set_jq_gridName() method.

1
2
3
4
5
6
7
8
9
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_edit();
$dg -> display();

$dg2 = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg2->set_jq_gridName('order2');
$dg2->set_caption("order 2");
$dg2->enable_edit();
$dg2 -> display();

See Live Example! (scroll to the right view the 2nd grid)

Comments ( 0 )

Row-level Edit Permission

Using actions column in the earlier example, it’s now possible to specify row-level permission for editing using additional javascript.

The Javascript used here hides the delete and edit buttons based condition that when status equals to “Shipped”. The example shown is for demo purpose only. It’s NOT 100% secure. Programmer should still validate the permission on the server side.

For simpler condition without using Javascript, you can consider set_edit_condition() method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
// note this should not replace database role-based or user-based permissions.
$onGridLoadComplete = <<<ONGRIDLOADCOMPLETE
function(status, rowid)
{
    var ids = jQuery("#orders").jqGrid('getDataIDs');
    for (var i = 0; i < ids.length; i++)
    {
        var rowId = ids[i];
        var rowData = jQuery('#orders').jqGrid ('getRowData', rowId);

        if($("#orders").jqGrid("getCell", rowId, "status") == "Shipped"){
            $("#orders").jqGrid("setCell", rowId, "actions", " zzz ", {"display":"none"}); // not possible to set value for virtual column
        }
    }
}
ONGRIDLOADCOMPLETE
;

$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->set_col_hidden('comments');

$dg->add_column("actions", array('name'=>'actions',
    'index'=>'actions',
    'width'=>'80',
    'formatter'=>'actions',
    'formatoptions'=>array('keys'=>true)),'Actions');
$dg->set_grid_property(array('onSelectRow'=>'')); // remove onSelect event
$dg->add_event("jqGridLoadComplete", $onGridLoadComplete);
$dg->enable_edit('INLINE');

$dg -> display();

See Live Example!

Comments ( 0 )

Inline Edit Actions Column

Inline edit mode also comes with actions column with edit and delete buttons. Use add_column method to add “actions”. In formatoptions, specify the following property to display or hide edit and delete buttons:

  • delbutton – true or false
  • editbutton – true or false

Note that when use actions column, it is not necessary to specify edit options in enable_edit method. An advantage using actions column is that it’s possible to have row-level permission. Please see datagrid row-level edit permission example.

1
2
3
4
5
6
7
8
9
10
11
12
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// enable edit - but no need to specify 2nd parameter because we will be using actions column
$dg -> enable_edit("INLINE");

$dg->add_column("actions", array('name'=>'actions',
    'index'=>'actions',
    'width'=>'70',
    'formatter'=>'actions',
    'formatoptions'=>array('keys'=>true, 'editbutton'=>true, 'delbutton'=>false)),'Actions');

$dg -> display();

See Live Example!

Comments ( 0 )

enable_autoheight()

  • Parameters:
    • $autoheight: true or false. Default to false.
  • Description:
    • Use this method to set datagrid height as the same as the current window height.
  • Remark:
    • Do not use this method when multiple grids present on a single page
  • Example:
1
$dg -> enable_autoheight(true);
Comments ( 0 )

enable_columnchooser()

  • Parameters:
    • $enable: true or false
  • Description:
    • Enable column chooser.
  • Remark:
    • The column chooser does not persist. The columns displayed resets whenever grid or the page refreshes.
  • Example:
1
$dg -> enable_cholumnchooser(true);
Comments ( 0 )

set_sortablerow()

  • Parameters:
    • $sortable: true or false
  • Description:
    • Activate sortable row. Drag and drop row to sort.
  • Remark:
    • The sort does not persist. The sort is lost the grid or the page refreshes.
  • Example:
1
$dg -> set_sortablerow(true);
Comments ( 0 )

THEME global constant

Available starting phpGrid version 6, you can set a global constant “THEME” in conf.php to change all of your datagrid themes without calling set_theme() method individually.

1
2
// set a global theme in conf.php (version 6+)
define('THEME', 'cobalt');

The premium themes are currently available to Enterprise and Universal Licenses.

Comments ( 0 )

DEBUG global constant

Note: DEBUG global constant is only available starting version 6. For phpGrid version 5.5.5 and lower, see enable_debug() method please.

phpGrid version 6 introduces a new global constant named “DEBUG” in conf.php. When set to true, it turns on the debug mode. When not defined, DEBUG value defaults to false.

When DEBUG set to true:

  • When set to true, it displays phpGrid object as well as the current PHP session information. In addition, AJAX response data are also displayed during CRUD operations. This is helpful debugging information can be used during development.
     
  • It also display the phpGrid version number and associated javascript libraries used.
1
2
// In conf.php
define('DEBUG', true);
Comments ( 0 )

set_edit_condition()

  • Parameters:
    • $edit_conditoin: An array stores edit permission condition.
  • Description:
    • Set row-level permission condition for editing.
  • Remark:
    • This works for INLINE ONLY by hiding the edit icons using javascript, CSS
    • For that reason, developers should still validate user permission at the database or on the server side.
    • For complex conditions, use add_event and javascript. See row-level edit permission example.
  • Example:
1
2
$dg -> enable_edit('INLINE', 'CRUD');
$dg -> set_edit_condition(array('status' => '=="Shipped"', '&&', 'customerNumber' => '==114' ));

The above code snippet generates javascript that validates Status and customerNumber value. If both are true, the edit icon will appear.

phpGrid_Custom_Edit_Condition_without_using_set_edit_condition

Comments ( 0 )

set_grid_property()

  • Parameters:
  • Description:
    •  Advanced method that  sets datagrid properties. The parameter must be an array. You must be rather familiar jqGrid API in order to take advantage of this method effectively. In most cases, you do not need to use this method.
  • Remark:
    • This method is completely difference from set_col_property() method that sets column property, a subset of grid property.
    • This is an advanced method. In most cases, you don’t need to call this method.
  • Example:
1
2
$dg -> set_grid_property(array('search'=>true));
$dg -> set_grid_property(array("altclass" => "alternatie_row_css_class"));

custo_prop_jsonstr or set_grid_property?
The difference between cust_prop_jsonstr and set_grid_property is that the later is a function and must take an array as parameter but cust_prop_jsonstr takes string parameter.

Which one to use?
The rule of thumb is to use cust_prop_jsonstr when the property is form of string; else use set_grid_property() function. In general, you want to use set_grid_property().

Comments ( 0 )

cust_prop_jsonstr

custo_prop_jsonstr is a variable, not a function. The variable type is JSONString (string in JSON format).

The difference between cust_prop_jsonstr and set_grid_property() is that the later is a function and must take an array as parameter. On the other hand, cust_prop_jsonstr gives user the power to inject string directly into the jQgrid properties.

Which one to use?
The rule of thumb is to use cust_prop_jsonstr when the property is form of string; else use set_grid_property() function. In general, you want to use set_grid_property().

Remark:
This is an advanced variable. In most cases, you do not need to use it unless you are already very familiar with jQgrid in and out that you want to manipulate your grid properties like a code ninja. :)

Example:

1
2
3
4
5
// Here filters must be passed as string to data.php later sent through POST. It's not possible to use set_grid_property function which must take array params
$dg->cust_prop_jsonstr = 'postData: {filters:
                            \'{"groupOp":"AND","rules":[{"field":"status","op":"eq","data":"Shipped"}]}\'},'
;
// add toolbar to top of the grid.
$dg->cust_prop_jsonstr = 'toppager:true,';
Comments ( 0 )

before_script_end

before_script_end is not a function but a variable. It allows you to inject custom javascript before the end of jqGrid object closing script tag so all DOM elements are presented. This is considered an advanced way to manipulate your grid.

The following code snippet demonstrates using this variable to insert a javascript that creates a export dropdown menu on the bottom toolbar.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$dg3 = new C_DataGrid("SELECT * FROM products", "productCode", "products");

$exportDropdown =<<< EXPORTDROPDOWN
$('#products_pager1_left').append ('<div style=padding-right: 16px;>Export:\
    <select onchange=document.location.href=this.options[this.selectedIndex].value;>\
        <option>---</option>\
        <option value=/phpGridx/export.php?gn=products&export_type=excel>Excel</option>\
        <option value=/phpGridx/export.php?gn=products&export_type=pdf>PDF</option>\
        <option value=/phpGridx/export.php?gn=products&export_type=html>HTML</option>\
        <option value=/phpGridx/export.php?gn=products&export_type=csv>CSV</option>\
    </select></div>');
EXPORTDROPDOWN;

$dg->before_script_end = $exportDropdown;
$dg->display();
Comments ( 0 )

odbc_mssql

“odbc_mssql” should only be used for DSN connection for Microsoft SQL Server in *nix or OS X operation system using unixODBC through FreeTDS protocol. You can learn more about how to use unixODBC with FreeTDS here

unixODBC

unixODBC requires additional environment variables in file conf.php

1
2
putenv("ODBCINSTINI=/usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini");
putenv("ODBCINI=/usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini");
 

FreeTDS

FreeTDS uses a configuration file called “freetds.conf”. Below is an sample of freetds.conf used by phpGrid during testing for your reference.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
/usr/local/Cellar/freetds/0.91/etc/freetds.conf

   # server specific section
    [global]
    # TDS protocol version
    ; tds version = 4.2

    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
    ; dump file = /tmp/freetds.log
    ; debug flags = 0xffff

    # Command and connection timeouts
    ; timeout = 10
    ; connect timeout = 10

    # If you get out-of-memory errors, it may mean that your client
    # is trying to allocate a huge buffer for a TEXT field.
    # Try setting 'text size' to a more reasonable limit
    text size = 64512

    [phpgridmssql]
    host = phpgridmssql.cbdlprkhjrmd.us-west-1.rds.amazonaws.com
    port = 1433
    tds version = 7.0
   /usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini

   [phpgridmssql]
    Description = MS SQL Server
    Driver = FreeTDS
    Server = phpgridmssql.cbdlprkhjrmd.us-west-1.rds.amazonaws.com
    TraceFile = /tmp/sql.log
    UID = mssqluser
    PWD = PASSWORD
    ReadOnly = No
    Port = 1433
   Database = sampledb
   /usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini

   [FreeTDS]
    Description = FreeTDS
    Driver = /usr/local/Cellar/freetds/0.91/lib/libtdsodbc.so
    Setup = /usr/local/Cellar/freetds/0.91/lib/libtdsodbc.so
    FileUsage = 1
    CPTimeout =
    CPResuse =
    client charset = utf-8
Comments ( 0 )

Column Methods

Version 5.5.5 introduced set_grid_method() method. You can use this method to call any jqGrid javascript method that can perform actions on the grid as a whole. However, it’s not possible to manipulate the grid on a row or cell level using set_grid_method.

The example below demonstrates set_grid_method to grouping header by calling the jqGrid “setGroupHeader” method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$dg = new C_DataGrid("SELECT * FROM payments", "customerNumber", "orders");

$dg->set_col_width("customerNumber", 30);
$dg->set_col_width("checkNumber",50);
$dg->set_col_width("amount",50);

$dg->set_grid_method('setGroupHeaders', array(
                                array('useColSpanStyle'=>true),
                                'groupHeaders'=>array(
                                        array('startColumnName'=>'customerNumber',
                                              'numberOfColumns'=>2,
                                              'titleText'=>'Numbers Header') )));

$dg -> display();

See Live Example!

Comments ( 0 )

Column Freeze

You can now use set_col_frozen method to set column freeze method. It’s useful when working with a big table with many columns. The freezing column must start from the very left and then one by one to the right.

1
2
3
4
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->set_dimension(600, 400, false);
$dg->set_col_frozen('orderNumber');
$dg -> display();

Note that it is recommended to set text not to wrap with CSS “nowrap”. See http://phpgrid.uservoice.com/knowledgebase/articles/154972

See Live Example!

Comments ( 0 )

set_grid_method()

  • Parameter(s):
    • parameter: A variable argument. Variable argument makes the function more flexible because different jqGrid methods will have different set of parameters. Super useful.
  • Description:
  • Remark:
    • Available in 5.5.5 and higher.
    • Starting version 6, the parameter becomes a single variable argument.
    • Only use this method to call jqGrid methods that can act on the grid as a whole. It’s NOT possible to manipulate the grid on a row or cell level using set_grid_method because the generated javascript code is appended to the end of the script before closing bracket.
  • Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// create group header using this method
$dg->set_grid_method('setGroupHeaders', array(
                    array('useColSpanStyle'=>true),
                    'groupHeaders'=>array(
                        array('startColumnName'=>'customerNumber',
                            'numberOfColumns'=>2,
                            'titleText'=>'Numbers Header') )));
// inline edit add new row
$dg->set_grid_method('inlineNav',
                '#'. $this->jq_gridName .'_pager1',
                array('addParams' => array(
                        'position'=> "last",
                        'addRowParams'=>array(
                        'keys'=>true))));
Comments ( 0 )

set_col_frozen()

  • Parameter(s):
    • $col_name: Column name
  • Description:
    • Similar to Excel column freeze, phpGrid can now freeze column(s)
  • Remark:
    • Note that the frozen columns should be from left to right, one after other.
    • In inline edit, the frozen columns are not editable.
    • Columns are not draggable if any column is frozen
  • Example:
1
2
// freeze both customer_id and customer_name columns
$dg -> set_col_frozen("customer_id")->set_col_frozen("customer_name"):
Comments ( 0 )

Local Array Data Source

phpGrid now supports local array data source (version 5.5+). No database is required for local data. So it’s NOT necessary to define PHPGRID_DB_* variables in conf.php when using local array. Simply pass a PHP array as the first parameter to the phpGrid constructor. Everything else is virtually the same.

In the below example, the first segment creates a local PHP array, namely $data1, will be used as the data source for phpGrid. The second segment demonstrates passing the $data1 to the phpGrid constructor and call its methods. All existing phpGrid methods can be used the same way as a database-driven datagrid*.

Make sure to check out the live example!

Local Array (PHP)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$name = array('Bonado', 'Sponge', 'Decker', 'Snob', 'Kocoboo');
for ($i = 0; $i < 200; $i++)
{
    $data1[$i]['id']    = $i+1;
    $data1[$i]['foo']    = md5(rand(0, 10000));
    $data1[$i]['bar1']    = 'bar'.($i+1);
    $data1[$i]['bar2']    = 'bar'.($i+1);
    $data1[$i]['cost']    = rand(0, 100);
    $data1[$i]['name']    = $name[rand(0, 4)];
    $data1[$i]['quantity'] = rand(0, 100);
    $data1[$i]['discontinued'] = rand(0, 1);
    $data1[$i]['email'] = 'grid_'. rand(0, 100) .'@example.com';
    $data1[$i]['notes'] = '';
}

phpGrid Code ($data1 is the local array created above)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$dg = new C_DataGrid($data1, "id", "data1");   // $data1 is the local array
$dg->set_col_title("id", "ID")->set_col_width('id', 20);
$dg->set_col_title("foo", "Foo");
$dg->set_col_title("bar", "Bar");
$dg->set_col_title('discontinued', 'disc.')
    ->set_col_width('discontinued', 35);
$dg->set_col_align('cost', 'right')
    ->set_col_currency('cost', '$');
$dg->set_col_width('bar1', 40);
$dg->set_col_width('quantity', 220);
$dg->set_row_color('lightblue', 'yellow', 'lightgray');
$dg->set_databar('quantity', 'blue');
$dg->enable_search(true);
$dg->enable_edit('FORM', 'CRUD');
$dg->enable_resize(true);
$dg->set_col_format('email', 'email');
$dg->set_col_dynalink('name', 'http://example.com', array("id", "name"));
$dg->set_caption('Array Data Test');
$dg->set_col_hidden('bar2');
$dg->set_col_property('notes', array('edittype'=>'textarea','editoptions'=>array('cols'=>40,'rows'=>10)))
    ->set_col_wysiwyg('notes');
$dg->set_dimension(900, 400);
//$dg->set_multiselect(true);
$dg->set_conditional_value('discontinued', '==1',  array("TCellStyle"=>"tstyle"));
$dg->set_theme($theme_name);
$dg->display();

Screenshot

local_array

* Please note that master detail, subgrid, export and file uploads are not yet available when using local array data source.

** Local array data source currently is only available to Enterprise and Universal Licensees.

See Live Example!

JSON Data Source

It’s also possible to use JSON string as a data source with one extra step: use json_decode and set the second parameter to true to return the decoded JSON string to an associative array.

Once you have the array, it can be passed to the phpGrid constructor as if it’s a local array data source. This is useful when your data is real-time or loaded from a remote source such as stock quote and RSS etc.

1
2
3
4
5
6
$url = "http://myurl.com/json_string";
$json = file_get_contents($url);
$json_output = json_decode($json, true);

$dg = new C_DataGrid($json_output['items'], "id", "items");
$dg->display();
Comments ( 0 )

Custom Data Validation

phpGrid automatically does data validation based on database data type such as a string cannot be used when the data type is integer and non-null field must have a value. It’s sufficient in most everyday use cases.

Starting version 5.5, users can use their own validation javascript function for more complex data edit rules. Use set_col_customrule() method is created for this purpose. Below is an example of three Javascript functions used for custom validation. You can even compare data among multiple columns. The Javascript functions in example is rather over simplified, but you get the picture. Once obtained the cell value, you can develop even more complex Javascript function to validation your data.

Make sure that you play around with the live example after the code snippet!

Javascript Functions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// validation (FORM and INLINE)
function price_validation1(value, colname) {
    if(value < 0){
       return [false,colname + " must be zero a positive integer."];
    }
    return [true, ""];
}

// validation (INLINE only). Note the technique to obtain a specific cell value
function price_validation2(value, colname) {
    var rowId = jQuery("#products").jqGrid('getGridParam','selrow');
    if(parseFloat(jQuery('#' + rowId + '_' + 'buyPrice').val()) >  parseFloat(jQuery('#' + rowId + '_' + 'MSRP').val()))
        return [false,"buyPrice must be equal or less than MSRP."];
    else
        return [true,""];
}

// validation (FORM only). Note the technique to obtain a specific cell value is different from INLINE edit.
function price_validation3(value, colname) {
    if(parseFloat(jQuery('#buyPrice').val()) >  parseFloat(jQuery('#MSRP').val()))
        return [false,"buyPrice must be equal or less than MSRP."];
    else
        return [true,""];
}

PHP

1
2
3
4
5
$dg = new C_DataGrid("SELECT * FROM products", "productCode", "products");
$dg->enable_edit('FORM');
$dg->set_col_customrule('quantityInStock', 'price_validation1');
$dg->set_col_customrule('buyPrice', 'price_validation3');
$dg->display();

See Live Example! (Try set quantityInStock to a negative number, and buyPrice < MSRP.)

Comments ( 0 )

set_col_customrule()

  • Parameters:
    • $col_name: column name
    • $custom rule: Javascript function to validate data
  • Description:
    • Create custom javascript validation for column value during edit. phpGrid has automatic default validation based on database data type. This method comes in handy when additional data validation is required such as value range, data dependency etc.
  • Example:
1
2
// price_validation1 is a javascript function. See related associate live example for complete implementation
$dg->set_col_customrule('quantityInStock', 'price_validation1'); 
Comments ( 0 )

Virtual/Calculated Column

Starting version 5.5, you can now add virtual column, AKA calculated column, to your existing datagrid. Virtual, by definition, is that it doesn’t exist in the database table. It’s a calculated field created from other columns. The virtual columns are added to the END of the existing datagrid.

phpGrid only adds a “virtual” column and does NOT change the database table structure. Front-end user should not be able to add/change a column in your database table structure. It should be done only by a very smaller number of people such as DBA, and ultimately through a back-end database administration program.

It’s important that the virtual column name is NOT one of the existing database column names used in PHP datagrid.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_edit('INLINE');

// calculated value to be displayed in the virtual column
// n1 stores column 1 value, n2 stores column 7 value..and so on.
$col_formatter = <<<COLFORMATTER
function(cellvalue, options, rowObject){
    var n1 = parseInt(rowObject[0],10),      
        n2 = parseInt(rowObject[6],10);      
    return n1+n2;
}
COLFORMATTER;

$dg -> add_column(
        'total',
        array('name'=>'total',
            'index'=>'total',
            'width'=>'360',
            'align'=>'right',
            'sortable'=>false,
            'formatter'=>$col_formatter),
        'Total (Virtual)');
$dg->display();

See Live Example! (The last column “Total” is a virtual column)

Comments ( 0 )

add_column()

  • Parameter(s):
    • $col_name: Name of the calculated/virtual column. It cannot have space and must NOT be one of the existing database column names.
    • $property: Column properties. See set_col_property() for available column properties usage.
    • $title: Optional. Title for this virtual column. If omitted, it’s the same as the column name $col_name.
  • Description:
    • Append virtual column, AKA calculated column, to the end of an existing datagrid with this method.
  • Remark:
    • The $col_name cannot contain space and must begin with a letter
    • Use “formatter” column property to hook up Javascript function, e.g. below, $col_formatter is the Javascript to display value in virtual column.
    • The virtual column always adds to the end of the grid in the order of virtual column is created.
    • Text must be surrounded with SINGLE quote.
    • Virtual column is not sortable.
  • Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$col_formatter = <<<COLFORMATTER
function(cellvalue, options, rowObject){
var n1 = parseInt(rowObject[0],10),    // get value from column #1
n2 = parseInt(rowObject[6],10);        // get value from column #7
return n1+n2;    
}
COLFORMATTER
;

$dg -> add_column(
        'total',
        array('name'=>'total',
            'index'=>'total',
            'width'=>'360',
            'align'=>'right',
            'formatter'=>$col_formatter),
        'Total (Virtual)');
Comments ( 0 )

display_script_includeonce()

  • Parameter(s):
    • None
  • Description:
    • Includes required Javascript libraries before displaying our grids.
  • Remark:
    • Developers don’t need to call this method to include required Javascript libraries (jqGrid, jwysiwyg, ajaxfilupload etc.) because the phpGrid includes those Javascript automatically for you. This method is only used in get_display() in a MVC framework.
Comments ( 0 )

phpGrid, phpChart Integration

 

Run Demo

phpGrid phpChart Integration

It’s easy to combine both phpGrid and phpChart into a seamless integrated, interactive, and completely web-based ad-hoc content and report management tool.

 

Setup

Before start, You must acquire both phpGrid and phpChart. You can download both for free.

It’s important to install phpGrid and phpChart in separate folder. Below is the folder structure used in our example. phpGrid and phpChart each resides in separate folder inside phpGrid_phpChart folder under web root folder htdocs. This is RECOMMENDED folder hierarchy.

htdocs
+-- phpGrid_phpChart
|   |-- phpGrid
|   |   +-- conf.php
|   |-- phpChart
|   |   +-- conf.php
|   +-- phpgrid_phpchart_integration.php
 

Once folder structure is ready. Set up phpGrid SERVER_ROOT value and phpChart SCRIPTPATH value in each of their respective conf.php file.

phpGrid conf.php

1
define('SERVER_ROOT', '/phpGrid_phpChart/phpGrid');

phpChart conf.php

1
define('SCRIPTPATH','/phpGrid_phpChart/phpChart/');
 

Programming

Now, we can move on to the actual integration. First all, include both conf.php files on top of the script.

1
2
require_once("phpGrid/conf.php");
require_once("phpChart/conf.php");

Then, create the chart with phpChart. Here in this example we have two charts. The first chart has a single series of data. The second one has two series and renders bar chart instead of the default line chart. All the phpChart methods and properties documents are available on phpChart.net. Pass “null” as the series data if you don’t want to have any initial data in the chart.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// first phpChart with one series
$pc = new C_PhpChartX(array(array(null)), 'Graph');
$pc->set_axes(array(
        'xaxis'=> array('label'=>'Buy Price'),
        'yaxis'=> array('label'=>'$')
    ));
$pc->draw(800,500);

// second phpChart with two series
$pc2 = new C_PhpChartX(array(array(null), array(null)), 'Graph2');
$pc2->set_axes(array(
        'xaxis'=> array(
            'label'=>'Buy Price/MSRP (by Product Code)'),
        'yaxis'=> array('label'=>'$')
    ));
$pc2->set_animate(true);
$pc2->set_legend(array('show'=>true,'location'=>'nw'));
$pc2->draw(800,500);

Finally the last part is to code the datagrid. In our example, we use “products” database table. The most important part of the datagrid is the custom event handler “jqGridLoadComplete” in which the phpGrid, phpChart integration happens. Here, values in both “buyPrice” and “MSRP” columns are retrieved and stored in d1 and d2 as array. You can replace those columns with columns with your own choice in your table. GraphData1 and GraphData2 are used to store the series of data that later will be used to plot our charts created earlier.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
$dg = new C_DataGrid("SELECT * FROM products", "productCode", "products");
$dg->enable_edit('FORM');
$onGridLoadComplete = <<<ONGRIDLOADCOMPLETE
function(status, rowid)
{
    var GraphData1 = [];
    var GraphData2 = [];

    d1 = $('#products').jqGrid('getCol', 'buyPrice', false);
    d2 = $('#products').jqGrid('getCol', 'MSRP', false);
    npoints = d1.length;
    for(var i=0; i < npoints; i++){
        GraphData1[i] = [i+1, parseInt(d1[i])];
    }
    _Graph.series[0].data = GraphData1;
    _Graph.replot({resetAxes:true});

    for(var i=0; i < npoints; i++){
        GraphData1[i] = [i+1, parseInt(d1[i])];
        GraphData2[i] = [i+1, parseInt(d2[i])];
    }
    _Graph2.series[0].data = GraphData1;
    _Graph2.series[1].data = GraphData2;
    _Graph2.replot({resetAxes:true});
}
ONGRIDLOADCOMPLETE
;
$dg->add_event("jqGridLoadComplete", $onGridLoadComplete);
$dg->set_col_property('MSRP', array('classes'=>'msrp_bg'));   // change column background color
$dg->set_col_property('buyPrice', array('classes'=>'buyprice_bg'));  // change column background color
$dg->enable_search(true);
$dg->display();

To easily recognize the data source, we set the datagrid column background color to match the series color in phpChart using CSS class

1
2
.msrp_bg { background-color: #4BB2C5 !important; }
.buyprice_bg {  background-color: #F8C05B !important; }
 

The COMPLETE example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<?php
require_once("phpGrid/conf.php");
require_once("phpChart/conf.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>phpGrid phpChart Integration Example</title>
</head>
<body>
<STYLE>
.msrp_bg {
    background-color: #4BB2C5 !important;
}
.buyprice_bg {
    background-color: #F8C05B !important;
}
</STYLE>

<p>Try do a quick search or do a sort. Observe the graph updates in real-time.</p>

<table><tr><td>
<?php
// first phpChart with one series
$pc = new C_PhpChartX(array(array(null)), 'Graph');
$pc->set_axes(array(
        'xaxis'=> array('label'=>'Buy Price'),
        'yaxis'=> array('label'=>'$')
    ));
$pc->draw(600,300);
?>
</td>
<td>
<?php
// second phpChart with two series
$pc2 = new C_PhpChartX(array(array(null), array(null)), 'Graph2');
$pc2->set_axes(array(
        'xaxis'=> array(
            'label'=>'Buy Price/MSRP (by Product Code)'),
        'yaxis'=> array('label'=>'$')
    ));
$pc2->set_animate(true);
$pc2->set_legend(array('show'=>true,'location'=>'nw'));
$pc2->draw(600,300);
?>
</td></tr></table>

<?php
$dg = new C_DataGrid("SELECT * FROM products", "productCode", "products");
$dg->enable_edit('FORM');

$onGridLoadComplete = <<<ONGRIDLOADCOMPLETE
function(status, rowid)
{
    var GraphData1 = [];
    var GraphData2 = [];

    d1 = $('#products').jqGrid('getCol', 'buyPrice', false);
    d2 = $('#products').jqGrid('getCol', 'MSRP', false);
    npoints = d1.length;
    for(var i=0; i < npoints; i++){
        GraphData1[i] = [i+1, parseInt(d1[i])];
    }
    _Graph.series[0].data = GraphData1;
    _Graph.replot({resetAxes:true});

    for(var i=0; i < npoints; i++){
            GraphData1[i] = [i+1, parseInt(d1[i])];
            GraphData2[i] = [i+1, parseInt(d2[i])];
    }
    _Graph2.series[0].data = GraphData1;
    _Graph2.series[1].data = GraphData2;
    _Graph2.replot({resetAxes:true});
}
ONGRIDLOADCOMPLETE
;

$dg->add_event("jqGridLoadComplete", $onGridLoadComplete);
$dg->set_col_property('MSRP', array('classes'=>'msrp_bg'));
$dg->set_col_property('buyPrice', array('classes'=>'buyprice_bg'));
$dg->enable_search(true);
$dg->display();
?>
</body>
</html>

That’s it! Do a quick search or sort. Observe the graph updating in real-time.

Give it a try! | Download Integration Example File

Technical Notes

  • The Javascript used in the custom event handler in phpGrid is the bread and butter of integration.
  • Set array to null for initial graph.
  • On initial plot creation you can pass series of data in a 1-D array. jqPlot internally converts it to a 2-D array. However, this doesn’t happen on .replot().
  • If the page throws an error
    1
    Notice: Constant DEBUG already defined in /phpGrid/conf.php on line 2

    This is because both phpChart and phpGrid have DEBUG constant, you need to comment out of them in conf.php

    1
    // define('DEBUG', true);
Comments ( 0 )

Customize Edit Form Layout

By default, the edit form is displayed a single column table. This is fine for table with small numbers of fields. When you got a large number of fields, the chances are that you want to modify the layout to display multiple columns. Use formoptions property “rowpos” and “colpos” for this purpose.

The following example demonstrates a 2-column edit form.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");

// hide a column
$dg -> set_col_hidden("requiredDate");

// enable edit
$dg -> enable_edit("FORM", "CRUD");

$dg -> set_col_property("orderNumber", array("formoptions"=>array("rowpos"=>1,"colpos"=>1)));
$dg -> set_col_property("orderDate", array("formoptions"=>array("rowpos"=>1,"colpos"=>2)));
$dg -> set_col_property("requiredDate", array("formoptions"=>array("rowpos"=>2,"colpos"=>1)));
$dg -> set_col_property("shippedDate", array("formoptions"=>array("rowpos"=>2,"colpos"=>2)));
$dg -> set_col_property("status", array("formoptions"=>array("rowpos"=>3,"colpos"=>1)));
$dg -> set_col_property("customerNumber", array("formoptions"=>array("rowpos"=>3,"colpos"=>2)));
$dg -> set_col_property("comments", array("formoptions"=>array("rowpos"=>4,"colpos"=>1)));

$dg->set_form_dimension(580, 270);

$dg->enable_debug(false);
$dg -> display();

Form Layout Screenshot

Hint: the edit form is resizable by click and drag bottom-right corner using mouse.

custom form edit layout

See Live Example! (double click a row to see 2-column edit form)

Comments ( 0 )

PHP Datagrid AJAX File Upload

* The file upload is supported in Enterprise and Universal license.

File upload is one of the most requested features. And we are finally able to delivery this new feature. It requires only a SINGLE LINE of code, with only a single function call to set_col_fileupload(). Since PHP is installed as Apache module in our host server, the 3rd parameter is optional that the physical path is automatically mapped based on 2nd parameter, the base URL.

All the file operations (Update, Delete) are Ajax-based, without refreshing the page.

1
2
3
4
5
$dg = new C_DataGrid("SELECT * FROM shippers", "ShipperID", "shippers");
$dg -> set_col_fileupload("fileToUpload", '/phpGridx/photos/');
$dg -> enable_edit('FORM');
$dg->set_col_img("fileToUpload", "/phpGridx/photos/");
$dg -> display();

You should consider resizing images displayed so large images won’t blow your datagrid out of proportion. Read more about Image resize using CSS.

The below screenshot (click to enlarge) illustrates the file edit operations in edit form. It has the “delete” button to remove the existing file before uploading a new one. The file name is saved to table “shippers”.

The below screenshot (click to enlarge) shows uploading new file as part of creating a new record by click on “choose file”, then pick file “truck.jpg” from the local system. Then the file “truck.jpg” is saved to file system under “/phpGridx/foo/” folder, and the file name is stored in table “shippers” alone with rest of data.

The follow screen (click to enlarge) shows that new record has been successfully created and the file “truck.jpg” has been successfully uploaded.

Comments ( 0 )

Hyperlink to Call JavaScript Function

You can show hyperlink using set_col_link for static link or set_col_dynalink for dynamic link. What about if you want to do something even fancier, like open a new window, or trigger a JavaScript event when hyperlink is clicked?

To call JavaScript function on hyperlink click, use “showlink” in set_col_format method.

In the following example, the first set_col_format displays text in alert window using JavaScript function “myFunction” to retrieved text from a different cell in the same row. The second does a phpgrid.com site search using jQuery Ajax function “phpGridSiteAjaxSearch”, and displays the result in a alert message window.

PHP:

1
2
3
4
5
6
7
8
9
10
11
12
13
$dg = new C_DataGrid("select * from products", "productCode", "products");
$dg -> set_col_title("productCode", "Product Code");
$dg -> set_col_title("productName", "Product Name");
$dg -> set_col_title("productLine", "Product Line");

$dg->set_col_format("productLine", "showlink", array("baseLinkUrl"=>"javascript:", "target"=>"_self",
    "showAction"=>"myFunction(jQuery('#products'),'",
    "addParam"=>"');"));                                                                                                                          

$dg->set_col_format("productName", "showlink", array("baseLinkUrl"=>"javascript:", "target"=>"_self",
    "showAction"=>"phpGridSiteAjaxSearch(jQuery('#products'),'",
    "addParam"=>"');"));                                                                                                                          
$dg -> display();

JavaScript:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
myFunction = function (grid,param) {
    var ar = param.split('=');
    if (grid.length > 0 && ar.length === 2 && ar[0] === '?id') {
        var rowid = ar[1];
        var prodDesc = grid.getCell(rowid, 'productDescription');

        alert(prodDesc);
    }
};

phpGridSiteAjaxSearch = function (grid,param) {
    var ar = param.split('=');
    if (grid.length > 0 && ar.length === 2 && ar[0] === '?id') {
        var rowid = ar[1];
        var productName = grid.getCell(rowid, 'productName');

        jQuery.ajax({
            url: 'http://phpgrid.com',
            data: { s: productName },
            type: 'GET',
            dataType: 'text',
            success:function(data,status) {
                // your code goes here for a successul Ajax call .e.g open a new window
                alert(data);
            },
            error:function(data,status,err){
                alert(data + status);
            }
        });
    }
};

See Live Example

Comments ( 0 )

Column Property

You can now directly manipulate individual column properties without using helper set_col_* functions such as set_col_format() and set_col_readonly() .  This is suitable for users who are already familiar with jqGrid colMdel API (http://www.trirand.com/jqgridwiki/doku.php?id=wiki:colmodel_options).

The new column property method does not replace existing helper functions. It should be used beside by side with the existing set_col_* helper functions.

1
2
3
4
5
6
7
8
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg -> set_row_color('yellow', 'blue', 'lightgray');
$dg -> set_col_property("orderNumber", array("name"=>;"Order Number", "width"=>;40));
// display only time
$dg -> set_col_property("orderDate",
                        array("formatter"=>;"date",
                              "formatoptions"=>;array("srcformat"=>;"ISO8601Short","newformat"=>;"g:i A"));
$dg -> display();

See Live Example!

Comments ( 0 )

Custom Event Handler

phpGrid supports custom event handler using add_event() method. The event handlers are essentially JavaScript functions so they must be enclosed with PHP heredoc syntax. This is a very powerful function once you are familiar with events.

Below are the events used in this example:

  • jqGridSelectRow
  • jqGridrowattr
  • jqGridAddEditAfterSubmit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

$onSelectRow = <<<ONSELECTROW
function(status, rowid)
{
    alert('event 1');
    console.log(rowid);
    console.log(status);
}
ONSELECTROW
;

$onSelectRow2 = <<<ONSELECTROW2
function(status, rowid)
{
    alert('event 2');
    console.log("here");
}
ONSELECTROW2
;

$rowattr = <<<ROWATTR
function (rowData, inputRowData)
{
    return rowData.status === "OnHold" ? {style: "background-color:blue"} : {};
}
ROWATTR
;

// post data another page after submit
$afterSubmit = <<<AFTERSUBMIT
function (event, status, postData)
{
    console.log(postData);
    alert(postData.customerNumber);
    $.ajax({ url: '/my/site',
        data: {custNum: postData.customerNumber}, // replace customerNumber with your own field name
        type: 'post',
        success: function(output) {
                    alert(output);
                }
        });
}
AFTERSUBMIT
;

$dg->add_event("jqGridSelectRow", $onSelectRow);
$dg->add_event("jqGridSelectRow", $onSelectRow2);
$dg->add_event("jqGridrowattr", $rowattr);
$dg->add_event("jqGridAddEditAfterSubmit", $afterSubmit);
$dg->enable_edit('FORM');
$dg -> display();

See Live Example! (click on any row to trigger event)

Comments ( 0 )

add_event()

  • Parameter(s):
    • $event_name: Event name from one of the following available events
      • jqGridResizeStart
      • jqGridResizeStop
      • jqGridAfterInsertRow
      • jqGridGridComplete
      • jqGridAfterGridComplete
      • jqGridBeforeRequest
      • jqGridLoadComplete
      • jqGridAfterLoadComplete
      • jqGridSortCol
      • jqGridSelectAll
      • jqGridBeforeSelectRow
      • jqGridCellSelect
      • jqGridDblClickRow
      • jqGridRightClickRow
      • jqGridHeaderClick
      • jqGridSelectRow
      • jqGridShowHideCol
      • jqGridRemapColumns
      • jqGridKeyLeft
      • jqGridKeyRight
      • jqGridKeyEnter
      • jqGridKeySpace
      • jqGridToolbarBeforeSearch
      • reloadGrid
      • jqGridToolbarAfterSearch
      • jqGridToolbarBeforeClear
      • jqGridToolbarAfterClear
      • jqGridFilterBeforeShow
      • jqGridFilterAfterShow
      • jqGridFilterInitialize
      • jqGridFilterSearch
      • jqGridFilterReset
      • jqGridAddEditBeforeCheckValues
      • jqGridAddEditClickSubmit
      • jqGridAddEditBeforeSubmit
      • jqGridAddEditErrorTextFormat
      • jqGridAddEditAfterSubmit
      • jqGridAddEditAfterComplete
      • jqGridAddEditBeforeInitData
      • jqGridAddEditBeforeShowForm
      • jqGridAddEditAfterShowForm
      • jqGridAddEditInitializeForm
      • jqGridAddEditClickPgButtons
      • jqGridAddEditAfterClickPgButtons
      • jqGridInlineEditRow
      • jqGridInlineAfterSaveRow
      • jqGridInlineSuccessSaveRow
      • jqGridInlineErrorSaveRow
      • jqGridInlineAfterRestoreRow
      • jqGridBeforeEditCell
      • jqGridAfterEditCell
      • jqGridSelectCell
      • jqGridBeforeSaveCell
      • jqGridBeforeSubmitCell
      • jqGridAfterSubmitCell
      • jqGridAfterSaveCell
      • jqGridErrorCell
      • jqGridAfterRestoreCell
      • jqGridSubGridBeforeExpand
      • jqGridSubGridRowExpanded
      • jqGridSubGridRowColapsed
      • jqGridGroupingClickGroup
      • jqGridImportComplete
    • $js_event_handler: JavaScript event handler to the specified event
  • Description:
  • Remark:
    • This is an advanced method.  The event handler is JavaScript.
  • Example:
Comments ( 0 )

Method Chaining

Method chaining is now supported starting version 5.0. It’s not required, but it provides more readable code and reduce amount of source code in a fluent interface.

For example:

With method chaining (v5.0 and later):

1
2
$dg->set_col_title(“lastName”, “Last Name”)->set_col_required(“lastName”, true)->set_co_default(“lastName”, “Robert”);
$dg->enable_edit(“FORM”)->enable_export(true)->enable_resize(true);

Without method chaining:

1
2
3
4
5
6
$dg -> set_col_title(“lastName”, “Last Name”);
$dg -> set_col_required(“lastName”, true);
$dg -> set_co_default(“lastName”, “Robert”);
$dg -> enable_edit(“FORM”);
$dg -> enable_export(true);
$dg -> enable_resize(true);
Comments ( 0 )

set_col_property()

  • Parameters:
  • Description:
    •  This method allows you to directly manipulate column properties.  Since column properties are an array, you can directly change the properties. This is more “closer to the metal” for users who are already familiar with jqGrid colMdel API.
  • Remark:
    • This is an advanced method. In most cases, you don’t need to call this method.  It does not replace existing helper functions such as set_col_date, set_col_currency, etc. We suggest you use those helper functions if you are new to phpGrid.
    • This method is also used to create custom formatter. See a custom formatter example using this method.
  • Example:
1
2
3
4
5
6
7
$dg -> set_col_property("orderNumber", array("name"=>"Order Number", "width"=>40));
$dg -> set_col_property("orderDate", 
                             array("formatter"=>"date",
                                   "formatoptions"=>array("srcformat"=>"Y-m-d","newformat"=>"m/d/Y")));  // display different time format
$dg -> set_col_property("shippedDate",
            array("formatter"=>"date",
                "formatoptions"=>array("srcformat"=>"ISO8601Short","newformat"=>"g:i A")));  // display time only
Comments ( 0 )

set_col_edit_dimension

  • Parameters:
    • $col_name: column name
    • $size/$cols: If the column is a text box, it’s the size that represents number of characters. If the column is a textarea, it’s the value for cols attribute in textarea.
    • $rows: Optional. Number of rows in a textarea
  • Description:
    •  Set input text box size or textarea cols and rows values in edit form.
  • Remark:
  • Example:
1
2
3
4
5
// status is a text fields, 40 indicates the text field is 40 characters long.
$dg -> set_col_edit_dimension("status", 40);

// comments, a text data type, is rendered as textarea with cols set to 50, rows set to 10
$dg -> set_col_edit_dimension("comments", 50, 10);
Comments ( 0 )

set_col_date()

  • Parameters:
    • $col_name: column name
    • $srcformat: source date format, eg. “Y-m-d”
    • $newformat: new date form to be displayed, eg. “n/j/Y”
    • $datepicker_format: datepicker format, eg. “m/dd/yy”
  • Description:
  • Remark:
    • In most cases, you don’t need to call this method because phpGrid automatically formats the data with corresponding data type including date.
    • Only use this method in order to display date format that is different from default date format.
    • To display time only, please use set_col_property instead.
  • Example:
1
2
3
4
// srcFormat: MySql date format is "Y-m-d"
// newFormat: Display date format is "n/j/Y"
// datepicker: Datepicker date format is "yy-mm-dd"
$dg -> set_col_date("orderDate", "Y-m-d", "n/j/Y", "yy-mm-dd");
Comments ( 0 )

set_col_fileupload() *

* Please note this feature is currently only available for Enterprise and Universal licenses.

  • Parameters:
    • $col_name: column name that stores file name
    • $base_url: base URL to the files
    • $physical_path: specify physical path to the folder where the files are uploaded. When PHP is installed as Apache module, this parameter is optional.
  • Description:
    •  The file upload is seamlessly integrated with the edit form using Ajax, no page refresh ever occurred. Files can be uploaded and deleted right within the form.
  • Remark:
    • A single file upload per datagrid is allowed.
    • FORM edit mode only
    • File system only with valid upload_tmp_dir value in php.ini
    • File name column should allow NULL value
    • $physical_path is automatically obtained if PHP running in Apache module, or MUST be provided as fallback
  • Example:
1
2
3
4
$dg -> set_col_fileupload("fileToUpload", "/phpGridx/FileUploadFolder/", "c:\\xampp\www\\phpGridx\\FileUploadFolder\\");

// if PHP is installed as Apache module, the 3rd parameter, $physical_path, can be ignored
$dg -> set_col_fileupload("fileToUpload", "/phpGridx/FileUploadFolder/");
Comments ( 0 )

set_col_default()

  • Parameters:
    • $col_name: column name
    • $default: column default value
  • Description:
    • Set column default value This option is only valid for Form Editing when adding a new record. User can override this entry.
  • Example:
1
$dg -> set_col_default('direction', 'top');
Comments ( 0 )

set_col_wysiwyg()

* only available in Professional and above.

  • Parameters:
    • $col_name: column name
  • Description:
    • Turns simple text field into word processor by replacing textarea with rich text editor. Should only be used for text fields that allows large amount of text such as notes and comment fields.
  • Remark:
    • The field must be a text data type. If not, use set_col_edittype and set edit type to “textarea” first.
  • Example:
1
2
// comments is text data type
$dg->set_col_wysiwyg('comments');
1
2
// news is not a text data type
$dg->set_col_edittype('news', 'textarea')->set_col_wysiwyg('news');

You can set its width height using set_col_edit_dimension method.

phpgrid wysiwyg - form edit

phpgrid wysiwyg - inline edit

Comments ( 0 )

phpGrid Knowledge Base

Please visit phpGrid Knowledge Base on uservoice.

Comments ( 0 )

Conditional Format *

* Please note this feature is not available in Lite and Basic versions.

conditional_format_ss

Conditional formatting example using set_conditional_format() method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

//Format a cell based on the specified condition
$dg->set_conditional_format("orderNumber","CELL",array(
    "condition"=>"eq","value"=>"10107","css"=> array("color"=>"#ffffff","background-color"=>"green")));

$dg->set_conditional_format("customerNumber","CELL",array(
    "condition"=>"eq","value"=>"141","css"=> array("color"=>"red","background-color"=>"#DCDCDC")));

// Format a row based on the specified condition
$dg->set_conditional_format("comments","ROW",array(
    "condition"=>"cn","value"=>"request","css"=> array("color"=>"white","background-color"=>"#4297D7")));    
                     
$dg->set_multiselect(true);
$dg -> display();

See Live Example!

Comments ( 0 )

Horizontal Scroll

Set parameter $shrinkToFit in set_dimension() to false to enable horizontal scroll when the container width is less than the grid width. The scroll bar will not show when the grid width is equal or less than the container width.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
 // change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber",  "Customer No.");
// hide a column
$dg -> set_col_hidden("requiredDate");
// change default caption
$dg -> set_caption("Orders List");
// set export type
$dg -> enable_export('EXCEL');
 // enable integrated search
$dg -> enable_search(true);
// set height and weight of datagrid
$dg -> set_dimension(800, 600, false);
// increase pagination size to 40 from default 20
$dg -> set_pagesize(40);

$dg -> display();

See Live Example!

Comments ( 0 )

Create Excel-Like Grid

Use enable_autowidth() and enable_autoheight() to set datagrid width as the same as the current page dimension to fill the entire screen, similar to what Excel spreadsheet looks like.

1
2
3
4
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_edit('FORM', 'CRUD');
$dg->enable_autowidth(true)->enable_autoheight(true);
$dg -> display();

See Live Example!

Hint:
Call enable_kb_nav() method to move between rows using only keyboard.

Comments ( 0 )

Conditional Value *

* Please note this feature is not available in Lite and Basic versions.

Conditional Value is similar to conditional format (see set_conditional_format but with simplier set of features. Use set_conditional_value() to dynamically display a value when specific condition is met. The conditional value can be text, HTML, or even CSS style.

You can use conditional value with data bar in the same grid.

1
2
3
4
5
6
7
8
.tstyle
{
display:block;background-image:none;margin-right:-2px;margin-left:-2px;height:14px;padding:5px;background-color:green;color:navy;font-weight:bold
}
.fstyle
{
display:block;background-image:none;margin-right:-2px;margin-left:-2px;height:14px;padding:5px;background-color:yellow;color:navy
}

PHP Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$dg = new C_DataGrid("SELECT * FROM joborders", "jobNumber", "joborders");
$dg -> set_col_title("jobNumber", "Job Number");
$dg -> set_col_title("jobDescription", "Description");
$dg -> set_col_title("status", "Status");              
$dg -> set_col_title("percentComplete", "Progress (%)");
$dg -> set_col_title("isClosed", "Closed");
 
$dg->set_conditional_value("isClosed", "==1", array(
    "TCellValue"=>"<img src='SampleImages/checked.gif' />",
    "FCellValue"=>"<img src='SampleImages/unchecked.gif' />"));
 
$dg->set_conditional_value("status", "=='Complete'", array(
    "TCellStyle"=>"tstyle",
    "FCellStyle"=>"fstyle"));
 
$dg->enable_edit('INLINE', 'CRUD');
 
$dg->set_multiselect(true);
$dg -> set_databar("percentComplete","red");
$dg -> display();

See Live Example!

Comments ( 0 )

In-cell Data Bar *

databar_ss

Bar chart is a great way to visualize numeric data. phpGrid now supports bar chart natively without 3rd party plugin using set_databar() method. You can have multiple data bar in a datagrid.

For complex data visualization, we recommend PHP Chart. Please visit PHP Chart for live demo.

1
2
3
4
5
6
7
8
9
10
11
12
$dg = new C_DataGrid("SELECT * FROM joborders", "jobNumber", "joborders");
$dg -> set_col_title("jobNumber", "Job Number");
$dg -> set_col_title("jobDescription", "Description");
$dg -> set_col_title("status", "Status");              
$dg -> set_col_title("percentComplete", "Progress (%)");
$dg -> set_col_title("isClosed", "Closed");
 
$dg -> set_databar("percentComplete","blue");
$dg -> set_databar("jobNumber","blue");
$dg -> enable_edit("INLINE", "CRUD");
 
$dg -> display();

See Live Example!

Comments ( 0 )

setCallBack

  • Parameters:
    • $callback_str: callback string
  • Description:
    • Used for pass setting between 3rd party system and phpGrid using AJAX callback. Read the comments in file callbackstr.php for usage
Comments ( 0 )

set_form_dimension()

  • Parameters:
    • $f_width: form width. The default value is ’400px’
    • $f_height: form height. The default is -1 so it is automatically adjusted to number of rows.
  • Description:
    • Set edit FORM width and height during edit.
  • Remark:
    • The default $f_height value is -1 (negative one) so it is automatically adjusted to number of rows.
  • Example:
1
$dg->set_form_dimension('500px');
Comments ( 0 )

enable_kb_nav() – beta

  • Parameters:
    • $is_enabled: true or false
  • Description:
    • Enable navigation by keyboard using up and down arrow keys.
  • Remark:
    • This is a beta feature currently. Not recommended using it in production.
  • Example:
1
$dg->enable_kb_nav(true);
Comments ( 0 )

get_display()

  • Parameters:
    • $add_script_includeonce: true or false. Whether or not to include script header.
  • Description:
    • It returns the grid body with options to include script header. It is useful for MVC framework integration such as Joomla! and Drupal.
  • Remark:
    • The get_display() function should be called AFTER display() is called so that the grid body and header is saved to an internal output buffer that can be later retrieved by get_display().

Example:

1
2
3
4
5
6
7
8
9
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg -> display(false);  // do not render display
$grid = $dg -> get_display(false);  // do not include required javascript libraries until later with with display_script_includeonce method.

// other page content goes here
/* ........................... */

$dg -> display_script_includeonce();
echo $grid;
Comments ( 0 )

enable_autowidth()

  • Parameters:
    • $autowidth: true or false
  • Description:
    • Use this method to set datagrid width as the same as the current window width.
    • Automatically resizes based on window width (verison 6+)
  • Example:
1
$dg -> enable_autowidth(true);
Comments ( 0 )

set_conditional_value() *

* Please note this feature is not available in Lite and Basic versions.

  • Parameters:
    • $col_name: Name of the column. Its value is used as the left operand for comparison.
    • $condition: comparison condition that compares given left and rigth hand operand with comparison operator . e.g. ” > 10 “; ” = ‘abc’”; ” != true ” etc.
    • $formatoptions:  an array stores CSS style format options. The value can be text, HTML, or even CSS class. See example.
      • TCellValue: value to display when condition is True
      • FCellValue: value to display when condition is False
  • Description:
    • It formats and displays value in a cell when specified condition is met. You can think it as a simplified version to set_conditional_format() method.
  • Remark:
    • The condition is not “stackable” for a single column. In other words, only one condition can be applied to any giving column.
  • Example:

Note that tstyle and fstyle are CSS styles used as the formatoptions.

1
2
3
4
5
6
7
8
.tstyle
{
display:block;background-image:none;margin-right:-2px;margin-left:-2px;height:14px;padding:5px;background-color:green;color:navy;font-weight:bold
}
.fstyle
{
display:block;background-image:none;margin-right:-2px;margin-left:-2px;height:14px;padding:5px;background-color:yellow;color:navy
}

PHP Code

1
2
3
4
5
6
7
$dg->set_conditional_value("isClosed", "==1", array(
"TCellValue"=>"<img src="SampleImages/checked.gif" alt="" />",
"FCellValue"=>"<img src="SampleImages/unchecked.gif" alt="" />"));

$dg->set_conditional_value("status", "=='Complete'", array(
"TCellStyle"=>"tstyle",
"FCellStyle"=>"fstyle"));
Comments ( 0 )

set_databar() *

* Please note this feature is not available in Lite and Basic versions.

  • Parameters:
    • $col_name: Name of column to display data bar. It should be numeric type
    • $color: Data bar color
  • Description:
    • Creates a data bar with gradient effect on a specified column. For complex data visualization, PHP Chart is highly recommended.
  • Remark:
    • For complex data visualization, we recommend phpGrid sister product, PHP Chart, a highly customizable PHP charting solution.
  • Example:
1
$dg -> set_databar("percentComplete","red");
Comments ( 0 )

Display Non-English Characters

If database is created in charset other than default charset, e.g. “latin1″, some characters may be displayed as “?” in the grid. To properly display non-English characters, such as Spanish, Fresh, or Chinese, in MySQL, PostgreSQL, and Oracle you can define the character set value in ‘DB_CHARSET’ variable in conf.php.

In conf.php, set the DB_CHARSET to the corresponding charsets in your database.

version 6+ (with “PHPGRID_” prefix):

1
2
3
4
5
6
define('PHPGRID_DB_HOSTNAME','hostname'); // database host name
define('PHPGRID_DB_USERNAME', 'username'); // database user name
define('PHPGRID_DB_PASSWORD', 'password'); // database password
define('PHPGRID_DB_NAME', 'sampledb'); // database name
define('PHPGRID_DB_TYPE', 'mysql'); // database type
define('PHPGRID_DB_CHARSET','utf8'); // OPTIONAL. Leave blank to use the default charset

version 5.5.x and below:

1
2
3
4
5
6
define('DB_HOSTNAME','hostname'); // database host name
define('DB_USERNAME', 'username'); // database user name
define('DB_PASSWORD', 'password'); // database password
define('DB_NAME', 'sampledb'); // database name
define('DB_TYPE', 'mysql'); // database type
define('DB_CHARSET','utf8'); // OPTIONAL. Leave blank to use the default charset

Add the Meta element to the HTML head node and set character encoding to UTF-8. Note it’s important that the actual file must also be saved as UTF-8 encoding.

1
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

See Live Example!

Comments ( 0 )

Reference to Multiple Databases *

* Please note this feature is only available in paid versions.

phpGrid nows supports data source from more than one database on a single page. Simply use the 4th parameter in constructor (C_DataGrid) to overwrite the initial database settings in conf.php.

In the following example, grid A binds a table from one database, while grid B binds another table from a completely different database, all on the same page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$gridA = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$gridA->enable_edit("INLINE","CRUD");
$gridA -> display();

// overwrite database settings with another database.
$gridB = new C_DataGrid("SELECT * FROM names", "id", "names",
                        array("hostname"=>"localhost",
                            "username"=>"USER2",
                            "password"=>"PASSWORD",
                            "dbname"=>"utf8db",
                            "dbtype"=>"mysql",
                            "dbcharset"=>"utf8"));

$gridB->enable_edit("INLINE","CRUD");
$gridB -> display();

See Live Example!

Comments ( 0 )

Locale Setting

If you are using phpGrid in a language rather English, you may want the datagrid to display appropriate system text used by phpGrid such as “Submit”, “Next”, “Page”, “Find” etc. The language locale files are located in js\src\i18n directory.

1
2
3
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->set_locale('fr');        
$dg -> display();

See Live Example!

Comments ( 0 )

set_conditional_format() *

* Please note this feature is not available in Lite and Basic versions.

  • Parameters:
    • $col_name: column name
    • $type: CELL or ROW
    • $formatoptions: array that stores condition and css style options
  • Description:
    • Format a cell based on the specified condition. CELL formats the a single cell. ROW formats the entire row.
       

      $formatoption is the most important parameter which itself is an array contains three elements:

      • condition,
      • value,
      • css.
       

      The elements are described in details as the following:

      The first parameter, condition, has the following available value.

      eq: Equals
      ne: Not Equals
      lt: Less than
      le: Less than or Equal
      gt: Greater than
      ge: Greater than or Equal
      cn: Contains
      nc: Does not Contain
      bw: Begins With
      bn: Not Begins With
      ew: Ends With
      en: Not Ends With

      The second parameter, value, is the comparing value in the right operand.

      css parameter in $formattoptions is an array collection that contains css style element for current cell or row.

  • Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
//Format a cell based on the specified condition
$dg->set_conditional_format("orderNumber","CELL",array("condition"=>"eq",
                                                      "value"=>"10107",
                                                      "css"=> array("color"=>"#ffffff","background-color"=>"green")));

$dg->set_conditional_format("customerNumber","CELL",array("condition"=>"eq",
                                                  "value"=>"141",
                                                  "css"=> array("color"=>"red","background-color"=>"#DCDCDC")));

// Format a row based on the specified condition
$dg->set_conditional_format("comments","ROW",array("condition"=>"cn",
                                                  "value"=>"request",
                                                  "css"=>array("color"=>"white","background-color"=>"#4297D7")));
Comments ( 0 )

set_locale()

  • Parameters:
    • $locale: language locale. List of values are: bg, cat, cn, cs, de, dk, el, en, fa, fi, fr, he, is, it, ja, nl, no, pl, pt, ro, ru, sp, sv, tr, ua.
  • Description:
    • If you are using phpGrid in a language rather English, you may want the datagrid to display appropriate system text used by phpGrid such as “Submit”, “Next”, “Page”, “Find” etc. The language locale files are located in js\src\i18n directory.
  • Example:
1
$dg -> set_locale("fr"); // French locale
Comments ( 0 )

enable_advanced_search()

  • Parameters:
    • $has_adsearch: boolean value. Set true to enable advanced search
  • Description:
    • Advanced search is different from the integrated search (see enable_search()). It can apply mulitple conditionals to a single field during advanced search. It is super flexible and easy to use.
  • Example:
1
$dg -> enable_advanced_search(true);

Comments ( 0 )

set_col_align()

  • Parameters:
    • $col_name: column name
    • $align: alignment direction: “left”, “center”, or “right”.
  • Description:
    • Horizontal alignment of text in a column. The default alignment is left.
  • Example:
1
$dg -> set_col_align('amount', 'right');
Comments ( 0 )

ABS_PATH (prior to version 4.3)

What is ABS_PATH

Pay attention to ABS_PATH variable. Make sure it is the absolute URL to phpGrid folder in the web browser. This value tells your script where to find phpGrid library on the web server.

For instance, if the URL to get to the phpGrid is http://www.yoursite.com/phpGrid, or http://localhost/phpGrid, the ABS_PATH should be “/phpGrid”;

1
define('ABS_PATH', '/phpGrid');

or if the URL to phpGrid is http://www.yoursite.com/admin/phpGrid, or http://localhost/admin/phpGrid, the ABS_PATH should be “/admin/phpGrid”,

1
define('ABS_PATH', '/admin/phpGrid');

and so forth.

Comments ( 0 )

Nested Master Detail Datagrid *

* Please note this feature is only available in paid versions.

The master detail datagrid can be nested to show relationships for multilevel hierarchal data. The example shows only three levels, though the number of nested levels can be unlimited.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//suppliers master-detail
$sg = new C_DataGrid("SELECT * FROM suppliers","supplierCode","suppliers");
$sg->set_sql_key("supplierName");

//supplier detail2: products
$sg_d1 = new C_DataGrid("SELECT productCode,productName,productDescription,quantityInStock,MSRP,productVendor FROM products","productCode","products");

//nested grid-level 3 for products
$sg_d1_n1 = new C_DataGrid("SELECT * FROM productparts","productCode","productparts");

//set detail for products
$sg_d1->set_masterdetail($sg_d1_n1, 'productCode');

//set detail 2 for suppliers
$sg->set_masterdetail($sg_d1, 'productVendor');

$sg->display();

Note:
in example, click on #4 on suppliers, then S10_4698 in the first detail grid to reveal the third nested detail grid.
See Live Example!

Comments ( 0 )

Master with Multiple Detail Grids *

* Please note this feature is only available in paid versions.

phpGrid supports a master grid with multiple detail grids. There is no limit on how many detail grids a master datagrid can have. Detail grids are defined the way as the master datagrid does. Click on example link to see it in action!

Note that you can have master and detail grids from the same database table as long as different SQL alias are used.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
//suppliers master-detail
$sg = new C_DataGrid("SELECT * FROM suppliers","supplierCode","suppliers");
$sg->set_sql_key("supplierName");
 
//supplier detail 1: product lines
$sg_d1 = new C_DataGrid("SELECT * FROM supplierproductlines","supplierCode","supplierproductlines");
 
$sg->set_masterdetail($sg_d1, 'supplierName');
 
//supplier detail2: products
$sg_d2 = new C_DataGrid("SELECT productCode,productName,productDescription,quantityInStock,MSRP,productVendor FROM products","productCode","products");
 
//set detail 2 for suppliers
$sg->set_masterdetail($sg_d2, 'productVendor');
$sg->display();

See Live Example!

Comments ( 0 )

Column Grouping with Summary *

* Please note this feature is only available in paid versions.

Simple Grouping with data-small

phpGrid can group data by columns. Simply define a column name on which grouping occur using method set_group_properties().

In addition to define grouping column, set summary field using method set_group_summary() by passing the column name and summary type, which can be sum, count, avg, min, or max.

1
2
3
4
5
6
7
$dg = new C_DataGrid("SELECT * FROM payments", "customerNumber", "orders");

$dg->set_group_properties('customerNumber');
$dg->set_group_summary('amount','sum');
$dg->set_group_summary('checkNumber','count');

$dg -> display();

See Live Example!

Comments ( 0 )

enable_rownumbers()

  • Parameters:
    • $has_rownumbers: boolean value indicating whether row number is displayed.
  • Description:
    • Display row number before each row.
  • Example:
1
$dg -> enable_rownumbers(true);
Comments ( 0 )

set_group_summary() *

* Please note this feature is only available in paid versions.

  • Parameters:
    • $column_name: column name for grouping
    • $summaryType: summary type. It can be one of the types below
      • sum
      • count
      • avg
      • min
      • max
  • Description:
    • Display group summary of a specific summary type, such as sum or max, in the group summary footer.
  • Remark:
  • Example:
1
2
3
$dg -> set_group_properties('customerNumber');
$dg -> set_group_summary('amount','sum');
$dg -> set_group_summary('checkNumber','count');
Comments ( 0 )

set_group_properties() *

* Please note this feature is only available in paid versions.

  • Parameters:
    • $column_name: column name for grouping
    • $groupCollapsed: boolean value indicating whether to expand or collapse grouping when displayed.  The default is false.
    • $showSummaryOnHide: boolean value indicating whether to display group summary when grouping is collapsed. The default is true.
  • Description:
    • Group grid values simply by specifying a column name.
  • Remark:
    • Single level grouping is currently supported.
    • When grouping is enabled, the followings methods are disabled:
      • set_scroll();
      • enable_rownumbers();
      • set_subgrid();
  • Example:
1
$dg -> set_group_properties('customerNumber');
Comments ( 0 )

set_col_width()

  • Parameters:
    • $col_name: column name
    • $width: width
  • Description:
    • Specify column width in pixel. The width should be an integer value.
  • Example:
1
$dg -> set_col_width("comments", 500);
Comments ( 0 )

Multiple PHP Editable Datagrid

Did you know it is possible to have multiple editable grids in a single page? No need to separate your data management into multiple pages. You can now performa all your content administrative tasks in one page.

Note that you can have multiple datagrids of the same SQL table on a single page. However, you must give different SQL alias to each table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", 'Orders');
$dg->display();

$dg2 = new C_DataGrid("select * from employees", "employeeNumber", "Employees");
$dg2->display();

$dg3 = new C_DataGrid("select * from offices", "officeCode", "Offices");
$dg3->display();

$dg4 = new C_DataGrid("select * from productlines", "productLine", "ProductLines");
$dg4->display();

$dg5 = new C_DataGrid("select * from customers", "customerNumber", "Customers");
$dg5->display();

See Live Example!

Comments ( 0 )

Subgrid

subgrid_ss

Defining a subgrid is essentially the same as setting up the master detail grid as described in our previous example. Again, only a single line of code change is required by calling set_subgrid().

Starting version 6, nested, drill-down subgrids are supported!

Important:
The difference between a master detail datagrid and a subgrid is that the subgrid displays inline grid rather than in a separate datagrid. In addition, it’s possible to nest master detail grids as well as subgrids. Nested subgrid currently is not yet supported.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// enable edit
$dg->enable_edit("INLINE", "CRUD");

// second grid as detail grid. Notice it is just another regular phpGrid with properites.
$sdg = new C_DataGrid("SELECT * FROM orderdetails", array("orderLineNumber", "productCode"), "orderdetails");
$sdg->enable_edit("INLINE", "CRUD");

// second grid as detail grid. Notice it is just another regular phpGrid with properites.
$sdg2 = new C_DataGrid("SELECT * FROM products", array("productCode"), "products");
$sdg2->enable_edit("INLINE", "CRUD");

// define master detail relationship by passing the detail grid object as the first parameter, then the foriegn key name.
$sdg->set_subgrid($sdg2, 'productCode');
$dg->set_subgrid($sdg, 'orderNumber');

$dg->display();

See 3-Level Nested Subgrid Example!

Comments ( 0 )

Master Detail Grid *

* Please note this feature is only available in paid versions.

The phpGrid went extra miles to make creating the master detail datagrid a super simple task. There is essentially a simple function to set_masterdetail() to set the master detail relationship between two datagrids, and have them work interactively.

The detail grid is a regular phpGrid object and can have the same methods like any other datagrid, such as description title, sort, and update etc. The detail grid renders dynamically based on the row selected in parent grid. A master grid can have one detail grid one or more detail datagrids.

Note that you can have master and detail grids from the same database table as long as different SQL alias are used.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// enable edit
$dg->enable_edit("INLINE", "CRUD");

// hide a column
$dg -> set_col_hidden("requiredDate");

// read only columns, one or more columns delimited by comma
$dg -> set_col_readonly("orderDate, customerNumber");

// required fields
$dg -> set_col_required("orderNumber, customerNumber");

// multiple select
$dg -> set_multiselect(true);
 
// second grid as detail grid. Notice it is just another regular phpGrid with properites.
$sdg = new C_DataGrid("SELECT orderNumber,productCode,quantityOrdered,priceEach FROM OrderDetails", "orderNumber", "OrderDetails");
$sdg->set_col_title("orderNumber", "Order No.");
$sdg->set_col_title("productCode", "Product Code");
$sdg->set_col_title("quantityOrdered", "Quantity");
$sdg->set_col_title("priceEach", "Unit Price");
$sdg->set_col_dynalink("productCode", "http://www.example.com/", "orderLineNumber", '&foo');
$sdg->set_col_format('orderNumber','integer', array('thousandsSeparator'=>'','defaultValue'=>''));
$sdg->set_col_currency('priceEach','$');

// define master detail relationship by passing the detail grid object as the first parameter, then the foriegn key name.
$dg->set_masterdetail($sdg, 'orderNumber');

$dg->display();

See Live Example!

Comments ( 0 )

Set Edit HTML Control

After enabling edit, you can set the type of HTML control with set_col_edittype() for any editable column used. List of Available controls are:

  • text
  • textarea
  • select
  • checkbox
  • password
  • autocomplete (version 6+) – New!

Text and textarea are set automatically based on the database access library ADOdb metatype as the default edit control.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$dg = new C_DataGrid("select * from employees", "employeeNumber", "Employees");
$dg -> set_col_title("employeeNumber", "Emp No.");
$dg -> set_col_title("lastName", "Last Name");
$dg -> set_col_title("firstName", "First Name");
$dg -> set_col_title("isActive", "Active?");
$dg -> set_col_format("email", "email");
$dg -> enable_edit("FORM", "CRUD");
$dg -> set_row_color("","","#DDEEF5");
$dg -> set_col_hidden('employeeNumber',false);

$dg -> set_col_edittype("isActive", "checkbox","1:0");
$dg -> set_col_edittype("reportsTo", "select", "Select employeeNumber, concat(firstName, ' ', lastName) from employees",false);
$dg -> set_col_edittype("officeCode", "autocomplete", "Select officeCode,city from offices",false);

// alternatively, for select type, you can hard-code the key value pair value
// $dg -> set_col_edittype("officeCode", "select", "1:San Francisco;2:Boston;3:NYC;4:Paris;5:Tokyo;6:Sydney;7:London");

$dg -> display();

See Live Example!

Comments ( 0 )

Select Multiple Records

You can select multiple records with set_multiselect() method. When multiselect is enabled, a checkbox is shown to the left of each row. It is currently designed to delete multiple records. More functionalities will be added to multiselect in the future.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");
 
// enable edit
$dg -> enable_edit("INLINE", "CRUD");

// hide a column
$dg -> set_col_hidden("requiredDate");

// read only columns, one or more columns delimited by comma
$dg -> set_col_readonly("orderDate, customerNumber");

// required fields
$dg -> set_col_required("orderNumber, customerNumber");

// multiple select
$dg -> set_multiselect(true);
 
$dg -> display();

Optional JavaScript to retrieve selected rows

1
2
3
4
5
6
7
<script type="text/javascript">
function ShowSelectedRows(){
    var rows = getSelRows();
    // replace your own javascript here
    alert(rows);
}
</script>

To get value from a different cell of the select row:

1
$('#TABLE_NAME').jqGrid('getCell',row_id,'COLUMN NAME');

See Live Example!

Comments ( 0 )

Required fields

To set fields as required in an editable grid, use set_col_required() method. If required fields are left blank, an “* required” message will be displayed. One more more columns can be specified as required fields in this method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");
 
// enable edit
$dg -> enable_edit("INLINE", "CRUD");

// hide a column
$dg -> set_col_hidden("requiredDate");

// read only columns, one or more columns delimited by comma
$dg -> set_col_readonly("orderDate, customerNumber");

// required fields
$dg -> set_col_required("orderNumber, customerNumber");
 
$dg -> display();

See Live Example!

To test, click on any row to toggle inline edit, leave a required fields blank, then hit Enter.

Comments ( 0 )

Read only fields

Sometimes we don’t want certain fields to be editable, then we can use set_col_readonly() method.

Note that you can set more than one columns to be read only using this method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");

// enable edit
$dg ->enable_edit("INLINE", "CRUD");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// read only columns, one or more columns delimited by comma
$dg -> set_col_readonly("orderDate, customerNumber");
 
$dg -> display();

Click on row to see the fields that are set to read only.

See Live Example!

Comments ( 0 )

Editable Datagrid *

* Please note this feature is only available in paid versions.

The PHP datagrid is not editable by default. You can enable edit by simply calling enable_edit(). Currently, two types of edit modes are supported, FORM edit and INLINE. When Form edit mode is used, additional icons appear in the data grid footer for editing. When set to inline mode, the cells in the selected row become editable, and must press Enter key to save changes. Only a single row can be edited for either mode.

When edit is enabled in a grid, all of the CRUD operations, Create, Read, Update, and Delete, are supported by default. You can restrict types of edit operations permitted by specifying the second parameter in enable_edit(). For example, to disallow Delete, change it to $operations=’CRU’.

Please note save is disabled in the online demo for security reason.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// enable edit
$dg -> enable_edit("FORM", "CRUD");

$dg -> display();

See Live Example!

Update:
Beginning version 4.4, you can double click to edit whenever edit mode is enabled.
The toolbar can also be moved to top with a little javascript. See KB “Move Toolbar to Top of the Grid“.

Comments ( 0 )

Interactive Grid Resizing

When enabled, the small triangle icon is displayed at the right bottom corner of the php data grid. The grid can be resized by simply click and drag the icon using mouse. This is more convenient than setting the width and height in the code.

This function is currently in beta.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
$dg= new C_DataGrid("select * from products", "productCode", "productCode");
$dg ->set_col_title("productCode", "Product Code");
$dg ->set_col_title("productName", "Product Name");
$dg ->set_col_title("productLine", "Product Line");

// display static Url
$dg ->set_col_link("productUrl");                                            

// dynamic url. e.g.http://www.example.com/?productCode=101&foo=bar
$dg -> set_col_dynalink("productCode", "http://www.example.com/", "productCode", '&foo=bar');

// column format
$dg -> set_col_currency("buyPrice");
$dg -> set_col_format("quantityInStock", "integer", array("thousandsSeparator" => ",",
                                                          "defaultValue" => "0"));  
$dg -> set_col_format("MSRP", 'currency', array("prefix" => "$",
                                                "suffix" => '',
                                                "thousandsSeparator" => ",",
                                                "decimalSeparator" => ".",
                                                "decimalPlaces" => '2',
                                                "defaultValue" => '0.00'));

// the above line is equivalent to the following helper function                        
$dg -> set_col_currency("MSRP", "$", '', ",",".", "2", "0.00");                                      

// display image
$dg -> set_col_img("Image");

// enable resize by dragging mouse
$dg -> enable_resize(true);
                                                 
$dg -> display();

See Live Example!

Comments ( 0 )

Image Display

image_display

Display image is easy in phpGrid. Simply set the column name in set_col_img(), phpGrid will take care of the rest.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
$dg = new C_DataGrid("select * from products", "productCode", "productCode");
$dg -> set_col_title("productCode", "Product Code");
$dg -> set_col_title("productName", "Product Name");
$dg -> set_col_title("productLine", "Product Line");

// display static Url
$dg -> set_col_link("productUrl");                                            

// dynamic url. e.g.http://www.example.com/?productCode=101&foo=bar
$dg -> set_col_dynalink("productCode", "http://www.example.com/", "productCode", '&foo=bar');

// column format
$dg -> set_col_currency("buyPrice");
$dg -> set_col_format("quantityInStock", "integer", array("thousandsSeparator" => ",",
                                                          "defaultValue" => "0"));  
$dg -> set_col_format("MSRP", 'currency', array("prefix" => "$",
                                                "suffix" => '',
                                                "thousandsSeparator" => ",",
                                                "decimalSeparator" => ".",
                                                "decimalPlaces" => '2',
                                                "defaultValue" => '0.00'));

// the above line is equivalent to the following helper function                        
$dg -> set_col_currency("MSRP", "$", '', ",",".", "2", "0.00");                                      

// display image
$dg -> set_col_img("Image");
                                                 
$dg -> display();


You can also further adjust image display options such as width, height and border with CSS using set_col_property method. The CSS will only apply the specific column.

1
2
3
<style>
    .image_css img{width: 40pt;border:1px solid black;}
</style>
1
$dg->set_col_property('Image', array("classes"=>'image_css');

Alternatively, you resize images for the entire grid with CSS.

See Live Example!

Comments ( 0 )

Data Format Display

phpGrid comes with several frequently used data formatter through set_col_format() method. Different data format has different format options. In most cases, users only need to set column formatting for “integer”, “number”, and “mail” using this method. For “currency’,”link”, and “showlink” formats, phpGrid provides a number of helper functions to make formatting simpler and easier. Refer to documentations on column formatter helper methods.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$dg = new C_DataGrid("select * from products", "productCode", "productCode");
$dg -> set_col_title("productCode", "Product Code");
$dg -> set_col_title("productName", "Product Name");
$dg -> set_col_title("productLine", "Product Line");

// display static Url
$dg -> set_col_link("productUrl");                                            

// dynamic url. e.g.http://www.example.com/?productCode=101&foo=bar
$dg -> set_col_dynalink("productCode", "http://www.example.com/", "productCode", '&foo=bar');

// column format
$dg -> set_col_currency("buyPrice");
$dg -> set_col_format("quantityInStock", "integer", array("thousandsSeparator" => ",",
                                                          "defaultValue" => "0"));  
$dg -> set_col_format("MSRP", 'currency', array("prefix" => "$",
                                                "suffix" => '',
                                                "thousandsSeparator" => ",",
                                                "decimalSeparator" => ".",
                                                "decimalPlaces" => '2',
                                                "defaultValue" => '0.00'));

// the above line is equivalent to the following helper function                        
$dg -> set_col_currency("MSRP", "$", '', ",",".", "2", "0.00");    
                                                                                   
$dg -> display();

See Live Example!

Comments ( 0 )

Display Dynamic URL

From example above, we learn that phpGrid can display simple, static URL using set_col_link() method. However, it is often for database drive webpage that URL is dynamically formed based on parameters value. Method set_col_dynalink() is designed specially for this purpose.

The following demonstrates passing the column orderNumber as the dynamic value used as part of the URL.

1
2
3
4
5
6
7
8
9
10
11
12
$dg = new C_DataGrid("select * from products", "productCode", "productCode");
$dg -> set_col_title("productCode", "Product Code");
$dg -> set_col_title("productName", "Product Name");
$dg -> set_col_title("productLine", "Product Line");
         
// display static Url
$dg -> set_col_link("productUrl");
                 
// display dynamic url. e.g.http://www.example.com/?productCode=101&foo=bar
$dg -> set_col_dynalink("productCode", "http://www.example.com/", "productCode", '&foo=bar');                                                                  

$dg -> display();

See Live Example!

Comments ( 0 )

Display Hyperlink

You can display the text in column as static hyperlink by calling set_col_link() and passing the column name. Note this method only display static URL stored in database field. To display dynamic URL links based in variables, see the next example on set_col_dynalink() method.

Please note we are using Products table in this example.

1
2
3
4
5
6
7
8
9
$dg = new C_DataGrid("select * from products", "productCode", "productcode");
$dg -> set_col_title("productCode", "Product Code");
$dg -> set_col_title("productName", "Product Name");
$dg -> set_col_title("productLine", "Product Line");

// display static Url
$dg -> set_col_link("productUrl");                                            
                                                                                     
$dg -> display();

See Live Example!

Comments ( 0 )

Theme Roller *

* Please note this feature is only available in paid versions.
** Premium Themes are now available to Enterprise and Universal Licenses customers

The overall look and feel of the phpGrid can be easily changed using set_theme() method. The theme elements are completely customizable through CSS. Theme files, css and images, are stored in css folder.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_caption("Orders List");

// set export type
$dg -> enable_export('EXCEL');

// enable integrated search
$dg -> enable_search(true);

// set height and weight of datagrid
$dg -> set_dimension(800, 600);

// increase pagination size to 40 from default 20
// $dg -> set_pagesize(40);

// use vertical scroll to load data
$dg -> set_scroll(true);

// change theme
$dg -> set_theme('dot-luv');
 
$dg -> display();

See Live Example!

Premium Themes are now available to Enterprise and Universal Licenses customers

  • Aristo
  • Cobalt
Comments ( 0 )

Load Data with Vertical Scroll

In contrast to traditional pagination method to browse through data, you can use vertical scroll as alternative way to load data. It is done by set parameter in set_scroll() to true. When scroll position changes, phpGrid makes ajax call in the background and refresh the content in the grid. As a result, pagination is disabled automatically when this method is used.

Note that it does not load everything at once and only hold all the items from the start through to the latest point ever visited. This improves page loading time while prevents memory leaks from happening.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_caption("Orders List");

// set export type
$dg -> enable_export('EXCEL');

// enable integrated search
$dg -> enable_search(true);

// set height and weight of datagrid
$dg -> set_dimension(800, 600);

// increase pagination size to 40 from default 20
$dg -> set_pagesize(40);

// use vertical scroll to load data
$dg -> set_scroll(true);
 
$dg -> display();

See Live Example!

Comments ( 0 )

Advanced Search

phpGrid also includes advanced search. By default, this feature is not enabled. To enable search use enable_advanced_search() method with parameter set to true. Once enabled, the advanced search can be toggled with the advanced search button on the footer.

The advanced search can search on multiple fields simultaneously with different comparison operators.

1
2
3
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_advanced_search(true);
$dg -> display();

See Live Example!

Comments ( 0 )

Datagrid Pagination

By default, the pagination size is 20. It means maximum of 20 records can be displayed in a page. This value can be easily changed with set_pagesize() method. Be aware that pagination is disabled when set_scroll() is set to true. See the next example on set_scroll() for more information.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_caption("Orders List");

// set export type
$dg -> enable_export('EXCEL');

// enable integrated search
$dg -> enable_search(true);

// set height and weight of datagrid
$dg -> set_dimension(800, 600);

// increase pagination size to 40 from default 20
$dg -> set_pagesize(40);
 
$dg -> display();

See Live Example!

Comments ( 0 )

Set Height and Width

Use set_dimension() method to specify data grid initial height and width. The default height and width is 400 by 300 pixels. In this example, the width is set to 800 pixel, and height is set to 600 pixel.

Wait, It looks funny! The datagrid height is taller than the space used by the number of rows in a page. To fix this, we can set a bigger pagination to accommodate a taller grid. See the next example on pagination.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_caption("Orders List");

// set export type
$dg -> enable_export('EXCEL');

// enable integrated search
$dg -> enable_search(true);

// set height and weight of datagrid
$dg -> set_dimension(800, 600);
 
$dg -> display();

See Live Example!

Comments ( 0 )

Integrated Search

phpGrid includes integrated search. By default, this feature is not enabled. To enable search use enable_search() method with parameter set to true. Once enabled, the integrated search can be toggled with the search button on the footer.

Notice the “status” is automatically rendered as a drop-down in the integrated search(v5.5+). Talking about making your life easy? :)

To always display the search toolbar, please see Always display integrated search toolbar on KB.

integrated_search_dropdown

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_captin("Orders List");

// set export type and edit select type
$dg -> enable_export('EXCEL');
$dg -> enable_edit('FORM', 'CRUD');
$dg -> set_col_edittype('status', 'select', 'Open:Open;Shipped:Shipped;Cancelled:Cancelled;Disputed:Disputed;On Hold:On Hold');

// enable integrated search
$dg -> enable_search(true);
 
$dg -> display();

See Live Example!

Comments ( 0 )

Excel, HTML, PDF, or CSV Export

phpGrid currently supports export in native Excel format, CSV, PDF, and HTML format. When the export feature is enabled, phpGrid displays an export icon in the footer. Also see enable_export().

PDF and CSV formats are now supported (Available to Professional License and up) !

export_icons

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
// hide a column
$dg->set_col_hidden("requiredDate");
// change default caption
$dg->set_caption("Orders List");
// EXCEL export
$dg->enable_export('PDF');
$dg->display();


$dg2 = new C_DataGrid("select * from customers", "customerNumber", "Customers");
// PDF export
$dg2->enable_export('EXCEL');
$dg2->display();

See Live Example!

Comments ( 0 )

Datagrid Caption

By default, phpGrid displays table name as the caption. It can be changed using set_caption(). You can also hide the caption by using an empty string “” as the caption.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg->set_col_title("orderNumber", "Order No.");
$dg->set_col_title("orderDate", "Order Date");
$dg->set_col_title("shippedDate", "Shipped Date");
$dg->set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");

// change default caption
$dg -> set_caption("Orders List");
 
$dg -> display();

See Live Example!

Comments ( 0 )

Hide Column

For columns need not to be shown, such as primary key, use method set_col_hidden() to hide those columns. To hide column in both datagrid and form, set the second parameter to false. This is useful to hide the auto increment primary key field from edit form.

Very Important:
The data are still sent to web browser but hidden using CSS display:none. For sensitive data such as passwords and SSN etc, do not use this method, instead do not include those fields in your SQL at all.
 
1
2
3
4
5
6
7
8
9
10
11
12
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");
 
// hide a column
$dg -> set_col_hidden("requiredDate");
 
$dg -> display();

See Live Example!

Comments ( 0 )

Descriptive Column Headers

By default phpGrid pulls out the data field name defined in database as the header for each column. You can simply change the title using method set_col_title().

1
2
3
4
5
6
7
8
9
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");

$dg -> display();

See Live Example!

Comments ( 0 )

set_theme()

Note: starting version 6, you can set a global constant “THEME” in conf.php to change all of your datagrid themes without calling set_theme() method individually.

  • Parameter(s):
    • $theme_name: Theme name
  • Description:
    • This is the phpGrid “Theme Roller”. phpGrid bundles the following themes from jQuery UI:
      • dot-luv
      • excite-bike
      • flick
      • overcast
      • pepper-grinder
      • redmond
      • smoothness
      • start
      • ui-darkness
      • ui-lightness
      • aristo ————– (Premium theme) *
      • cobalt ————– (Premium theme) *
    • The default theme is ‘start‘.
  • Example:
1
$dg -> set_theme('flick');

* Premium themes are currently available to Enterprise and Universal Licenses.

Comments ( 0 )

set_subgrid()

  • Parameter(s):
    • $obj_grid: phpGrid object as subgrid
    • $s_fkey: subgrid foreign key.
    • $m_fkey: Optional. Master foreign key. (ver 4.5+).
  • Description:
    • The method displays inline and read-only detail grid rather than in separate datagrid table. It is similar to set_masterdetail() except it can takes an additional parameter as the foreign key from master grid. When ignored, phpGrid assumes that m_fkey has the same value as the s_fkey.
  • Remark:
    • Edit is currently not supported in subgrid.
    • Nested subgrid is currently not supported,  Nested/drill-down subgrid is now supported (version 6+).
    • The order of the 2nd and 3rd parameter matters,
    • When the subgrid is the same table(self-reference, in database term) as the master grid, subgrid must use an different alias as table name in the select statement.
    • All the grid properties and method should be called BEFORE set_subgrid is called.
    • Do not include ‘WHERE’ in set_query_filter() when used on subgrid.
  • Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_edit("INLINE", "CRUD");

// 2nd grid as detail grid
$sdg = new C_DataGrid("SELECT * FROM orderdetails", array("orderLineNumber", "productCode"), "orderdetails");
$sdg->enable_edit("INLINE", "CRUD");

// 3rd grid as detail grid.
$sdg2 = new C_DataGrid("SELECT * FROM products", array("productCode"), "products");
$sdg2->enable_edit("INLINE", "CRUD");

// passing the detail grid object as the first parameter, then the foreign key name.
// set_subgrid should be the last method to call before display
$sdg->set_subgrid($sdg2, 'productCode');
$dg->set_subgrid($sdg, 'orderNumber');  

$dg->display();
Comments ( 0 )

set_sortname()

  • Parameter(s):
    • $col_name: Column name to sort
    • $sort_order: ’ASC’ or ‘DESC’. Default value is ‘ASC’  (version 4.4+)
  • Description:
    • Set column sort by and sort order when datagrid is initially loaded.
  • Example:
1
$dg -> set_sortname('productName', 'DESC');
Comments ( 0 )

set_scroll()

  • Parameter(s):
    • $scroll: Boolean value indicates wheter
    • $height: phpGrid height
  • Description:
    • Use vertical scroll to load data. The datagrid will not load everything at once and will hold all the items from the start through to the latest point ever visited. This prevents memory leaks from happening.
  • Remark:
    • Pagination is disabled when scroll is set to true.
    • The default height is 400 when scroll is true and should never be ’100%’.
  • Example:
1
$dg -> set_scroll(true);
Comments ( 0 )