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

• database table
• File System
• PHP array
• 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 3 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
• by render_complex_sql() method when you use to call stored procedures in the database + 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 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:

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

• 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.

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

Back to top

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:

• name of PHP array or a PHP array itself
• 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)

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

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