Basic concepts

In this chapter you'll find base information concerning static loading data from:

• database table
• File System
• Excel file

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 2 ways to specify the desired data:

• by render_table() method when you work with one table
• by render_sql() method when you work with several tables (to implement complex queries)

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:

• database table name
• name of identity field (optional)
• list of fields which should be used as values of component item (cells of grid, item label in tree, text of option in combo)
• list of extra fields (know more about extra fields here)

If you want to render all fields from DB ( except of key field ), you can use 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:

1. Define sql for each operation manually
2. Use server-side events to define your own processing logic
3. Define different configs for select and update operations

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. In this case you should use the render_sql() method:

$grid->render_sql("SELECT * from tableA INNER JOIN tableB  ON tableA.id=tableB.id", "","name,price", "extra1, extra2");

Parameters:

• sql statement
• name of identity field (optional)
• list of fields which should be used as values of component item (cells for grid, item label for tree, text of option for combo)
• list of extra fields (know more about extra fields here)
• parent ID field name for hierarchical structures (required for tree and treegrid)

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.

The last parameter of render_sql and render_table methods allows 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 , both render_sql and render_table accept one more parameter - relation ID. For default treegrid hierarchy - it's name of 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");

Back to top

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:

• folder, for which data listing is required
• field's id. Leave it empty or use safe_name as ID of file
• list of fields, possible values are:
  • filename - name of file
  • full_filename - full path to file
  • size - size of file in bytes
  • name - name part of file name
  • extension - extension part of file name
  • date - timestamp of file
  • is_folder - file|folder flag

There are 3 ways to limit files in output:

1. by extension type
2. by regexp pattern
3. by meta-type

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:

• image - image files
• document - doc, xls, txt, rtf
• web - php, html, js, css
• audio - mp3, wav, ogg
• video - avi, mpg, mpeg, mp4
• only_dir - folders

$fileTypes = FileSystemTypes::getInstance();
$fileTypes->setType('web');

Back to top

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:

1. Download phpExcel.
   It isn't included in the connector's package. You can grab it from 2 sources:
   • http://support.dhtmlx.com/x-files/connector/phpExcel.zip
   • http://www.codeplex.com/PHPExcel.
2. Unzip library to the connector's folder.
3. Include related files:
   • 'lib/PHPExcel.php' (phpExcel package)
   • 'lib/PHPExcel/IOFactory.php' (phpExcel package)
   • 'db_excel.php' (standard connector's package)

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

• constructor GridConnector()
  • name of excel file, absolute path or path related to the php file
  • database type

• render_table() method
  • first parameter can be
    • range of cells in the spreadsheet, for example A18:F83
      

      $grid->render_table("A18:F83", "id", "A,B,C,D,E,F"); 

    • number of the top row, from which output need to be started, for example A4
      

      $grid->render_table("A1", "id", "A,B,C,D,E,F"); 

    • '*', which means include all not empty rows
      

      $grid->render_table("*", "id", "A,B,C,D,E,F"); 

  • name of identity field. You can use 'id' value for auto id generation
  • list of fields which will be used as columns in the grid

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

Back to top