Docs Archives | phpGrid - PHP Datagrid

System Requirements

Sat, 03 Jul 2010 03:46:09 +0000

phpGrid is an operating system independent PHP library. It runs on Windows, Linux, and even IBM i as long as the following criteria are met:

PHP 7 and higher
Apache, Tomcat, Ngnix or Microsoft IIS webserver
Major relational databases: MySQL/MariaDB, PostgreSQL, SQL Server, Oracle, DB2, SQLite, Access etc.

If you are new to Apache/PHP/MySQL, consider XAMPP or WAMP. Either installs Apache, PHP, and MySQL in one batch installation and requires almost no configuration for both Windows and Linux operating systems.

The post System Requirements appeared first on phpGrid - PHP Datagrid.

Technical Overview

Sat, 03 Jul 2010 03:52:27 +0000

System Environment: Web-based
Intended Audience: Non-Technical Users, Web Developers,
Language: Multilingual
Operating System: OS Independent
Programming Language: PHP, jQuery, jqGrid, XHTML, SQL
Database: All Modern Relational Database (thru ADOdb)

Since the major system architecture shift in phpGrid version 4, the new version abandons the old programming model which heavily relies on server side scripting, both in rendering and data operations. Much of the datagrid rendering now resides on the client side browser using jQuery and jqGrid. The server side is solely responsible for data CRUD operations. The new version is much more scalable than the previous generation, It also offers more features, such as subgrid and theme roller.

PHP web-based editable datagrid now can be achieved in two lines of code rather than five lines in the previous version. It also use Cascading Style Sheets, or CSS, extensively for display related tasks, a perfect balance between simplicity and features.

phpGrid supports all major databases and all major web browsers.

The post Technical Overview appeared first on phpGrid - PHP Datagrid.

Installation

Tue, 14 Sep 2010 07:37:57 +0000

Web-based install

This is the easiest method for installing phpGrid on your web server. It is the recommended for installing Lite version. Simply extract phpGrid zip file into the web folder, and run install.php from the browser. For example http://localhost/phpGrid_Lite/install.php.

Be sure conf.php is writable or installation will fail.

For demo, the only database type supported is MySql. Enter database hostname, username and password in the form, and choose wether to create a new database or not, then hit Install button.

Upon successful demo install, you will be redirect to the demo explorer where one can check out list of live examples and their source code.

Manual Install

If you choose to manual install, first and foremost, download the phpGrid and extract the zip file somewhere on your web server.

Install Sample Database

You can find the sample database script for our demo in examples/SampleDB folder.

Configuration

Before you begin coding using phpGrid, you must specify database information in conf.php. conf.php is our phpGrid configuration file in which we specify database connection parameters and path to the phpGrid.

Starting phpGrid version 6, The constant names prefixed with “PHPGRID_” to avoid potential naming collision. Please note that in PHP the method name and parameters are case sensitive.

conf.php for phpGrid version 6 and above (find phpGrid version #):

1
2
3
4
5
6

define('PHPGRID_DB_HOSTNAME','hostname'); // database host name or TNS name (Oracle only)
define('PHPGRID_DB_USERNAME', 'username'); // database user name
define('PHPGRID_DB_PASSWORD', 'password'); // database password
define('PHPGRID_DB_NAME', 'sampledb'); // database name
define('PHPGRID_DB_TYPE', 'mysql'); // database type
define('PHPGRID_DB_CHARSET','utf8mb4'); // OPTIONAL. Leave blank to use the default charset

conf.php for phpGrid version 4, 5, 5.x (find phpGrid version #):

1
2
3
4
5
6

define('DB_HOSTNAME','hostname'); // database host name or TNS name (Oracle only)
define('DB_USERNAME', 'username'); // database user name
define('DB_PASSWORD', 'password'); // database password
define('DB_NAME', 'sampledb'); // database name
define('DB_TYPE', 'mysql'); // database type
define('DB_CHARSET','utf8mb4'); // OPTIONAL. Leave blank to use the default charset

For PHPGRID_DB_CHARSET, before MySQL 5.5.3 or earlier, use utf8. Use utf8mb4 for later version. Leave it blank if you are not sure to use default character set.

Hint:

If you need Emoji, then you need ‘utf8mb4’ character set

If you use Apache alias directive or IIS virtual directory, you need to also set SERVER_ROOT value.

Once values in conf.php are set, simply reference phpGrid by adding the following at on top of your PHP code:

1	require_once("phpGrid/conf.php"); // relative path to conf.php without leading slash

or –

1	require_once(realpath($_SERVER["DOCUMENT_ROOT"]) ."/phpGrid/conf.php"); // absolute path to conf.php

Database Type

phpGrid supports wide range of database types. Simply define PHPGRID_DB_TYPE parameter value to your own database type. It can be one of the following strings. The default database type for phpGrid is “mysql”. PHPGRID_DB_TYPE string is case sensitive.

PHPGRID_DB_TYPE	Description
mysql	MySQL (default, using the new MySQLi driver)
mysql_dsn	MySQL (similar to above but using DSN)
odbc_mssql	SQL Server (*nix Only)
odbc_mssql_native / sqlsrv	SQL Server Windows native (Download PHP SQL Server Driver)
oci805	Oracle (Got TNS?)
sqlite	SQLite
postgres	PostGreSql
access	Microsoft Access
pdo_odbc_db2	DB2 (PDO)
db2	DB2 (Requires IBM_DB2 extension in IBM i environment)
db2-dsnless	DB2 DSN-less connection
informix	Informix
informix72	Alternative Informix Driver
ibase	Firebird/InterBase
odbc	Generic ODBC

A full list of supported databases and naming convention are available in ADOdb website. Not required but we encourage users to visit ADOdb website. It helps you understand how phpGrid utilizes it under the hood.
phpGrid now supports local array data source without using a database.

PHP Grid Oracle 9, 10g, 11g, 12c

When using PHP datagrid for Oracle database with TNS (Transparent Network Substrate), replace the database host name with the TNS Name in conf.php e.g.

1	define('PHPGRID_DB_HOSTNAME', 'YOUR ORACLE TNS NAME');

IBM i for DB2

DB2 requires ibm_db2 extension and one additional configuration setting – PHPGRID_DB_OPTIONS. Learn more about phpGrid DB2 support.

1	define('PHPGRID_DB_OPTIONS', serialize(array('i5_lib' => 'SAMPLEDB', 'i5_naming' => DB2_I5_NAMING_ON)));

Microsoft SQL Server

SQL Sever needs to use Mixed Authentication (SQL Server and Windows Authentication) mode to include SQL Server Authentication because the default authentication is Window authentication.

Below is a common error when SQL Server authentication is not enabled

1	Fatal error: Uncaught TypeError: sqlsrv_query(): Argument #1 ($conn) must be of resource, bool given

Following SQL Server official document to change SQL Server authentication mode: Change SQL server authentication mode. We recommend SMSS (SQL Server Management Studio) if you are more conformable with a graphic user interface.

Lastly be sure to restart SQL Server afterwards.

Firebird

Firebird is supported via “ibase” and “odbc”. Newer Firebird PHP driver is not yet supported due to ADOdb still has it under development. KB: Firebird Install & Setup on Windows, and Firebird Install & Setup on MacOS

SERVER_ROOT

In conf.php, SERVER_ROOT is the value tells your script where to find phpGrid library on web server. By default, it sets URL absolute directory path to phpGrid library automatically with the following script, so you don’t have to.

1	define('SERVER_ROOT', str_replace(str_replace('\\', '/', realpath($_SERVER['DOCUMENT_ROOT'])),'', str_replace('\\', '/',dirname(FILE))));

When to set SERVER_ROOT value manually

Whenever you use Apache alias directive or IIS virtual directory, you need to set SERVER_ROOT value manually.

SERVER_ROOT value is the URL to the phpGrid library folder subtracts the host name. For instance, if the URL to get to the phpGrid is http://www.yoursite.com/phpGrid, or http://localhost/phpGrid, the SERVER_ROOT should be “/phpGrid” – with leading slash.

1	define('SERVER_ROOT', '/phpGrid');

Another example, if the URL to phpGrid is http://www.yoursite.com/admin/phpGrid, or http://localhost/admin/phpGrid, the SERVER_ROOT should be “/admin/phpGrid” – with leading slash,

1	define('SERVER_ROOT', '/admin/phpGrid');

Composer Install

Starting version 7.5.3, phpGrid incorporates Composer as the package manager to manage popular dependencies such as ADOdb, PHPSQLParser, etc. The goals to gradually migrate to Composer for all dependencies. It ensures the dependencies can be updated by end users directly without going through phpGrid’s support, and also importantly without bloating the downloads.

Composer install is required for both web-based and manual install.

Simply run the following install command in phpGrid root folder

1	composer install

To get the latest versions of the dependencies, you should use the update command.

1	composer update

Addition Resources on Composer

Read our recent post PHP Composer to Autoload a Third Party Library to learn more about using Composer.
phpGrid Common composer install issues
Composer official website: https://getcomposer.org

The post Installation appeared first on phpGrid - PHP Datagrid.

phpGrid Constructor

Tue, 14 Sep 2010 07:47:05 +0000

After successful installation, call the constructor like the following:

1
2
3
4
5

// always add namespace and include conf.php on top of every script
use phpCtrl\C_DataGrid;
require_once('/path/to/conf.php');

$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

The details of phpGrid constructor are explained below:

__constructor($sql, $sql_key=’id’, $sql_table=”, $db_connection=”)

Parameter(s):
- $sql: SQL SELECT statement. This is the only SQL statement users’ needs to implement. The SELECT statement must include the primary key as one of the columns if not using the wildcard start (*) in SELECT statement.
  - This parameter can also be a local array (version 5.5+). Refer to phpGrid local array data source demo.
  - This parameter can also be the name of a stored procedure (version 7.5). In that case, the 2nd parameter is an array containing parameters value.
- $sql_key: The name of the database table primary key. The default is “id”.
  - Starting version 6, phpGrid supports composite primary key (Requires commercial licenses) by simply passing an array of string as $sql_key.
  - Note that composite PK is not supported as foreign key referential in master/detail and subgrid.
- $sql_table: Name of the database table used in the SQL statement. If not defined, the table name is automatically parsed from the SQL statement.
- $db_connection: Optional. Since version 4.3, the fourth parameter was introduced and used to overwrite connection parameters in conf.php. It allows datagrid to reference to a different data source on the fly. See Reference to Multiple Databases example for more information on this parameter. If you have only one database, you can safely ignore this parameter.
Description:
- This is our datagrid object constructor. Use this method to create the phpGrid object. Usually, this is the first line in your code.
Example:

Do NOT include WHERE clause in $sql. Instead use set_query_filter() method (version 4.2+) to set query filter. If not using the wildcard star(*), the SELECT statement MUST include the primary key as one of the columns.

Single primary key:

1	$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", 'Orders');

– OR –

1	$dg = new C_DataGrid("SELECT * FROM Orders", array("orderNumber"), 'Orders');

Composite primary key (commercial licenses only)

1	$dg = new C_DataGrid("SELECT * FROM orderdetails", array("productCode", "orderNumber"), "orderdetails");

Check out the Basic Datagrid example and composite primary key online example.

The post phpGrid Constructor appeared first on phpGrid - PHP Datagrid.

set_query_filter($WHERE)

Tue, 14 Sep 2010 07:48:22 +0000

Parameters:
- $WHERE: Sql WHERE statement to select data conditionally (DO NOT include WHERE keyword).
Description:
- The query filter is essentially the WHERE clause without the WHERE. The method adds WHERE condition an existing Sql SELECT statement. Use this method if you have WHERE clause in your SQL statement. You should use the same SQL syntax, such as single quotes around non-numeric values, and even SQL functions.
Remark:
- NEVER include keyword “WHERE” the filter. phpGrid automatically adds WHERE to the query.
- Use only SINGLE QUOTE around the filtered value.
- When using table alias, also include the new table alias
Example:

1	$dg -> set_query_filter("status='Shipped' AND YEAR(shippedDate) = 2003");

Using Table Alias

When using table alias, please also include the table alias as part of the filter. Otherwise, the full table name is automatically inserted as part of the query which would throw “Unknown column in ‘where clause” error.

Example

1	$dg -> set_query_filter("T.mycolumn = 'foo' ");

The post set_query_filter($WHERE) appeared first on phpGrid - PHP Datagrid.

enable_edit() *

Tue, 14 Sep 2010 07:50:23 +0000

* Please note this feature is only available in paid versions. CELL edit is only available in the commercial licenses

Parameters:
- $edit_mode: FORM, INLINE, or CELL. The default is FORM.
- $operations: specify allowed datagrid edit operations. By default, all edit operations are allowed.
  - C = Create
  - R = Review
  - U = Update
  - D = Delete
- $edit_file: Optional. Use a custom edit file other than the default “edit.php”. Do not set this parameter unless you ABSOLUTELY know what you are doing!
Description:
- When Form edit mode is used, additional icons appear in the data grid footer used for editing. When set to inline mode, the cells in the selected row become editable when the row is clicked. Only a single row can be edited for either mode.
Remark:
- Multiple records can be deleted when multiple select is enabled. See set_multiselect() for more information on this method.
- For inline edit, the user must press Enter key to save changes
- In FORM edit mode, double click triggers the popup edit window to display. In INLINE edit it is a single click to edit, similar to how Excel works. This is a design decision.
- When edit mode set to ‘CELL’, the datagrid will behave like an Excel spreadsheet. It is advisable to also enable keyboard navigation with enable_kb_nav().
Example:

1	$dg -> enable_edit('FORM', 'CRU'); // Everything is allowed but delete

It’s also possible to load a custom form in an iframe in a modal window with your own custom CRUD scripts.

Quick Tip: Keep the edit form open after submit

To keep form open after submit, add the following javascript to override ‘closeAfterEdit’ value before . Double click to edit a row, and form will remain open after edit.

Replace “orders” with your table name.

1
2
3
4
5
6
7
8
9
10
11
12

jQuery(document).ready(function($){
var grid = $('#orders')[0];
$(grid).jqGrid('setGridParam',{
ondblClickRow: function(rowid) {
$(this).jqGrid('editGridRow', rowid,
{ recreateForm:true,
closeAfterEdit:false,
closeOnEscape:true,
reloadAfterSubmit:false})
}
});
});

The post enable_edit() * appeared first on phpGrid - PHP Datagrid.

enable_export() *

Tue, 14 Sep 2010 07:51:08 +0000

* In Basic edition, export feature is limited to non-lookup tables only

Parameter(s):
- $type: Export type can be EXCEL, EXCELXML, PDF, CSV, or HTML. The default is EXCEL, the native .xls format. EXCELXML is the OpenOffice .xml format.
- $PDF_logo: Array. Requires commercial license 6.53+. It contains properties for
  - image logo file path,
  - width (optional)
  - height (optional)
  - static text (optional)
Description:
- When export is enabled, the export icon appears in the grid footer. The datagrid can be exported in EXCEL, PDF, CSV or HTML format. The Excel is exported in native Excel XML format. More formats will be supported in the future.
- PDF export can take one array parameter for its logo. The second parameter can have logo file path, and optional width and height. When width and height are not defined, their values are based on 1/4 of actual logo image integer dimension.
Example:

1	$dg -> enable_export('EXCEL');

Export in PDF with a logo image (PDF logo requires commercial license 6.53+)

1
2
3

$count = $dg->db->num_rows($dg->db->db_query('SELECT * FROM orders'));
$pdf_prop = array(realpath('../logos/phpgrid-logo.jpg'), 50, 15, $count);
$dg -> enable_export('PDF', $pdf_prop);

The post enable_export() * appeared first on phpGrid - PHP Datagrid.

enable_resize()

Tue, 14 Sep 2010 07:53:48 +0000

Parameter(s):
- $is_resizable: boolean value indicating whether the phpGrid is resizable.
- $min_w: minimum resizable width of the datagrid. The default is 350 pixel.
- $min_h: minimum resizable height of the datagrid. The default is 80 pixel.
Description:
- When $is_resizable is set to true, the small triangle shaped icon is displayed at the right bottom corner of the php data grid. The grid can be resized by simply click and drag the icon using mouse. This is more convenient than setting the width and height by programming.
Remark:
- The method utilizes jQuery UI library. This is currently an experimental feature.
Example:

1	$dg -> enable_resize(true);

The post enable_resize() appeared first on phpGrid - PHP Datagrid.

display()

Tue, 14 Sep 2010 01:09:59 +0000

Parameter(s):
- $render_content: Optional. True or false. Render content to screen, default value is set to true.
Description:
- This is the method to call to display the datagrid. This is usually the last method to call. It automatically stores the output to output buffer first before rending the data grid.
Remark:
- Unless used together wtih get_display(), it’s not necessary to supply a parameter value.
Example:

1	$dg -> display();

The post display() appeared first on phpGrid - PHP Datagrid.

enable_debug()

Tue, 14 Sep 2010 08:13:06 +0000

Note: this method is going to be deprecated in the future. Please use DEBUG global constant instead.

Parameter(s):
- $debug: boolean value indicating whether to display debug message
Description:
- Enable debug displays phpGrid object as well as the current PHP session information. In addition, XMLHttpRequest, or AJAX, response data is displayed that occurs during CRUD operations. This is helpful debugging information can be used during development.
- It also display the phpGrid version number and associated javascript libraries used.
Remark:
- Make sure this method is not called, or the $debug parameter is set to false, when deployed in production.
Example:

1	$dg -> enable_debug(true);

The post enable_debug() appeared first on phpGrid - PHP Datagrid.