demo Archives | phpGrid - PHP Datagrid

How to display data from other columns?

Sun, 21 Nov 2021 06:48:28 +0000

Sometimes, you want to have more control on how data should be displayed, such as showing full name instead of displaying first and last name in separate columns. Similar use cases includes manipulating data, modifying, or even adding additional information in a single column.

Custom formatter is your friend.

A Custom Formatter is a powerful and versatile function. You will fall in love with set_col_property() It not part of phpGrid, but a jqGrid Javascript function with the following parameters:

– cellvalue – the cell value to be formatted
– options
– rowObject – the data of the current row in an array with column name indexed

So a custom formatter function can be declared like so:

1
2
3

function nameFormatter(cellvalue, options, rowObject){
return rowObject["firstName"] + ' ' + rowObject["lastName"];
}

then use it with phpGrid set_col_property() function:

1	$dg -> set_col_property('firstName', array('formatter' => '###nameFormatter###')); // must have ###

The example demonstrates concatenating first and last name as full name displayed inside firstName column .

Full example code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// 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 firstName, lastName, age FROM table1", "id", "table1");
$dg->set_col_property('firstName', array('formatter'=>'###nameFormatter###')); // must have ###
$dg->display();
?>

The post How to display data from other columns? appeared first on phpGrid - PHP Datagrid.

JSON Data Source

Sun, 15 Aug 2021 01:54:10 +0000

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

Load Stored Procedure with OUT parameter

Tip: sp() function is your friend

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

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

$db = new C_DataBase(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME, PHPGRID_DB_TYPE,PHPGRID_DB_CHARSET);

$results = $db->sp('CALL GetOrderCountByStatus(?, @total)', 'Shipped');
$data = $db->db_query("SELECT @total");

// create PHP array from results returned by stored proc
$data1 = [];
$count = 0;
while($row = $db->fetch_array_assoc($data)) {
$data_row = array();
for($i = 0; $i < $db->num_fields($data); $i++) {
$col_name = $db->field_name($data, $i);
$data1[$count][$col_name] = $row[$col_name];
}
$count++;
}

$dg = new C_DataGrid($data1, "id", "data1");
$dg -> display();

Create Array from Database Table

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

Save local array back to database

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 JSON Data Source appeared first on phpGrid - PHP Datagrid.

Data Source: Stored Procedure with IN Paramters

Sun, 15 Aug 2021 01:44:55 +0000

* This feature is only available with a commercial license.

phpGrid now supports stored procedure (version 7.5+). It works with stored proc without parameters; and stored proc with IN parameter(s). Stored proc with OUT parameter currently is not directly supported, but possible by using local array as a workaround.

It’s easy to use. Pass the stored proc name as 1st parameter into the phpGrid constructor. And parameter values as the 2nd (must be the same order defined in stored proc). You only need those two parameters.

Stored proc with no parameter

1
2
3

// stored proc with 2 in parameters
$dg = new C_DataGrid("CALL GetTotalAssets()");
$dg -> display();

Stored proc with 2 IN parameters

1 2	$dg = new C_DataGrid("CALL GetOfficeByCountryState(?, ?)", ['USA', 'CA']); $dg -> display();

Yeap, it’s that easy. No muss, no fuss.

Keep in mind to always include namespace and conf.php on TOP of the script.

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

The post Data Source: Stored Procedure with IN Paramters appeared first on phpGrid - PHP Datagrid.

CELL edit with Add, Delete and Export buttons

Tue, 21 May 2019 22:55:07 +0000

phpGrid has a less known edit feature called “CELL”. It’s similar to INLINE edit, but with only a single editable cell when selected. This is not a fully-fledged feature from jqGrid itself, especially it missed add and delete out of the box. However, we have hacked (but works) Javascript to included the add and delete functions through custom javascript.

Pay close attention to the “url” property. Be sure it points to a valid edit.php address.

1
2
3
4
5
6
7
8

// 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");
$dg->enable_edit('CELL');
$dg -> display();

ADD & DELETE

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

$(function() {

$('#addNew').on('click', function(){
$("#orders").jqGrid('editGridRow', "new", {url:'/phpGrid/edit.php?dt=json&gn=orders&oper=add'} );
});

$('#delRow').on('click', function(){
var selRowId = $("#orders").jqGrid('getGridParam', 'selrow');
$.ajax({
url: '/phpGrid/edit.php?dt=json&gn=orders',
data: {'id': selRowId, 'oper': 'del'},
type: 'POST',
dataType: 'JSON'
});
$('#orders').trigger( 'reloadGrid' );
});
})
<button id="addNew">Add Newbutton>
<button id="delRow">Delete Selectedbutton>

See Live Example!

EXPORT (new)

To add Export buttons to the bottom toolbar, insert the following BEFORE $dg->$display(); Be sure to replace /phpGrid/ with the actual phpGrid absolute file path on your web server

1
2
3
4
5
6
7
8
9
10
11
12

$exportDropdown =<<< EXPORTDROPDOWN
$('#orders_pager1_left').append (`

Export:

`);
EXPORTDROPDOWN;
$dg->before_script_end = $exportDropdown;

The post CELL edit with Add, Delete and Export buttons appeared first on phpGrid - PHP Datagrid.

Bootstrap Grid Layout

Tue, 02 Apr 2019 22:09:23 +0000

The datagrid can also be displayed and positioned easily in a Bootstrap Grid System layout, uses a series of containers, rows, and columns to the layout that is fully responsive.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<div class="container">
<div class="row">
<div class="col-md-4">.col-md-4div>
<div class="col-md-4">.col-md-4

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

$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg -> set_theme('bootstrap');
$dg -> display();
?>
div>
<div class="col-md-4">.col-md-4div>
div>
div>

See Live Example!

The post Bootstrap Grid Layout appeared first on phpGrid - PHP Datagrid.

Change Master Detail Grids Layout Using CSS

Sun, 24 Feb 2019 21:55:50 +0000

The default master detail grid has the top down layout. The master, the parent datagrid is always on top of the child grid. This is the default layout. Each datagrid is enclosed by a div with a unique ID.

In the example below, the master grid would have the ID gbox_orders, and the 1st detail grid would have the ID gbox_orders_d1 (Replace “orders” here with your own master table name) Once you have those IDs, one can use CSS to set layout easily. Read more about CSS layout on w3school.

The below CSS set the parent grid float on the left while the child grid is fixed at the right bottom corner.

1
2
3
4
5
6
7
8
9
10
11
12

/*; master grid id format: #gbox_<MASTER TABLE NAME> */
#gbox_orders{
float:left;
margin:30px 10px;
}
/* detail grid id format: #gbox_<MASTER TABLE NAME>_d<index> */
#gbox_orders_d1{
margin:10px;
position: fixed;
bottom: 0;
right: 0;
}

Live Demo!

The post Change Master Detail Grids Layout Using CSS appeared first on phpGrid - PHP Datagrid.

Simple Shopping Cart Using the Action Column

Wed, 16 Jan 2019 00:11:48 +0000

In the action column, it is possible to add additional links or buttons beside the CRUD buttons with phpGrid custom JSON property “cust_prop_jsonstr” For example, you can use “cust_prop_jsonstr to insert “add to cart” button via the property called actionsNavOptions.

In the example below, the onClick event call back function simply returns an alert message with the rowid. That is only the tip of the iceberg. By using Ajax, the options.rowid could return the unique key of the row to a server-side script for much more complex workflow such as add to cart, add new users etc.

1
2
3

// 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 orders", "orderNumber", "orders");
$dg->cust_prop_jsonstr = 'actionsNavOptions: {
                addUsericon: "fa-user-plus",
                addUsertitle: "Add user",
                deleteUsericon: "fa-user-times",
                deleteUsertitle: "Delete user",
                addToCarticon: "fa-cart-plus",
                addToCarttitle: "Add item to the cart",
                custom: [
                    { action: "addUser", position: "first", onClick: function (options) { alert("Add user, rowid=" + options.rowid); } },
                    { action: "addToCart", position: "first", onClick: function (options) { alert("Add to Cart, rowid=" + options.rowid); } },
                    { action: "deleteUser", onClick: function (options) { alert("Delete user, rowid=" + options.rowid); } }
                ]
            },';
$dg->add_column("actions", array('name'=>'actions', 
	'sortable'=>false,
    'index'=>'actions',
    'width'=>'150',
    'formatter'=>'actions',
    'formatoptions'=>array('keys'=>true, 'editbutton'=>true, 'delbutton'=>true)),'Actions');
$dg->enable_edit();
$dg -> display();

Online Demo (Scroll all the way to the right to view the Action column)

The post Simple Shopping Cart Using the Action Column appeared first on phpGrid - PHP Datagrid.

Complete Date Format Demo (Updated)

Fri, 23 Nov 2018 15:57:41 +0000

The example demonstrates date column formatting using functions set_col_date, set_col_datetime, set_col_property(…formatter”=>”date”,…). If you have trouble with date format, most likely you will find answer here.

One can “fine tune” the jQuery datepicker properties like year range and min and max date. The jQuery UI Datepicker is a highly configurable plugin that one can customize the date format and language, restrict the selectable date ranges and add in buttons and other navigation options easily.

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

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

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

// Method 1: change date display and datepicker display (used for edit) to Spanish date
$dg -> set_col_date("orderDate", "Y-m-d", "m/d/Y", "m/d/yy");

// Method 2: change date display and datepicker display (used for edit) to Spanish date
$dg -> set_col_property("requiredDate",
array("formatter"=>"date",
"formatoptions"=>array("srcformat"=>"Y-m-d","newformat"=>"m/d/Y"),
"editoptions"=>array(
"dataInit"=>"function(el) {
$(el).datepicker({
changeMonth: true,
changeYear: true,
dateFormat: 'm/d/yy',
yearRange: '-50:+50', // set range of selectable years back and future 50 yrs
minDate: '+100d' // minimum selectable date is 100 days into future
})
}")));

// Method 3: Display using jQuery Datetimepicker extension replacing the built-in datepicker plus option time picker
$dg -> set_col_datetime("contractDateTime", "Y-m-d H:i", "m/d/Y H:i", "m/d/Y H:i");

// Display time only. No date. It cannot be edited, so we also made it hidden on the edit form.
$dg -> set_col_property("shippedDate",
array("formatter"=>"date",
"formatoptions"=>array("srcformat"=>"ISO8601Short","newformat"=>"g:i A"),
'editable'=>false,'hidedlg'=>true));

$dg->enable_edit();
$dg -> display();

See Live demo!

Note
Starting version 7.2.8, for MySQL, the pickers for the Date, Time and DateTime are automatically used respectively based on the datbase field type. This is for MySQL database only. Users no longer need to specify the date type with set_col_format.

Date Picker (MySQL Date)

Datetime Picker (MySQL Datetime)

Time Picker (MySQL Time)

See Live demo!

The post Complete Date Format Demo (Updated) appeared first on phpGrid - PHP Datagrid.

Timepicker Only Support

Tue, 24 Apr 2018 19:01:56 +0000

phpGrid now has standalone time-picker support using set_col_time() method. Only the table fields that have TIME type are applicable. For non-TIME types, it will display the regular datetime picker. For database has no TIME type support, you can use Sql Datetime type and choose to show time only

1
2
3
4
5

// Display TIME. Requires column with SQL Time data type.
// For database has no TIME type support, you can use Sql Datetime type and choose to show time only
$dg -> set_col_time('logTime')->set_col_property("logTime",
array("formatter"=>"date",
"formatoptions"=>array("srcformat"=>"h:i A","newformat"=>"h:i A")));

In live demo, edit the “logTime” field to activate the timepicker.

See Live demo!

The post Timepicker Only Support appeared first on phpGrid - PHP Datagrid.

Display Hyperlink From Another Field of The Same Row

Sat, 17 Feb 2018 09:56:49 +0000

This demo is based on Hyperlink to Call JavaScript Function. It triggers a JavaScript event when hyperlink is clicked.

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

In the following example, the gotoUrl() function displays text from one field (productName) and hyperlink from another (productUrl) in the same row.

Javascript:

1
2
3
4
5
6
7
8

gotoUrl = function (grid,param) {
var ar = param.split('=');
if (grid.length > 0 && ar.length === 2 && ar[0] === '?id') {
var rowid = ar[1];
var url = grid.getCell(rowid, 'productUrl');
window.location.href = url;
}
};

PHP:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// 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 products", "productCode", "products");
$dg -> set_col_title("productCode", "Product Code");
$dg -> set_col_title("productName", "Product Name");
$dg -> set_col_title("productLine", "Product Line");

// form hyperlink from another cell that contains URL in product code
$dg->set_col_format("productName", "showlink", array("baseLinkUrl"=>"javascript:", "target"=>"_new",
"showAction"=>"gotoUrl(jQuery('#products'),'",
"addParam"=>"');"));

$dg -> display();

See live demo!

The post Display Hyperlink From Another Field of The Same Row appeared first on phpGrid - PHP Datagrid.