PHP DataGrid - Getting Started (for version 4.0.0 or above).Step 1.
+---------------------------------------------------------------------------+
| Creating & Calling:
+---------------------------------------------------------------------------+
Be sure you write here a right path to the datagrid.class.php file, relatively to code_template.php, PEAR.php and DB.php files. Relative path is recommended,
but you can use a physical path too. Example:
define ("PEAR_DIR", "C:\\Apache2\\htdocs\\dgproject\\datagrid\\pear\\");
define ("DATAGRID_DIR", ""); /* Ex.: "datagrid/" */
define ("PEAR_DIR", "pear/"); /* Ex.: "datagrid/pear/" */
require_once(DATAGRID_DIR.'datagrid.class.php');
require_once(PEAR_DIR.'PEAR.php');
require_once(PEAR_DIR.'DB.php');
Put right values to these variables. Be sure, you use a prefix if you need it.
## *** creating variables that we need for database connection
$DB_USER='name'; /* usually like this: prefix_name */
$DB_PASS=''; /* must be already enscrypted (recommended) */
$DB_HOST='localhost'; /* usually localhost */
$DB_NAME='dbName'; /* usually like this: prefix_dbName */
First of all, we need to be connected to our database.
ob_start();
## *** (example of ODBC connection string)
## *** $result_conn = $db_conn -> connect(DB::parseDSN('odbc://root:12345@test_db'));
## *** (example of Oracle connection string)
## *** $result_conn = $db_conn -> connect(DB::parseDSN('oci8://root:12345@localhost:1521/mydatabase));
## *** (example of PostgreSQL connection string)
## *** $result_conn = $db_conn -> connect(DB::parseDSN('pgsql://root:12345@localhost/mydatabase));
## === (Examples of connections to other db types see in "docs/pear/" folder)
$db_conn =& DB::factory('mysql');
$result_conn = $db_conn -> connect(DB::parseDSN('mysql://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
if(DB::isError($result_conn)){
die($result_conn->getMessage());
}
Now you have to prepare the SELECT SQL statement. It can be any type of SELECT statement your database supports (with JOIN, UNION etc.), but you must put the primary key on the first place. Also be careful to write all fileds you need them to be shown, because the DataGrid class works with only on fields that you placed in SELECT statement.
Don't add here ORDER BY, LIMIT words or ; at the end of the statement.
## *** put a primary key on the first place
$sql = "SELECT primary_key, filed_1, filed_2 ... FROM tableName ";
Creating the new class instance and linking the DataGrid class to our database.
## *** set needed options and create new class instance // display SQL statements while processing $debug_mode = false;// display system messages on a screen $messaging = true;// prevent overlays $unique_prefix = "_abc";
$dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);
Now we need to set data source for the Grid
## *** set data source with needed optionsMake default(first) ordering by thid field$default_order_field = "field_name";Default field order type$default_order_type = "ASC|DESC";
$dgrid->dataSource($db_conn, $sql, $default_order_field, $default_order_type);
Step 2.
+---------------------------------------------------------------------------+
| General Settings:
+---------------------------------------------------------------------------+
We can add an unique prefix (optional) to our datagrid if we want to prevent
using of double names on this page (in case, when you use some datagrids or
forms on one page)
## *** set unique prefix
$u_prefix = "_abc";
$dgrid->setUniqueNames($u_prefix);
If you want to use a local language (not English) - you have to set the right
encoding.
The properly fields in your database must be created with the same CHARACTER SET too. Also you need to define in your file:
header('Content-type: text/html; charset=XXXX'); where xxxx ISO-8859-1 or UTF-8 or whatever you have.
## *** set encoding (default - utf8)
$dg_encoding = "utf8";
$dgrid->setEncoding($dg_encoding);
Option for some right-to-left languages (Hebrew, Arabic etc.)
## *** set direction: "ltr" or "rtr" (default - ltr)
$direction = "ltr";
$dgrid->setDirection($direction);
Set layouts for datagrid in view or edit mode and for the filtering block
## *** set layouts: 0 - tabular(horizontal) - default, 1 - columnar(vertical)
$layouts = array("view"=>0, "edit"=>1, "filter"=>1);
$dgrid->setLayouts($layouts);
Set various modes for datagrid.
True - allow the operation in this mode("view" or "edit"), false - don't allow.
Type - a type of command button (link, html button or image).
byFieldValue - if you want to make this field to be a link to edit mode page
(instead of edit button), write here a name of the field. If you want to use a standart edit command button leave it empty: "byFieldValue"=>""
## *** set other modes ("type" = "link|button|image"),
## *** ("byFieldValue" - make the field as a link to edit mode page)
$modes = array(
"add"=>array("view"=>true, "edit"=>false, "type"=>"link"),
"edit"=>array("view"=>true, "edit"=>true, "type"=>"link", "byFieldValue"=>"FieldName"),
"cancel"=>array("view"=>true, "edit"=>true, "type"=>"link"),
"details"=>array("view"=>true, "edit"=>false, "type"=>"link"),
"delete"=>array("view"=>true, "edit"=>true, "type"=>"image")
);
$dgrid->setModes($modes);
Set scrolling settings and parameters for DataGrid. If you want the DataGrid will
be displayed with scrolling, allow this option by the next commands.
## *** allow scrolling on datagrid
$scrolling_option = false;
$dgrid->allowScrollingSettings($scrolling_option);
## *** set scrolling settings (optional)
$scrolling_width = "90%";
$scrolling_height = "100%";
$dgrid->setScrollingSettings($scrolling_width, $scrolling_height);
If you want to allow multirow operations for DataGrid, set
$multirow_option = true;## *** allow mulirow operations
$multirow_option = false;
$dgrid->allowMultirowOpeartions($multirow_option);
Set CSS parameters for the datagrid. If you want to use embedded css class - define
$css_type as "embedded" and $css_class as you wish. If you use an external file of
CSS styles - you define $css_type as "file" and $css_class as full path to your
file.
For example: $css_class = "css/style.css".
Embedded CSS styles: "default", "blue", "gray" and "green".
## *** set CSS class for datagrid:
// "default" or "blue" or "gray" or "green" or your css file relative path with name
$css_class = "default";// "embedded" - use embedded classes, "file" - link external css file$css_type = "embedded";
$dgrid->setCssClass($css_class, $css_type);
Set variables that you use to get acces to your page
## *** set variables that used to get access to the page
## *** (like: my_page.php?act=34&id=56 etc.)
$http_get_vars = array("act", "id");
$dgrid->setHttpGetVars($http_get_vars);
If you want to use some PHP DataGrid on your page, you need to define properties for another datagrid/s
## *** set another datagrid/s unique prefixes (if you use few datagrids on one page)
## *** format (in wich mode to allow processing of another datagrids)
## *** array("unique_prefix"=>array("view"=>true|false, "edit"=>true|false, "details"=>true|false), [,...]);
$anotherDatagrids = array("abcd_"=>array("view"=>true, "edit"=>true, "details"=>true));
$dgrid->setAnotherDatagrids($anotherDatagrids);
Set datagrid title (caption)
## *** set DataGrid caption
$dg_caption = "My Favorite Lovely PHP DataGrid";
$dgrid->setCaption($dg_caption);
Step 3.
+---------------------------------------------------------------------------+
| Printing & Exporting Settings:
+---------------------------------------------------------------------------+
Set printing as true, if you want to allow this option
## *** set printing option: true(default) or false
$printing_option = true;
$dgrid->allowPrinting($printing_option); Set exporting as true, if you want to allow this option
## *** set exporting option: true(default) or false
$exporting_option = true;
$dgrid->allowExporting($exporting_option);
Step 4.
+---------------------------------------------------------------------------+
| Sorting & Paging Settings:
+---------------------------------------------------------------------------+
Set sorting option as true, if you want to allow sorting on columns
## *** set sorting option: true(default) or false
$sorting_option = true;
$dgrid->allowSorting($sorting_option);
Set paging option as true, if you want to allow paging on datagrid
## *** set paging option: true(default) or false
$paging_option = true;
$rows_numeration = false;
$numeration_sign = "N #";
$dgrid->allowPaging($paging_option, $rows_numeration, $numeration_sign);
Set aditional paging settings.
$top_paging or
$bottom_paging both defines paging
(top and bottom) behaviour. We have three parts of the paging line:
results,
pages and
page size dropdownbox. You need to set parameters for each of them. If you don't want to show any of them or all of them - leave it empty (Ex.:
$bottom_paging = array() or
$bottom_paging = array("pages"=>true, "pages_align"=>"left");).If you want to define your own dropdown box with page sizes - you can make it in
$pages_array array- see example below. Also you need to define default page size in
$default_page_size variable.
## *** set paging settings
$bottom_paging = array("results"=>true, "results_align"=>"left", "pages"=>true, "pages_align"=>"center", "page_size"=>true, "page_size_align"=>"right");
$top_paging = array("results"=>true, "results_align"=>"left", "pages"=>true, "pages_align"=>"center", "page_size"=>true, "page_size_align"=>"right");
$pages_array = array(10, 25, 50, 100, 250, 500, 1000);
$default_page_size = 10;
$dgrid->setPagingSettings($bottom_paging, $top_paging, $pages_array, $default_page_size);
Step 5.
+---------------------------------------------------------------------------+
| 5. Filter Settings:
+---------------------------------------------------------------------------+
If you want to allow a filtering mode, set $filtering_option as true.
## *** set filtering option: true or false(default)
$filtering_option = true;
$dgrid->allowFiltering($filtering_option);
Set aditional filtering settings.
"FieldName_1/2/3/.../n"=> - field on which will be made the filtering
"table"=>"tableName_1/2/3/.../n""field"=>"fieldName_1/2/3/.../n" - table and field with the filtered field is linked
"source"=>"self"|$fill_from_array - take a values from specific array or not
"operator"=>false|true - draw comparison operators dropdown list or not
"order"=>"ASC|DESC" - dropdown list values order (optional)
"type"=>"textbox|dropdownlist" - view type of filtering filed (textbox - default)
"case_sensitive"=>false|true - whether filtering is case sensitive
"comparison_type"=>"string|numeric|binary" - filtering comparison type
## *** set aditional filtering settings
$fill_from_array = array("1", "2", "3", "4", "5");
$filtering_fields = array(
"FieldName_1"=>array("table"=>"tableName_1", "field"=>"fieldName_1", "source"=>"self"|$fill_from_array, "operator"=>false|true, "order"=>"ASC|DESC", "type"=>"textbox|dropdownlist", "case_sensitive"=>false|true, "comparison_type"=>"string|numeric|binary"),
"FieldName_2"=>array("table"=>"tableName_2", "field"=>"fieldName_2", "source"=>"self"|$fill_from_array, "operator"=>false|true, "order"=>"ASC|DESC", "type"=>"textbox|dropdownlist", "case_sensitive"=>false|true, "comparison_type"=>"string|numeric|binary"),
"FieldName_3"=>array("table"=>"tableName_3", "field"=>"fieldName_3", "source"=>"self"|$fill_from_array, "operator"=>false|true, "order"=>"ASC|DESC", "type"=>"textbox|dropdownlist", "case_sensitive"=>false|true, "comparison_type"=>"string|numeric|binary")
);
$dgrid->setFieldsFiltering($filtering_fields);
Step 6.
+---------------------------------------------------------------------------+
| 6. View Mode Settings:
+---------------------------------------------------------------------------+
## *** set table properties
$vm_table_properties = array("width"=>"90%");
$dgrid->setViewModeTableProperties($vm_table_properties);
This method sets up columns, that will be viewable.
For all types:
"header"=>"..." - name of the column header
"type"=>"..." - type of column: label, image, linktoview, link (http://, mailto:) or password
"align"=>"..." - alignment of the column (left or right)
"width"=>"..." - width of column in pixels or in percents
"wrap"=>"..." - wraping of the column data (wrap or nowrap)
"text_length"=>"..." - viewable length of text in characters (any integer number - truncate after this number of characters, "-1" - don't truncate )
"case"=>"..." - text case (normal, upper or lower)
"summarize"=>... - summarize values in this column (true or false)
For link type:
"field_key"=>"..." - field for href parameter in <a href="field_key">
"field_data"=>"..." - field for <a href="">field_data</a>
"href"=>"..." - href parameter, {0} will be chaanged on "field_key" value
"target"=>"..." - target parameter
## *** set columns in view mode
$vm_colimns = array(
"FieldName_1"=>array("header"=>"Name_A", "type"=>"label", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>"-1", "case"=>"normal|upper|lower", "summarize"=>true|false),
"FieldName_2"=>array("header"=>"Name_B", "type"=>"image", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>'-1', "case"=>"normal|upper|lower", "summarize"=>true|false),
"FieldName_3"=>array("header"=>"Name_C", "type"=>"linktoview", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>'-1', "case"=>"normal|upper|lower", "summarize"=>true|false),
"FieldName_4"=>array("header"=>"Name_D", "type"=>"link", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>"-1", "case"=>"normal|upper|lower", "summarize"=>true|false, "field_key"=>"field_name_1", "field_data"=>"field_name_2", "href"=>"{0}", "target"=>"_new"),
"FieldName_5"=>array("header"=>"Name_E", "type"=>"link", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>"-1", "case"=>"normal|upper|lower", "summarize"=>true|false, "field_key"=>"field_name_1", "field_data"=>"field_name_2", "href"=>"mailto:{0}", "target"=>"_new"),
"FieldName_6"=>array("header"=>"Name_F", "type"=>"link", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>"-1", "case"=>"normal|upper|lower", "summarize"=>true|false, "field_key"=>"field_name_1", "field_data"=>"field_name_2", "href"=>"http://mydomain.com?act={0}&code=ABC", "target"=>"_new"),
"FieldName_7"=>array("header"=>"Name_G", "type"=>"password", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>"-1", "case"=>"normal|upper|lower", "summarize"=>true|false),
"FieldName_8"=>array("header"=>"Name_H", "type"=>"barchart", "align"=>"left", "width"=>"X%|Xpx", "wrap"=>"wrap|nowrap", "text_length"=>"-1", "case"=>"normal|upper|lower", "summarize"=>true|false, "field"=>"field_name", "maximum_value"=>"value")
);
$dgrid->setColumnsInViewMode($vm_colimns);
To be continued...
