5.5 Archives | phpGrid - PHP Datagrid

Local Array Data Source *

Wed, 01 Apr 2015 14:59:00 +0000

* This feature is available with a commercial license.

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, which 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 grids*.

For local array, it’s highly recommended to use all lower case for array key (e.g., bar1, bar2…) and use “id” as the primary key.

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. Use “id” as the primary key name.)

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

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$dg = new C_DataGrid($data1, "id", "data1");
$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 -> enable_search(true);
$dg -> enable_edit('FORM', 'CRUD');
$dg -> enable_export('EXCEL');
$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_conditional_format("cost","CELL",array("condition"=>"lt","value"=>"20.00","css"=> array("color"=>"black","background-color"=>"yellow")));

$dg -> set_theme($theme_name);

// set data sort type. With data array, we don't have data type information as we usually do with database fields.
$dg -> set_col_property("quantity", array("sorttype"=>"integer")); // display different time format
$dg -> set_col_property("cost", array("sorttype"=>"currency")); // display different time format

$dg -> set_databar('quantity', 'blue');

$dg -> display();

Screenshot

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

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

Create Array from Database Table

Learn how to create array from database from Complex Query tutorial.

Save local array back to datbase

It’s possible to save local array data back into a relational DB. The technique uses a local array as indirect mean to edit complex database query with JOINS, UNION etc. See KB: Save local array back to database via Ajax

Demo

The post Local Array Data Source * appeared first on phpGrid - PHP Datagrid.

Custom Data Validation

Tue, 08 Jan 2013 23:40:33 +0000

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.

Client Side Validation

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.

Server Side Validation

For server side data validation, in your javascript function, use jQuery.ajax to call your server side validation routine. An example of Ajax call can be found on Hyperlink to Call JavaScript Function example

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

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

// 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
6
7
8
9

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/path/to/conf.php");

$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.)

The post Custom Data Validation appeared first on phpGrid - PHP Datagrid.

set_col_customrule()

Tue, 08 Jan 2013 23:33:06 +0000

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

The post set_col_customrule() appeared first on phpGrid - PHP Datagrid.

Virtual/Calculated Column

Tue, 08 Jan 2013 23:18:48 +0000

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 virtual column name is NOT an existing database column name used by 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
24
25
26

use phpCtrl\C_DataGrid;
require_once("/path/to/conf.php");

$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 = <<
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 two columns “Total” and “Foo” are virtual.)

The post Virtual/Calculated Column appeared first on phpGrid - PHP Datagrid.

add_column()

Tue, 08 Jan 2013 23:02:56 +0000

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 = << 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)');

The post add_column() appeared first on phpGrid - PHP Datagrid.

display_script_includeonce()

Tue, 08 Jan 2013 20:21:53 +0000

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.

The post display_script_includeonce() appeared first on phpGrid - PHP Datagrid.

Integrated Search

Thu, 16 Sep 2010 00:20:38 +0000

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.

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

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$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!

It is possible to externalize search command using Javascript. Check out Externalize Search Example.

The post Integrated Search appeared first on phpGrid - PHP Datagrid.

Export Data to Excel, PDF, CSV, and HTML

Thu, 16 Sep 2010 00:18:41 +0000

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. Users can export data to Excel in php or any one of the supported file formats Also see enable_export().

If you want to enable users to export data from the PHP grids, you should enable this feature. By default, users can export to Microsoft Office Excel, PDF, HTML, or CSV any list of data that appears in a grid. Export to Excel produces native Microsoft Excel .xls file format.

Native Excel .xls format is now supported in version 6.7. Previously Excel export is in OpenOffice XML .xml format.

Users can export data to Excel in php or any one of the supported file formats by clicking the Export button that appears in the footer. Users can only export data appears in the datagrid. All hidden columns made with set_col_hidden() are not included in the export. It is not recommended to enable export feature when the datagrid contains data such as social security numbers or passwords.

When users click Export button, the phpGrid will generate the export file by using data from the database table in Ajax. Data that does not appear on the screen is not exported. If search filter is used, only the filtered results are exported.

When the datagrid contains fields that reference to others database tables through lookups, the text will be used in the export rather than the id fields. Please check out the “select” control type in set_col_edittype function.

PDF and CSV formats are now supported (Available to Premium Edition) !

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$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 Demo!

The post Export Data to Excel, PDF, CSV, and HTML appeared first on phpGrid - PHP Datagrid.