Data Source Archives | 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.

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!