Intermediate Archives | phpGrid - PHP Datagrid

Show Remaining Characters in Textarea/Input

Tue, 25 Apr 2017 03:54:50 +0000

In a recent user support request, the user would like to see the remaining characters allowed for data input in a text input. This is not a phpGrid standard feature. How can it be achieved?

Custom event comes to rescue!

First of all, we must insert a new html node for our counter right after the text input. In our demo, we use the “status” field. The new counter will display the text of remaining allowed characters for data entry.

1	<span id='counter'></span>

We bind a new “onkeyup” JavaScript event to the “status” text input. The phpGrid event you should use is “jqGridAddEditAfterShowForm” so that the counter is only displayed AFTER the form is shown.

1
2
3
4
5
6
7
8

$afterShowForm = << function ()
{
$("#tr_status > td.CaptionTD + td.DataTD").append("");
$("#status").attr("onkeyup","updateCounter()");
}
AFTERSHOWFORM;
$dg->add_event('jqGridAddEditAfterShowForm', $afterShowForm);

We use the CSS selector to style our counter. Most importantly, we make sure it is displayed in the right place (at the end of the text input box).

1
2
3
4
5
6
7
8
9

/* move the counter atop */
#counter{
font-size: 8px;
position: relative;
top: -15px;
float: right;
padding-right: 25px;
display:inline-block;
}

Lastly, insert the javascript function below that updates our counter during each key press.

1
2
3
4
5
6
7
8

// show how much characters in "status" field input. Max is 10
function updateCounter() {
var maxChar = 10;
var pCount = 0;
var pVal = $("#tr_status > td.CaptionTD + td.DataTD input").val();
pCount = pVal.length;
$("#counter").text(pCount + '/' + maxChar);
}

Run Live Demo! (Click “Status” input box, and start typing)

The post Show Remaining Characters in Textarea/Input appeared first on phpGrid - PHP Datagrid.

Bulk Edit, Select Multiple Rows

Fri, 17 Feb 2017 01:07:22 +0000

You can select multiple records with set_multiselect() method. When multiselect is enabled, a checkbox is shown to the left of each row. Use multiselect feature to for bulk edit such as delete multiple records and save multiple selected rows.

Version 6.7.10 Update

Set the 2nd parameter in set_multiselect() to true to persist row selection following pagination. Before the selection is lost after going to another page.

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

// 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.");

// 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 selection. The 2nd true signals grid not lose selections following pagination
$dg -> set_multiselect(true, true);

$dg -> display();

Bonus: send selected rows back to server

We can also easily obtain the selected row information and send captured rows back to server side for additional processing via AJAX. Below we got some working code snippets so you can get jump started. Check out the live example to see them in action!

Retrieve selected rows (Javascript)

1
2
3
4
5

function ShowSelectedRows(){
var rows = getSelRows();
// replace your own javascript here
alert(rows);
}

Retrieve selected row ids and posted to a remote URL via $.ajax in JSON format (Javascript)

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

Retrieve selected rows objects and posted to a remote URL via $.ajax in JSON format (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

Bonus 2: Code Snippets

To obtain row id of a SINGLE selected row

1	var row_id = $('#TABLE_NAME').jqGrid ('getGridParam', 'selrow')

Get ALL selected rows in an array (with multiple select enabled)

1	var rows = jQuery("#TABLE_NAME").jqGrid("getGridParam","selarrrow");

To get value from a cell of the currently selected row:

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

See Live Example!

The post Bulk Edit, Select Multiple Rows appeared first on 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.

Column Freeze

Sat, 20 Apr 2013 22:20:08 +0000

You can now use the set_col_frozen() method to set the 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
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->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!

The post Column Freeze appeared first on phpGrid - PHP Datagrid.

Customize Edit Form Layout

Thu, 09 Aug 2012 19:21:43 +0000

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.

IMPORTANT

The column order on the edit form, from top to bottom, and left to right, must match EXACTLY as the order in your Sql Select statement.
set_col_required() function should always go BEFORE set_col_property(). This is because an existing bug in jqGrid that adds extra TD tags after the formoptions has been already created that will push everything else to the next line.

1
2
3

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

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
27
28
29
30
31

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

// Required should be called BEFORE set_col_property
$dg -> set_col_required("orderDate, shippeDate, customerNumber");

// 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_col_property("comments", array("editoptions"=>array("style"=>"width:95%;")));

$dg->set_form_dimension(700, 400);

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

Bonus! Set comments field to always have the full width of the form. Please change text “orders” to table name used for your grid.

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

Form Layout Screenshot

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

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

A sample form layout (from a actual customer):

The post Customize Edit Form Layout appeared first on phpGrid - PHP Datagrid.

Conditional Format *

Sat, 14 Jan 2012 20:51:16 +0000

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

Conditional formatting example using set_conditional_format() method.

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

// 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");

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

Note:
For even more complex conditions, please refer to row level permission example by using set_grid_property() and add_event() method.

The post Conditional Format * appeared first on phpGrid - PHP Datagrid.

Create Excel-Like, Responsive Grid

Sat, 14 Jan 2012 20:10:24 +0000

Expand to Current Window Width

Use enable_autowidth() and enable_autoheight() to set datagrid responsive with the page dimension to fill the entire screen, similar to Excel spreadsheet.

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('INLINE', 'CRUD');
$dg->enable_autowidth(true)->enable_autoheight(true);
$dg->display();

Expand to Outer Container Width

By default, auto width function expands datagrid to fit the current window width. When the grid is insider another container, you need to add call setGridWidth() to adjust the width after the grid has been loaded.

1
2
3

$dg->before_script_end .= 'setTimeout(function(){$(window).bind("resize", function() {
phpGrid_orders.setGridWidth($("#mydiv").width());
}).trigger("resize");}, 0)';

Completely Hide the Horizontal Scrollbar

Add the following CSS to completely hide the horizontal scrollbar of the parent container. In this case, its id is “mydiv”.

1
2
3
4
5

>
#mydiv {
overflow-x: hidden;
}
>

Important:

“mydiv” is the id of parent DIV in which contains your grid.
“phpGrid_orders” is “phpGrid_” + your datagrid table name.

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

See Live Example! (Try to resize the window)

The post Create Excel-Like, Responsive Grid appeared first on phpGrid - PHP Datagrid.

Conditional Value *

Sat, 14 Jan 2012 20:08:01 +0000

* 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 simpler 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
21
22
23
24

// 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 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"=>"",
"FCellValue"=>""));

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

Note:
For even more complex conditions, please refer to row level permission example by using set_grid_property() and add_event() method.

The post Conditional Value * appeared first on phpGrid - PHP Datagrid.

In-cell Data Bar *

Sat, 14 Jan 2012 20:04:14 +0000

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!

The post In-cell Data Bar * appeared first on phpGrid - PHP Datagrid.

Display Non-English Characters

Wed, 30 Mar 2011 01:30:59 +0000

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" />

Dealing with Special Characters in SQL Server with Non-English Collation

It is slightly more complicated when using SQL Server collation other than the default English-language (US) “SQL_Latin1_General“, you may have trouble display those non-English characters such as “Löi, cé”.

The methods below is also available on KB. It demonstrate how to display Chinese (BIG5) characters from SQL Server.

In data.php, near line 213 change the following from

1	$data[] = $row[$col_name];

1	$data[] = iconv("BIG5", "UTF-8", $row[$col_name]);

– OR –

1	$data[] = mb_convert_encoding($row[$col_name], "UTF-8", "BIG5");

Also in the same file, below comment
“// ******************* execute query finally *****************”

Add the following line

1	$SQL = iconv("UTF-8","BIG5",$SQL);

Foreign Characters in PDF export

To export Chinese or other foreign characters, see KB: PDF can not display Chinese character

See Live Example!

The post Display Non-English Characters appeared first on phpGrid - PHP Datagrid.