In this chapter you'll find base information concerning static loading data from:
APPLICABLE TO: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch components
Loading characteristics are defined at stage of component configuration.
There are 3 ways to specify the desired data:
When all necessary data is placed in one database table you should use the render_table() method:
$grid->render_table("grid50","item_id","item_nm,item_cd", "extra1, extra2");
Parameters:
If you want to render all fields from DB ( except for the key field ), you can use a simplified command:
$grid->render_table("grid50");
That's enough to make connector implement select, insert, update and delete operations.
If your SQL statement contains more than one table, connector won't be able to generate insert/update/delete operations correctly and you will need to do one from the next:
The 3rd approach is shown in the code snippet below:
if ($grid->is_select_mode())//code for loading data $grid->render_sql("Select * from tableA, tableB where tableA.id=tableB.id", "a.id","name,price,other"); else //code for other operations - i.e. update/insert/delete $grid->render_table("tableA","id","name,price");
With such init code grid will be loaded with three columns of data from 2 tables, but during saving only data from the first table will be saved.
You are allowed to use any SQL statements to populate a dhtmlx component through dhtmlxConnector. For complex SQL queries we strongly encourage you to use the render_complex_sql() method:
$grid->render_complex_sql("SELECT name from tableA WHERE dept = (SELECT dept FROM tableB where name = 'John')", "contact_id","name,surname,age,address", "extra1, extra2");
Parameters:
In case your SQL query was against a single table, it is quite probable that insert/update/delete operations do not require any additional code. dhtmlxConnector will parse your SQL and generate insert/update/delete statements based on used table and fields names.
Starting from version 1.5, you can load data using in SQL statements stored procedures. The library provides a special method for this need - render_complex_sql.
$grid->render_complex_sql("exec usp_findusers 'John'", "id","name,surname,age,address,phone");
The last parameter of render_array, render_sql, render_complex_sql, render_table methods allows you to define a list of fields which will be extracted from database table but won't be sent to client-side.
These fields can be used as attributes or flags, mapped to different properties of records ( userdata, row styles, images, etc. ).
$grid->render_table("tableA","id","name,price","extra1,extra2"); // or $grid->render_sql("Select * from tableA, tableB where tableA.id=tableB.id", "table_a_id","name,price,other","extra1,extra2");
extra1 and extra2 fields will be available in all server-side events but won't be sent to client-side, and won't be included in update|insert operations.
In case of Tree and TreeGrid , render_array, render_sql, render_complex_sql and render_table accept one more parameter - the relation ID. For default treegrid hierarchy - it's the name of a field , which will be used to link parent and child records.
$treeGrid->render_table("tableA","id","name,price","","parent_id"); // or $treeGrid->render_sql("Select * from tableA, tableB where tableA.id=tableB.id", "a.id","name,price,other","","parent_id");
To make usage of extracted data handier you can use aliases for DB field names (makes sense only if you use server-side events):
$grid->render_table("tableA","id","name,price(product_price)"); // or $grid->render_sql("Select *,tableA.id as aid from tableA, tableB where tableA.id=tableB.id", "tableA.id(aid)","name,price(product_price),other");
Starting from version 1.5, instead of database you can load data from a PHP array which can be filled by any kind of external logic.
To load data from a PHP array, use method render_array. Beware, the method can't be used for storing data but you still can use event handlers, or custom model to intercept data saving command and process them in some custom way.
Parameters:
$data = array( array("id" => "1", "product" => "Phone AB12", price:"460"), array("id" => "2", "product" => "Tablet device", price:"830") ); $conn->render_array($data, "id", "product,price");
APPLICABLE TO: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form
Starting from version 1.0, dhtmlxConnector allows to use FileSystem as datasource (please note, to start use this functionality you should include db_filesystem.php file located in connector's package).
require("./codebase/connector/db_filesystem.php"); require("./codebase/connector/grid_connector.php"); $grid = new GridConnector("", "FileSystem"); $grid->render_table("../","safe_name","filename,full_filename,size,name,extention,date,is_folder");
In the code snippet above, grid is filled with info about files located in 'd:/www' folder
Parameters of 'render-table' method:
There are 3 ways to limit files in output:
by extension type:
$fileTypes = FileSystemTypes::getInstance(); $fileTypes->addExtention('png');
by regexp pattern:
$fileTypes = FileSystemTypes::getInstance(); $fileTypes->addPattern('/^.+\..*$/');
by meta-type:
The following meta-types can be used:
$fileTypes = FileSystemTypes::getInstance(); $fileTypes->setType('web');
APPLICABLE TO: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form
Starting from version 1.0, dhtmlxConnector allows to use Excel file as datasource.
To start use this functionlity you should:
//files from libExcel package require_once('lib/PHPExcel.php'); require_once('lib/PHPExcel/IOFactory.php'); //connectors require("../../codebase/db_excel.php"); require("../../codebase/grid_connector.php"); $grid = new GridConnector("../some.xls", "ExcelDBDataWrapper"); $grid->render_table("A18:F83", "id", "A,B,C,D,E,F");
Parameters:
$grid->render_table("A18:F83", "id", "A,B,C,D,E,F");
$grid->render_table("A1", "id", "A,B,C,D,E,F");
$grid->render_table("*", "id", "A,B,C,D,E,F");
Applicable to: Grid
When you need to load both data and header from excel file, you can make it through GridConfiguration:
$grid = new GridConnector($excel_file, "Excel"); $config=new GridConfiguration(); //array of cells, with labels for grid's header $config->setHeader($grid->sql->excel_data(array("A3","B3","F13"))); $grid->set_config($config); $grid->render_table("A18", "id", "A,B,F");