• System Requirements
    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.
    Continue reading →
  • Technical Overview
    • 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.

    Continue reading →
  • Installation

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

    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.

    install phpgrid

    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.

    demo explorer

    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.
    new 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.
    X

    Simply run the following install command in phpGrid root folder

    1
    composer install

    Addition Resources on Composer

    Continue reading →
  • phpGrid Constructor

    After successful installation, call the constructor like the following:

    1
    $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.
    X

    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 composite primary key online example.

    Continue reading →
  • set_query_filter($WHERE)
    • 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' ");
    Continue reading →
  • enable_edit() *

    * 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 </body>. 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})
                }
            });
    });
    Continue reading →
  • enable_export() *

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

     
    pdf-export-prop

    Continue reading →
  • enable_resize()
    • 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);
    Continue reading →
  • display()
    • 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();
    Continue reading →
  • enable_debug()

    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);
    Continue reading →
  • enable_search()
    • Parameter(s):
      • $can_search: boolean. When set to true, the search icon displays in the footer, and the integrated search is toggled when the icon is clicked.
      • $auto_filter: Array. List of fields used for Excel-like auto filter (Local array data source only. version 6.52+)
    • Description:
      • The integrated search provides an intuitive interface for searching data within the datagrid. The user can search one or multiple fields. No additional programming is required. The datagrid is automatically refreshed and repopulated with results returned from the query through AJAX.
    • Remark:
      • The integrated search is toggle off by default. Click on the Search button in the footer to toggle on the integrated search. To toggle search on by default. Please see this KB.
      • The second parameter is a paid feature only that auto-generates filters based on field contents. It’s similar to how Excel auto filter works. It works with local array data source only where data are loaded at once.
    • Example:
    1
    $dg -> enable_search(true);
    Continue reading →
  • set_caption()
    • Parameter(s):
      • $caption | false: the text for the grid caption. Set to false to completely hide the caption.
    • Description:
      • When set, this method displays text above the column header as the datagrid caption. When this method is not called, phpGrid sets the table name as the default caption. For example, the caption will be displayed as “Orders” when the table name is “Orders”.
    • Remark:
      • When caption is set to false, the space used for displaying caption will be hidden, leaves only the column header, grid, and the footer. To display the caption without any text, uses ” ” without the quote.
    • Example:
    1
    $dg -> set_caption(false); // this will hide the caption
    Continue reading →
  • set_col_currency()
    • Parameter(s):
      • $col_name: column name
      • $prefix: currency prefix. The default is USD symbol, $
      • $suffix: currency suffix. The default is blank.
      • $thousandsSeparator: The character as the thousands separator. The default is the comma character “,”.
      • $decimalSeparator: The character as the decimal separator. The default is the dot character.
      • $decimalPlaces: The number of digital is displayed after the decimal point. The default is two.
      • $defaultValue: The value display when the value return from database is null or empty string. The default is 0.00
    • Description:
      • This is helper method to display currency value. It formats column as currency when displayed. The value is automatically unformatted during CRUD operations.
    • Example:
    1
    $dg -> set_col_currency("price", "EUR", "", ",", 2, "0.00");

    How to enter comma as decimal separator during data entry

    The function set_col_currency() is a specialized formatter for displaying currency only. During data entry, it is still required to use the dot/period symbol is as the decimal separator.

    To allow some countries that use a comma (,) instead of decimal to indicate that separation, a common workaround is replacing “,” with “.” as soon as “,” is entered during data entry using jqGridAddEditAfterShowForm event handler that evokes onkeyup javascript keyboard event.

    1
    2
    3
    4
    5
    6
    $afterShowForm = <<<AFTERSHOWFORM
    function (){
        $("#MSRP").attr("onkeyup","replacekomma()");
    }
    AFTERSHOWFORM
    ;
    $dg->add_event("jqGridAddEditAfterShowForm", $afterShowForm);
    Continue reading →
  • set_col_dynalink()

    Now supports local array (version 6) !

    • Parameter(s):
      • $col_name: Name of column to display dynamic URL
      • $baseLinkUrl: The base URL. e.g. “http://www.example.com”
      • $dynaParam: Name of a single data field OR an array of multiple fields. The default is “id” if omitted.
      • $staticParam: Optional. Parameters with static value in addition to $dynaParam. They are added to the end of the URL string. e.g. “&foo=bar”
      • $target: Optional. This sets the value of the hyperlink “Target” attribute. The available options are:
        • _new
        • _blank
        • _top
        • _parent
      • $prefix: prefix to hyperlink
    • Description:
      • This method sets a specific column to display HTML hyperlink URL based on dynamic values. It is different from set_col_link() method where it is simply a static link stored in a data field.
    • Remark:
      • Developers should always check the validity of the dynamic URL constructed when using this method.
    • Example:
    1
    2
    3
    4
    // a single dynamic field as $dyanParam parameter
    $dg -> set_col_dynalink("productCode", "http://www.example.com/", "orderNumber", '&foo=bar', "_top");
    // an array of multiple dynamic fields as $dynaParam parameter
    $dg -> set_col_dynalink("productCode", "http://www.example.com/", array("orderNumber", "customerNumber", "price"), '&amp;foo=bar', "_top");
    Continue reading →
  • set_col_edittype()
    • Parameter(s):
      • $col_name: Column name
      • $ctrl_type: HTML control type. A list of valid controls are:
        • text
        • textarea
        • select
        • checkbox
        • password
        • button
        • autocompleteNew!

        For enum type, use select as edit type and pass hard coded key:value pair as 3rd parameter

      • $keyvalue_pair: This parameter is only required when the $ctrl_type is type “select” or “checkbox”. It essentially represents list of value key pairs in the dropdown. e.g. “1:San Francisco;2:Boston;3:NYC”. It is ignored if the control type is not “select”.*

         
        Hint: add “:;” (colon semicolon) to the front of the the key value pairs to add a blank dropdown option. Make sure there is no trailing semi-colon.

        v4.2 Update:

        You can now use values from a database lookup table to bind the dropdown. Simply pass SELECT statement for the $keyvalue_pair when the $ctrl_type is “select”. For example: SELECT key, value FROM FOO
        X
      • $multiple: This parameter is only used when control type is “select”. It indicates whether multiple items can be selected in the list of options. The values are passed and stored as a comma delimited string. e.g. “1,2,3”. For single value “select”, this is not required.
      • $dataUrl: If your lookup table has more than 100 elements (a hard limit set in function), it is recommended to use this parameter. This option is valid only for elements of type “autocomplete” and should be the URL to get the AJAX data for the select element. The data is obtained via an AJAX call and should be a valid HTML select element with the desired options <select><option value=’1′>One</option>…</select>. You can use option group. The AJAX request is called only once when the element is created. In the inline edit or the cell edit module it is called every time when you edit a new row or cell. In the form edit module only once.
    • Description:
      • Set control of a specific column used for grid editing. It displays HTML input tag of different type accordingly e.g. <input type=”text” />, <input type=”textarea” /><
    • Remark:
      • Text and textarea are set automatically based on the database access library ADOdb metatype as the default edit control. Users do not need to understand how ADOdb works since phpGrid took care all the plumbing work internally. Isn’t that nice?
    • Example:
    1
    2
    3
    4
    $dg -> set_col_edittype("officeCode", "select", "1:San Francisco;2:Boston;3:NYC", false);
    $dg -> set_col_edittype("isActive", "checkbox", "1:0");
    // bind dropdown menu to a lookup table
    $dg -> set_col_edittype("officeCode", "select", "Select officeCode,city from offices",false);

    An example that uses $dataUrl to filter potentially large sets of data in select options.

    1
    2
    3
    $dg -> set_col_edittype("officeCode", "autocomplete",
        array('sql' => 'Select officeCode,city from offices150k',
              'ajaxDataUrl' => 'sample-select-dataUrl.php'), false);

    An example of “sample-select-dataUrl.php

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    use phpGrid\C_DataGrid;
    use phpGrid\C_DataBase;

    require_once("../conf.php");

    $db = new C_DataBase(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME, PHPGRID_DB_TYPE,PHPGRID_DB_CHARSET);

    $result = $db->select_limit("Select officeCode, city from offices150k WHERE city like '%". $_GET['q'] ."%'", 10, 0);

    $options = array();
    while($row = $db->fetch_array_assoc($result))
    {
        $options[] = (object)array('id'=>$row['officeCode'], 'text'=>$row['city']);
    }
    $select_options['results'] = $options;

    echo json_encode($select_options);
    Continue reading →
  • set_col_format()
    • Parameter(s):
      • $col_name: Datagrid column name
      • $format: The format types are:
        • integer
        • number
        • email
        • link
        • showlink
        • currency
        • date
        • checkbox
        • select
        • rating (supported on v7.1.5+)
        • custom formatter *
      • $formatoptions: PHP array stores format options of different formatting.
    • Description:
      • Although this method is purely for cosmetic purpose, it is very useful and also practical in many situations when displaying different type of data.
      • Depending on the type of format, it could have different format options. In most cases, users only need to set column formatting for “integer”, “number”, and “mail” using this method. For “currency’, “link”, and “showlink” formats, phpGrid provides a number of helper functions to make formatting simpler and easier. They are
      • The helper functions will be covered later in the documentation.
      • For type “date”, users don’t have to do anything for formatting. The format is automatically set to “date” when the database data field type is date or datetime.
      • In addition, the formats are automatically set as “checkbox” or “select” if the edit type is checkbox or select defined in set_col_edittype().
    • Remark:
      • Though all of the formattings can be done in this method, it is recommended to use helper functions whenever possible. phpGrid ensures the formatting is automated as much as possible. Automation means less coding, and thus less room for error. Simplicity is what phpGrid strives for.
    • Example:

    1
    2
    3
    $dg -> set_col_format('quantity','integer', array('thousandsSeparator'=>',', 'defaultValue'=>'0'));
    $dg -> set_col_format("emailAddress", "email");
    $dg -> set_col_format("weight", 'number', array("thousandsSeparator" => ",", "decimalSeparator" => ".", "decimalPlaces" => '2', "defaultValue" => '0.00'));
     
     
     
     

    Custom Formatter *

    To create custom formatter, please see set_col_property advanced method and custom formatter example. Some javascript knowledge is required.

    Continue reading →
  • set_col_hidden()
    • Parameter(s):
      • $col_name: Comma delimited one or more column names
      • $edithidden: Boolean value indicating whether the column is editable. The default value is set to true. This only applies when the edit mode is “FORM”.  A hidden column cannot be editable in “INLINE” edit mode.
    • Description:
      • Hide a column in datagrid when displayed
    • Remark:
      • You can hide more than one columns at a time (version 7.1.5+).
      • To hide column in both datagrid and form, set the second parameter to false
      • Very Important:
        The data are still sent to the browser but hidden using CSS display:none. For sensitive data such as passwords and SSN etc, do not use this method, instead do not include those fields in your SQL at all.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    // hide only in grid display
    $dg -> set_col_hidden('employeeNumber');

    // hide in both gird and edit form
    $dg -> set_col_hidden('DOB', false);

    // hide more than one columns at once (version 7.1.5+)
    $dg -> set_col_hidden('column1, column2, column3, column4, column5', false);
    Tip:
    To do the reverse, meaning to have a field appear in grid, but not in the form, use set_col_property set editable to false and hidedlg to true.

    1
    $dg->set_col_property('COLUMN_NAME', array('editable'=>false,'hidedlg'=>true));
    Continue reading →
  • set_col_img()
    • Parameter(s):
      • $col_name: Colume name
      • $base_path: Optional. Base path to image
    • Description:
      • Display as image for a data field in data grid. The 2nd parameter specifies the base path to the image file.
    • Example:

    Assuming the productImag value is “abc.jpg“,

    The image file ‘abc.jpg’ is stored under ‘path-to-images’ folder on web server.

    So the direct full URL to the image would be something like this

    1
    https://www.example.com/path-to-images/abc.jpg

    The following will render a img tag similar to <img src=”/path-to-images/abc.jpg”>. The 2nd parameter “/path-to-images/” form the base path to the “abc.jpg”.

    1
    $dg -> set_col_img("productImage", "/path-to-images/");

     
     
     

    Watch Tutorial Video

    Continue reading →
  • set_col_link()
    • Parameter(s):
      • $col_name: Column name
      • $target: Hyperlink target attribute. The default is _new
    • Description:
      • Display the text in column as a static hyperlink.
    • Remark:
      • Use this method for displaying static HTML hyperlink. For dynamic links, use set_col_dynalink() method.
    • Example:
    1
    $dg -> set_col_link("productCode");
    Continue reading →
  • set_col_readonly()
    • Parameter(s):
      • $col_names: Comma delimited one or more column names
    • Description:
      • Set one more more columns as read only field
    • Example:
    1
    $dg -> set_col_readonly("SSN, lastname, employeeNumber");

    Remark:

    To allow add for read-only field when adding new records, use “jqGridAddEditAfterShowForm” event, and conditionally remove the “disabled” attribute based on operand, which is either “edit” or “add”.

    1
    2
    3
    4
    5
    6
    $dg -> add_event('jqGridAddEditAfterShowForm',
      'function(e, form, oper){
        if (oper == "add") {
          $("#COLUMN_NAME").removeAttr("disabled");
        }
      }'
    );
    Continue reading →
  • set_col_required()
    • Parameter(s):
      • $col_names: Comma delimited one or more column names
    • Description:
      • Set one more more columns as required field during edit
    • Remark:
      • Use this function for any database table fields that are NOT NULL, or the INSERT will fail.
    • Example:
    1
    $dg -> set_col_required("phone, email");
    Continue reading →
  • set_col_title()
    • Parameter(s):
      • $col_name: Colume name
      • $new_title: Display title in grid column header
    • Description:
      • Change data grid column header text from the default data field name to more user friendly name
    • Remark:
      • The default column header text is the database table data field name
    • Example:
    1
    $dg -> set_col_title("fname", "First Name");
    Continue reading →
  • set_dimension()
    • Parameter(s):
      • $width: Datagrid width
      • $height: Datagrid height. The default is 100% which expanses grid vertically to the entire browser height
      •  $shrinkToFit: true or false. If set to false, horizontal bar will appear when the total width of grid is wider than the dimesion width. The default is true.
    • Description:
      • Set the overall height and width of phpGrid
    • Remark:
      • It is recommended to set $shrinkToFit to false when there are a large number of columns to display.
    • Example:
    1
    $dg -> set_dimension(800, 600);
    Continue reading →
  • set_jq_datatype()
    • Parameter(s):
      • $datatype: Internal data source type. It can be either “json” or “xml“. The default is “json“.
    • Description:
      • Manually sets data type used by jqGrid when displaying data grid.
    • Remark:
      • In most cases, you do not need to call this method.
    Continue reading →
  • set_jq_editurl()
    • Parameter(s):
      • $url: URL
    • Description:
      • Manually set URL used for editing. The default URL is edit.php. phpGrid takes care all of data insert, delete, and update in edit.php. No coding is required.
    • Remark:
      • Only use this method when the users need to manually handle data update using custom save routine.
      • It’s the same as setting the 3rd parameter in enable_edit() method.
    Continue reading →
  • set_jq_gridName()
    • Parameter(s):
      • $grid_name: Unique name of the datagrid used internally by phpGrid
    • Description:
      • Manually set the unqiue name of the datagrid. By default, the name is the database table name associated with the grid.
    • Remark:
      • In most cases, you do not need to call this method.
    Continue reading →
  • set_masterdetail() *

    * Please note this feature is only available in Premium and higher licenses.

     

    • Parameter(s):
      • $obj_grid: phpGrid object as the detail datagrid
      • $fkey: Foreign key of the detail grid to the master datagrid.
      • $m_fkey: Foreign key of the master grid to the detail grid. It’s optional when $fkey and $m_fkey have the same column name. (supported since version 7.1)
    • Description:
      • This method sets the master detail, or the parent child, relationship between datagrids.
      • The phpGrid went the extra mile to make creating the master detail datagrid a simple task for developers. The primary key value of master datagrid is automatically retrieved when a user selects a row. The value is used as the foreign key for the detail datagrid. The detail datagrid renders dynamically based on the row selected in the master grid.
    • Remark (important):
      • The detail grid is a regular phpGrid object and can have the same methods like any other phpGrid, such as description title, sort, and update etc. A master detail can have one or more detail datagrid.
      • The foreign key, or $fkey, does NOT necessarily have to have the same name as the master table primary key, as long as the same name exists in both master and detail tables.
      • When foreign keys don’t have the same name, you should use SQL alias in the master grid. However, if the grid is editable, it’s important to call set_col_readonly() and seike>e new alias to be read only. the 3rd parameter to reference to the foreign key from the master datagrid.
      • By leaving the foreign key field blank in detail grid, master grid will prefill its detail grid with the value of the foreign key before the new record is saved/inserted to the database table. Or simply hide the entire foreign key column (Version 4.5.5 and above only).
    • Example:

    When the master detail reference keys have the same name,  the 3rd parameter can be omitted.

    1
    2
    3
    4
    // orderId exists in both master table(Orders) and detail table(OrderDetails)
    $dg = new C_DataGrid("SELECT orderId, orderDate, customerId FROM Orders", "orderNumber", "Orders");
    $sdg = new C_DataGrid("SELECT lineId, orderId, productCode, price FROM OrderDetails", "lineId", "OrderDetails");
    $dg -> set_masterdetail($sdg, 'orderId');

     

    Use the 3rd parameter when the keys to reference have different name in master and detail tables.

    1
    2
    3
    4
    5
    // productId is SQL alias to productNo in master table(ProductParts), then used as foreign key to link to detail table(Products).
    $dg = new C_DataGrid("SELECT partNo, partName, productNo FROM ProductParts", "partNo", "ProductParts");
    $dg -> enable_edit('INLINE', 'CRUD')->set_col_readonly("productId");
    $sdg = new C_DataGrid("SELECT productId, productName, productDescription FROM Products", "productId", "Products");
    $dg -> set_masterdetail($sdg, 'productId', 'productNo');

    Still having trouble editing master detail grids with column name alias?
    Please see Master detail grid with column name alias

    Continue reading →
  • set_multiselect()
    • Parameter(s):
      • $multiselect:  Boolean value indicates whether the multiselect is allowed
      • $multipage:  Boolean. Persisted selection following pagination
    • Description:
      • When parameter $multiselect is set to true, a checkbox is shown to the left of each row. It is currently only used for enabling deleting multiple records. More functions will be added to this feature in the future.
    • Example:
    1
    $dg -> set_multiselect(true);
    Continue reading →
  • set_pagesize()
    • Parameter(s):
      • $pagesize: An integer value indicates number of results displaying in a page in the datagrid
    • Description:
      • This method changes the default pagination of the datagrid. The default is 20.
    • Example:
    1
    $dg -> set_pagesize(40);  // display up to 40 rows in a page
    Continue reading →
  • set_row_color()
    • Parameter(s):
      • $hover_color: Background color when mouse is over a row
      • $highlight_color: Background color when a row is clicked on
      • $alt_color: Alternating row color
    • Descript:
      • This method is pure cosmetic. When modified, it overwrites color of jQuery UI properties:
        • ui-state-hover,
        • ui-state-highlight
        • ui-priority-secondary
    • Example:
    1
    $dg -> set_row_color('yellow', 'lightblue', 'lightgray');
    Continue reading →
  • set_scroll()
    • Parameter(s):
      • $scroll: Boolean value indicates wheter
      • $height: phpGrid height
    • Description:
      • Use vertical scroll to load data. The datagrid will not load everything at once and will hold all the items from the start through to the latest point ever visited. This prevents memory leaks from happening.
    • Remark:
      • Pagination is disabled when scroll is set to true.
      • The default height is 400 when scroll is true and should never be ‘100%’.
    • Example:
    1
    $dg -> set_scroll(true);
    Continue reading →
  • set_sortname()
    • Parameter(s):
      • $col_name: Column name to sort
      • $sort_order: ‘ASC’ or ‘DESC’. Default value is ‘ASC’  (version 4.4+)
    • Description:
      • Set column sort by and sort order when datagrid is initially loaded.
    • Example:
    1
    $dg -> set_sortname('productName', 'DESC');
    Continue reading →
  • set_subgrid()
    • Parameter(s):
      • $obj_grid: phpGrid object as subgrid
      • $s_fkey: subgrid foreign key.
      • $m_fkey: Optional. Master foreign key.
    • Description:
      • The method displays inline detail grid rather than in separate datagrid table. It is very similar to set_masterdetail(). When ignored, phpGrid assumes that m_fkey has the same value as the s_fkey.
      •  new The $m_fkey value from the master datagrid is automatically retrieved when a user selects a row.  The value is passed to the pop-up Add form in the subgrid.  ( supported since 7.1.5)
        1
        2
        3
        ...
        $dg->set_subgrid($sdg, 'sales_rep', 'id');
        ...

        subgrid-master-linking-value

    • Remark:
      • Edit is currently not supported in subgrid.
      • Nested subgrid is currently not supported,  Nested/drill-down subgrid is now supported (version 6+).
      • The order of the 2nd and 3rd parameter matters,
      • When the subgrid is the same table(self-reference, in database term) as the master grid, subgrid must use a different table alias in select statement using set_jq_gridName().
      • All the grid properties and method should be called BEFORE set_subgrid is called.
      • Do not include ‘WHERE’ in set_query_filter() when used on subgrid.
      • Due to the calling sequence, the enable_edit() should be called BEFORE set_subgrid() method, or edit properties will be ignored.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    $dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
    $dg->enable_edit("INLINE", "CRUD");

    // 2nd grid as detail grid
    $sdg = new C_DataGrid("SELECT * FROM orderdetails", array("orderLineNumber", "productCode"), "orderdetails");
    $sdg->enable_edit("INLINE", "CRUD");

    // 3rd grid as detail grid.
    $sdg2 = new C_DataGrid("SELECT * FROM products", array("productCode"), "products");
    $sdg2->enable_edit("INLINE", "CRUD");

    // passing the detail grid object as the first parameter, then the foreign key name.
    // set_subgrid should be the last method to call before display
    $sdg->set_subgrid($sdg2, 'productCode');
    $dg->set_subgrid($sdg, 'orderNumber');

    $dg->display();
    Continue reading →
  • set_theme()

    * Premium themes are currently available in phpGrid commercial licenses.

     

    Note: starting version 6, you can set a global constant THEME in conf.php to change all of your datagrid themes.
    X
    • Parameter(s):
      • $theme_name: Theme name
    • Description:
      • This is the phpGrid “Theme Roller”. phpGrid bundles the following themes from jQuery UI:
        • dot-luv
        • excite-bike
        • flick
        • overcast
        • pepper-grinder
        • redmond
        • smoothness
        • start
        • ui-darkness
        • ui-lightness
        • aristo ————– (Premium theme) *
        • cobalt ————– (Premium theme) *
        • cobalt-flat ——— (Premium theme) *
        • bootstrap ———– (Premium theme) *
      • The default theme is ‘start‘.
    • Remarks:
      • Both Bootstrap 3 and 4 (7.4.5+) themes are now supported with ‘bootstrap‘ or ‘bootstrap4
      • Bootstrap 5 theme is supported in version 7.5.5 with bootstrap5
    • Example:
    1
    $dg -> set_theme('flick');
    Continue reading →
  • set_col_width()
    Starting version 6.6, phpGrid will automatically resize column width based on its content during page load. Users can also double-click a column divider to resize a column.
    X
    • Parameters:

      • $col_name: column name
      • $width: width
    • Description:

      • Specify column width in pixel. The width should be an integer value.
    • Example:
    1
    $dg -> set_col_width("comments", 500);
    Continue reading →
  • set_group_properties() *

    * Please note this feature is only available in paid versions.

    • Parameters:

      • $column_name: column name for grouping
      • $groupCollapsed: boolean value indicating whether to expand or collapse grouping when displayed. The default is false.
      • $showSummaryOnHide: boolean value to whether display group summary when grouping is collapsed. The default is true.
      • $groupColumnShow: boolean value to display group column. Default is false.
      • $hideGroup: boolean. Hide grouped rows. Default is false. (v7.1+)
    • Description:

      • Group grid values simply by specifying a column name.
    • Remark:

    • Example:
    1
       $dg -> set_group_properties('customerNumber');
    Continue reading →
  • set_group_summary() *

    * Please note this feature is only available in paid versions.

    • Parameters:
      • $column_name: column name for grouping
      • $summaryType: summary type. It can be one of the types below
        • sum
        • count
        • avg
        • min
        • max
    • Description:
      • Display group summary of a specific summary type, such as sum or max, in the group summary footer.
    • Remark:
    • Example:
    1
    2
    3
    $dg -> set_group_properties('customerNumber');
    $dg -> set_group_summary('amount','sum');
    $dg -> set_group_summary('checkNumber','count');
    Continue reading →
  • enable_rownumbers()
    • Parameters:
      • $has_rownumbers: boolean value indicating whether row number is displayed.
    • Description:
      • Display row number before each row.
    • Example:
    1
    $dg -> enable_rownumbers(true);
    Continue reading →
  • set_col_align()
    • Parameters:
      • $col_name: column name
      • $align: alignment direction: “left”, “center”, or “right”.
    • Description:
      • Horizontal alignment of text in a column. The default alignment is left.
    • Example:
    1
    $dg -> set_col_align('amount', 'right');
    Continue reading →
  • enable_advanced_search()
    • Parameters:
      • $has_adsearch: boolean value. Set true to enable advanced search
    • Description:
      • Advanced search is different from the integrated search (see enable_search()). It can apply mulitple conditionals to a single field during advanced search. It is super flexible and easy to use.
    • Example:
    1
    $dg -> enable_advanced_search(true);
    Built-in Advanced Search
    Built-in Advanced Search
    Continue reading →
  • set_locale()
    • Parameters:
      • $locale: language locale. List of values are: bg, cat, cn, cs, de, dk, el, en, fa, fi, fr, he, hu, is, it, ja, nl, no, pl, pt, ro, ru, sp, sv, tr, ua.
    • Description:
      • If you are using phpGrid in a language rather English, you may want the datagrid to display appropriate system text used by phpGrid such as “Submit”, “Next”, “Page”, “Find” etc. The language locale files are located in js\src\i18n directory.
    • Example:
    1
    $dg -> set_locale("fr"); // French locale
    Continue reading →
  • set_conditional_format() *

    * Please note this feature is not available in Lite and Basic versions.

    • Parameters:
      • $col_name: column name
      • $type: CELL or ROW
      • $formatoptions: array that stores condition and css style options
    • Description:
      • Format a cell based on the specified condition. CELL formats the a single cell. ROW formats the entire row.
         

        $formatoption is the most important parameter which itself is an array contains three elements:

        • condition,
        • value,
        • css.
         

        The elements are described in details as the following:

        The first parameter, condition, has the following available value.

        eq: Equals
        ne: Not Equals
        lt: Less than
        le: Less than or Equal
        gt: Greater than
        ge: Greater than or Equal
        cn: Contains
        nc: Does not Contain
        bw: Begins With
        bn: Not Begins With
        ew: Ends With
        en: Not Ends With

        The second parameter, value, is the comparing value in the right operand.

        css parameter in $formattoptions is an array collection that contains css style element for current cell or row.

    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    //Format a cell based on the specified condition
    $dg->set_conditional_format("orderNumber","CELL",array("condition"=>"eq",
                                                          "value"=>"10107",
                                                          "css"=> array("color"=>"#ffffff","background-color"=>"green")));

    $dg->set_conditional_format("customerNumber","CELL",array("condition"=>"eq",
                                                      "value"=>"141",
                                                      "css"=> array("color"=>"red","background-color"=>"#DCDCDC")));

    // Format a row based on the specified condition
    $dg->set_conditional_format("comments","ROW",array("condition"=>"cn",
                                                      "value"=>"request",
                                                      "css"=>array("color"=>"white","background-color"=>"#4297D7")));
    Continue reading →
  • set_databar() *

    * Please note this feature is not available in Lite and Basic versions.

    • Parameters:
      • $col_name: Name of column to display data bar. It should be numeric type
      • $color: Data bar color
    • Description:
      • Creates a data bar with gradient effect on a specified column. For complex data visualization, PHP Chart is highly recommended.
    • Remark:
      • For complex data visualization, we recommend phpGrid sister product, PHP Chart, a highly customizable PHP charting solution.
    • Example:
    1
    $dg -> set_databar("percentComplete","red");
    Continue reading →
  • set_conditional_value() *

    * Please note this feature is not available in Lite and Basic versions.

    • Parameters:
      • $col_name: Name of the column. Its value is used as the left operand for comparison.
      • $condition: comparison condition that compares given left and rigth hand operand with comparison operator . e.g. ” > 10 “; ” = ‘abc'”; ” != true ” etc.
      • $formatoptions:  an array stores CSS style format options. The value can be text, HTML, or even CSS class. See example.
        • TCellValue: value to display when condition is True
        • FCellValue: value to display when condition is False
    • Description:
      • It formats and displays value in a cell when specified condition is met. You can think it as a simplified version to set_conditional_format() method.
    • Remark:
      • The condition is not “stackable” for a single column. In other words, only one condition can be applied to any giving column.
    • Example:

    Note that tstyle and fstyle are CSS styles used as the formatoptions.

    1
    2
    3
    4
    5
    6
    7
    8
    .tstyle
    {
    display:block;background-image:none;margin-right:-2px;margin-left:-2px;height:14px;padding:5px;background-color:green;color:navy;font-weight:bold
    }
    .fstyle
    {
    display:block;background-image:none;margin-right:-2px;margin-left:-2px;height:14px;padding:5px;background-color:yellow;color:navy
    }

    PHP Code

    1
    2
    3
    4
    5
    6
    7
    $dg->set_conditional_value("isClosed", "==1", array(
    "TCellValue"=>"<img src="SampleImages/checked.gif" alt="" />",
    "FCellValue"=>"<img src="SampleImages/unchecked.gif" alt="" />"));

    $dg->set_conditional_value("status", "=='Complete'", array(
    "TCellStyle"=>"tstyle",
    "FCellStyle"=>"fstyle"));
    Continue reading →
  • enable_autowidth()
    • Parameters:
      • $autowidth: true or false
    • Description:
      • Use this method to set datagrid width as the same as the current window width.
      • Automatically resizes based on window width (verison 6+)
    • Example:
    1
    $dg -> enable_autowidth(true);

    Set auto width to expand its container (e.g. body), then followed by setting static width for horizontal scroll. Works the best when the grid has many columns but in a small container.

    1
    2
    $dg->enable_autowidth(true);
    $dg->set_dimension('2000', 'auto', false);

    Optional

    Sometimes the browser overcalculates the document width and displays the scrollbar. You can hide it using the following CSS.

    1
    2
    3
    body{
      overflow:hidden;
    }
    Continue reading →
  • get_display()
    • Parameters:
      • $add_script_includeonce: true or false. Whether or not to include script header.
    • Description:
      • It returns the grid body with options to include script header. It is useful for MVC framework integration such as Joomla! and Drupal.
    • Remark:
      • The get_display() function should be called AFTER display() is called so that the grid body and header is saved to an internal output buffer that can be later retrieved by get_display().

    Example:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    $dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
    $dg -> display(false);  // do not render display
    $grid = $dg -> get_display(false);  // do not include required javascript libraries until later with with display_script_includeonce method.

    // other page content goes here
    /* ........................... */

    $dg -> display_script_includeonce();
    echo $grid;
    Continue reading →
  • enable_kb_nav() – beta
    • Parameters:
      • $is_enabled: true or false
    • Description:
      • Enable navigation by keyboard using up and down arrow keys.
    • Remark:
      • This is a beta feature currently. Not recommended using it in production.
    • Example:
    1
    $dg->enable_kb_nav(true);
    Continue reading →
  • set_form_dimension()
    • Parameters:
      • $f_width: form width. The default value is ‘400px’
      • $f_height: form height. The default is -1 so it is automatically adjusted to number of rows.
    • Description:
      • Set edit FORM width and height during edit.
    • Remark:
      • The default $f_height value is -1 (negative one) so it is automatically adjusted to number of rows.
      • The function only can apply to non-bootstrap themes such as ‘aristo’ or ‘cobalt’. For Bootstrap themes, it has no effect as the layout is managed by the Bootstrap framework.
    • Example:
    1
    $dg->set_form_dimension('500px');
    The function only can apply to non-bootstrap themes such as ‘aristo’ or ‘cobalt’. For Bootstrap themes, it has no effect as the layout is managed by the Bootstrap framework.
    X
    Continue reading →
  • setCallBack
    • Parameters:
      • $callback_str: callback string
    • Description:
      • Used for pass setting between 3rd party system and phpGrid using AJAX callback. Read the comments in file callbackstr.php for usage
    Continue reading →
  • set_col_wysiwyg()

    * Only available in the Premium & Ultimate version.

    • Parameters:
      • $col_name: column name
    • Description:
      • Turns simple text field into a word processor by replacing textarea with a rich text editor. Should only be used for text fields that allow a large amount of text such as notes and comment fields.
    • Remark:
      • The field must be a text data type. If not, use set_col_edittype and set edit type to “textarea” first.
    • Example:
    1
    2
    // comments is text data type
    $dg->set_col_wysiwyg('comments');
    1
    2
    // news is not a text data type
    $dg->set_col_edittype('news', 'textarea')->set_col_wysiwyg('news');

    You can set its width height using set_col_edit_dimension method.

    phpgrid wysiwyg - form edit

    phpgrid wysiwyg - inline edit

    Continue reading →
  • set_col_default()
    • Parameters:
      • $col_name: column name
      • $default: column default value
    • Description:
      • Set column default value This option is only valid for Form Editing when adding a new record. Users can override this entry.
    • Remark:
      • To set the default value for edit existing records, use beforeEditRow event.  Be sure to set default only when the value is blank otherwise any populated values entered by users would be overwritten.
    • Example:
    1
    2
    $dg -> set_col_default('direction', 'top');
    $dg -> set_col_default('dateField',  date("Y-m-d H:m"));
    Continue reading →
  • set_col_fileupload() *

    * Please note this feature is currently only available for commercial licenses.

    • Parameters:
      • $col_name: column name that stores file name
      • $base_url: base URL to the files
      • $physical_path: specify the physical path to the folder where the files are uploaded. When PHP is installed as Apache module, this parameter is optional.
    • Description:
      •  The file upload is seamlessly integrated with the edit form using Ajax, no page refresh ever occurred. Files can be uploaded and deleted right within the form.
    • Remark:
      • A single file upload per datagrid is allowed.
      • FORM edit mode only
      • File system only with valid upload_tmp_dir value in php.ini
      • Filename column should allow NULL value
      • $physical_path is automatically obtained if PHP running in Apache module, or MUST be provided as a fallback
      • When receiving alert message “unexpected token <“, it is your upload folder that doesn’t allow write permission. Please set the folder permission to be writable.
      • By default, only JPEG files are allowed for file upload, but one can change the upload file extension to include additional file formats in conf.php called ‘UPLOADEXT
      The 2nd and 3rd parameters are now dropped. Instead the user must set the upload directory value ‘UPLOADDIR’ in conf.php
      X
    • Example:
    1
    2
    3
    // conf.php
    define('UPLOADEXT', 'gif,png,jpg,jpeg');
    define('UPLOADDIR', '/var/www/web/myuploads/');
    1
    2
    // sample_file_upload.php
    $dg -> set_col_fileupload("employee_photo");
    Continue reading →
  • set_col_date()
    • Parameters:
      • $col_name: column name
      • $srcformat: source date format, eg. “Y-m-d”
      • $newformat: new date form to be displayed, eg. “m/d/Y”
      • $datepicker_format: datepicker format, eg. “m/d/yy”
    • Description:
      •  Helper function to format date using PHP date format options (http://php.net/manual/en/function.date.php). MySql always stores date in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. However, we can still change the front-end date display format while conform to its date standards.
    • Remark:
      • In most cases, you don’t need to call this method because phpGrid automatically formats the data with corresponding data type including date.
      • Only use this method in order to display date format that is different from default date format.
      • To display time only, please use set_col_property instead.
    • Example:

    Format date to “m/d/Y”, the Spanish date format. The example illustrates two different methods. You can use either or.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    // srcFormat: MySql date format is "Y-m-d"
    // newFormat: Display date format is "m/d/Y"
    // datepicker: Datepicker date format is "yy-mm-dd"

    // Method 1: change date display and datepicker display (used for edit) to Spanish date
    $dg -> set_col_date("orderDate", "Y-m-d", "m/d/Y", "m/d/yy");

    // Method 2: change date display and datepicker display (used for edit) to Spanish date
    $dg -> set_col_property("requiredDate",
                                 array("formatter"=>"date",
                                       "formatoptions"=>array("srcformat"=>"Y-m-d","newformat"=>"m/d/Y"),
                                       "editoptions"=>array(
                                            "dataInit"=>"function(el) {
                                                $(el).datepicker({
                                                    changeMonth: true,
                                                    changeYear: true,
                                                    dateFormat: 'm/d/yy'
                                                })
                                            }"
    )));

    // Display time only. No date. It cannot be edited, so we also made it hidden on the edit form.
    $dg -> set_col_property("shippedDate",
                array("formatter"=>"date",
                    "formatoptions"=>array("srcformat"=>"ISO8601Short","newformat"=>"g:i A"),
                    'editable'=>false,'hidedlg'=>true));

    // Display datetimepicker. Used when data is DATETIME type.
    $dg -> set_col_datetime("myDateTime");

    Change calendar datepicker locale

    Using method 2, add the following in datepicker in dataInit in editoptions, replace ‘es’ (Spanish) with locale of your choice.

    1
     $(el).datepicker( $.datepicker.regional[ 'es' ] )

    With above included, the entire snippet would be

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    $dg -> set_col_property("requiredDate",
                                 array("formatter"=>"date",
                                       "formatoptions"=>array("srcformat"=>"Y-m-d","newformat"=>"m/d/Y"),
                                       "editoptions"=>array(
                                            "dataInit"=>"function(el) {
                                               $(el).datepicker( $.datepicker.regional[ 'es' ] );
                                               $(el).datepicker({
                                                   changeMonth: true,
                                                   changeYear: true,
                                                   dateFormat: 'm/d/yy'
                                               })
                                           }")));

    Finally include script link to jQueryUI.

    1
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.1/i18n/jquery-ui-i18n.min.js" type="text/javascript"></script>
    Continue reading →
  • set_col_edit_dimension()
    • Parameters:
      • $col_name: column name
      • $size/$cols: If the column is a text box, it’s the size that represents number of characters. If the column is a textarea, it’s the value for cols attribute in textarea.
      • $rows: Optional. Number of rows in a textarea
    • Description:
      •  Set input text box size or textarea cols and rows values in edit form.
    • Remark:
    • Example:
    1
    2
    3
    4
    5
    // status is a text fields, 40 indicates the text field is 40 characters long.
    $dg -> set_col_edit_dimension("status", 40);

    // comments, a text data type, is rendered as textarea with cols set to 50, rows set to 10
    $dg -> set_col_edit_dimension("comments", 50, 10);
    Continue reading →
  • set_col_property()
    • Parameters:
    • Description:
      •  This method allows you to directly manipulate column properties.  Since column properties are an array, you can directly change the properties. This is more “closer to the metal” for users who are already familiar with jqGrid colMdel API.
    • Remark:
      • This is an advanced method. In most cases, you don’t need to call this method.  It does not replace existing helper functions such as set_col_date, set_col_currency, etc. We suggest you use those helper functions if you are new to phpGrid.
      • This method is also used to create custom formatter. See a custom formatter example using this method.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    $dg -> set_col_property("orderNumber", array("name"=>"Order Number", "width"=>40));
    $dg -> set_col_property("orderDate", 
                                 array("formatter"=>"date",
                                       "formatoptions"=>array("srcformat"=>"Y-m-d","newformat"=>"m/d/Y")));  // display different time format

    // Display time only. No date. It cannot be edited, so we also made it hidden on the edit form.
    $dg -> set_col_property("shippedDate",
                array("formatter"=>"date",
                    "formatoptions"=>array("srcformat"=>"ISO8601Short","newformat"=>"g:i A"),
                    'editable'=>false,'hidedlg'=>true));

    // currency format
    $dg -> set_col_format("quantityInStock", "integer", array("thousandsSeparator" => ",",
                                                              "defaultValue" => "0"));  
    $dg -> set_col_format("MSRP", 'currency', array("prefix" => "$",
                                                    "suffix" => '',
                                                    "thousandsSeparator" => ",",
                                                    "decimalSeparator" => ".",
                                                    "decimalPlaces" => 2,
                                                    "defaultValue" => '0.00'));

    Custom Formatter/Unformatter Example

    PHP

    1
    2
    3
    4
    5
    6
    7
    $dg -> set_col_property('rating',
            array("classes"=>'ratingcol',
                "sorttype"=>"number",
                "formatter"=>"###ratingformatter###",
                "unformat"=>"###unformathtml###"));

    ...

    JavaScript

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    // Formatter JavaScript
    function ratingformatter(cellValue, options, rowdata)
    {
        output = '';
        width = Math.max(2, 14 * Math.min(5, Math.max(0, cellValue)));
        output += '<div class="accRatingBar" style="width:' + width + 'px;"></div>';
        output += '<p class="accRatingText">' + cellValue + '</p>';
        output += "<!--" + cellValue + "-->";
        return output;
    }

    // The corresponding unformatter JavaScript function
    function unformathtml(cellValue, options, cellObject)
    {
        html = $(cellObject).html();
        quotestart = html.indexOf("<!--");
        if (quotestart == -1)
        return html;
        quoteend = html.indexOf("-->");
        output = html.substring(quotestart + 4, quoteend);
        return output;
    }
    Continue reading →
  • add_event()
    • Parameter(s):
      • $event_name: Event name from one of the following available events
          • jqGrid events

             

          • jqGridAfterAddRow
          • jqGridAfterChangeRowid
          • jqGridAfterDelRow
          • jqGridAfterInsertRow
          • jqGridAfterLoadComplete
          • jqGridAfterSetRow
          • jqGridAfterResizeDblClick
          • jqGridBeforeInitGrid
          • jqGridBeforeProcessing
          • jqGridBeforeRequest
          • jqGridBeforeSelectRow
          • jqGridGridComplete
          • jqGridLoadBeforeSend
          • jqGridLoadComplete
          • jqGridDblClickRow
          • jqGridHeaderClick
          • jqGridInitGrid
          • jqGridPaging
          • jqGridRightClickRow
          • jqGridSelectAll
          • jqGridSelectRow
          • jqGridShowHideCol
          • jqGridSortCol
          • jqGridRemapColumns
          • jqGridResizeDblClick
          • jqGridResizeStart
          • jqGridResizeStop
          • jqGridResetFrozenHeights
          •  

            Grouping event

             

          • jqGridGroupingClickGroup
          •  

            Form editing events

             

          • jqGridAddEditAfterClickPgButtons
          • jqGridAddEditAfterComplete
          • jqGridAddEditAfterShowForm
          • jqGridAddEditAfterSubmit
          • jqGridAddEditBeforeCheckValues
          • jqGridAddEditBeforeInitData
          • jqGridAddEditBeforeShowForm
          • jqGridAddEditBeforeSubmit
          • jqGridAddEditClickPgButtons
          • jqGridAddEditClickSubmit
          • jqGridAddEditInitializeForm
          • jqGridAddEditSerializeEditData
          •  

            Form view events

             

          • jqGridViewBeforeInitData
          • jqGridViewBeforeShowForm
          •  

            Form deleting events

             

          • jqGridDeleteAfterShowForm
          • jqGridAddEditAfterComplete
          • jqGridDeleteBeforeInitData
          • jqGridDeleteBeforeShowForm
          • jqGridAfterDelRow
          •  

            Searching form

             

          • jqGridFilterAfterChange
          • jqGridFilterAfterShow
          • jqGridFilterInitialize
          • jqGridFilterBeforeShow
          •  

            Inline editing events

             

          • jqGridInlineBeforeAddRow
          • jqGridInlineBeforeCancelRow
          • jqGridInlineBeforeEditRow
          • jqGridInlineBeforeSaveRow
          • jqGridInlineAfterRestoreRow
          • jqGridInlineEditRow
          • jqGridInlineSaveRowValidation
          • jqGridInlineAfterSaveRow
          • jqGridInlineSerializeSaveData
          • jqGridInlineSuccessSaveRow
          • jqGridInlineErrorSaveRow
          •  

            Cell editing events

             

          • jqGridBeforeEditCell
          • jqGridAfterEditCell
          •  

            Subgrid events

             

          • jqGridSerializeSubGridData
          • jqGridSubGridBeforeCollapse
          • jqGridSubGridBeforeExpand
          • jqGridSubGridRowExpanded
      • $js_event_handler: JavaScript event handler to the specified event
    • Description:
    • Remark:
      • This is an advanced method.  The event handlers are essentially JavaScript.
      • Native javascript events are always supported.
    • Example:

    You can also add event handler directly, even add additional event handler. The following adds dropdown ‘change’ event handler when the edit form shows.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <script type="text/javascript">
    $(function() {
            var grid = jQuery("#orders");

            grid.bind('jqGridAddEditAfterShowForm', () => {
                document.getElementById('status')?.addEventListener('change',
                    () =>  console.log(this)
                );
            })
        });
    </script>
    Continue reading →
  • display_script_includeonce()
    • Parameter(s):
      • None
    • Description:
      • Includes required Javascript libraries before displaying our grids.
    • Remark:
      • Developers don’t need to call this method to include required Javascript libraries (jqGrid, jwysiwyg, ajaxfilupload etc.) because the phpGrid includes those Javascript automatically for you. This method is only used in get_display() in a MVC framework.
    Continue reading →
  • add_column()
    • Parameter(s):
      • $col_name: Name of the calculated/virtual column. It cannot have space and must NOT be one of the existing database column names.
      • $property: Column properties. See set_col_property() for available column properties usage.
      • $title: Optional. Title for this virtual column. If omitted, it’s the same as the column name $col_name.
    • Description:
      • Append virtual column, AKA calculated column, to the end of an existing datagrid with this method.
    • Remark:
      • The $col_name cannot contain space and must begin with a letter
      • Use “formatter” column property to hook up Javascript function, e.g. below, $col_formatter is the Javascript to display value in virtual column.
      • The virtual column always adds to the end of the grid in the order of virtual column is created.
      • Text must be surrounded with SINGLE quote.
      • Virtual column is not sortable.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    $col_formatter = <<<COLFORMATTER
    function(cellvalue, options, rowObject){
    var n1 = parseInt(rowObject[0],10),    // get value from column #1
    n2 = parseInt(rowObject[6],10);        // get value from column #7
    return n1+n2;    
    }
    COLFORMATTER
    ;

    $dg -> add_column(
            'total',
            array('name'=>'total',
                'index'=>'total',
                'width'=>'360',
                'align'=>'right',
                'formatter'=>$col_formatter),
            'Total (Virtual)');
    Continue reading →
  • set_col_customrule()
    • Parameters:
      • $col_name: column name
      • $custom rule: Javascript function to validate data
    • Description:
      • Create custom javascript validation for column value during edit. phpGrid has automatic default validation based on database data type. This method comes in handy when additional data validation is required such as value range, data dependency etc.
    • Example:
    1
    2
    // price_validation1 is a javascript function. See related associate live example for complete implementation
    $dg->set_col_customrule('quantityInStock', 'price_validation1'); 
    Continue reading →
  • set_col_frozen()
    • Parameter(s):
      • $col_name: Column name
    • Description:
      • Similar to Excel column freeze, phpGrid can now freeze column(s)
    • Remark:
      • Note that the frozen columns should be from left to right, one after other.
      • In inline edit, the frozen columns are not editable.
      • Columns are not draggable if any column is frozen
    • Example:
    1
    2
    // freeze both customer_id and customer_name columns
    $dg -> set_col_frozen("customer_id")->set_col_frozen("customer_name"):
    Continue reading →
  • set_grid_method()
    • Parameter(s):
      • parameter: A variable argument. Variable argument makes the function more flexible because different jqGrid methods will have different set of parameters. Super useful.
    • Description:
    • Remark:
      • Available in 5.5.5 and higher.
      • Starting version 6, the parameter becomes a single variable argument.
      • Only use this method to call jqGrid methods that can act on the grid as a whole. It’s NOT possible to manipulate the grid on a row or cell level using set_grid_method because the generated javascript code is appended to the end of the script before closing bracket.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    // create group header using this method
    $dg->set_grid_method('setGroupHeaders', array(
                        array('useColSpanStyle'=>true),
                        'groupHeaders'=>array(
                            array('startColumnName'=>'customerNumber',
                                'numberOfColumns'=>2,
                                'titleText'=>'Numbers Header') )));
    // inline edit add new row
    $dg->set_grid_method('inlineNav',
                    '#'. $this->jq_gridName .'_pager1',
                    array('addParams' => array(
                            'position'=> "last",
                            'addRowParams'=>array(
                            'keys'=>true))));
    Continue reading →
  • odbc_mssql

    “odbc_mssql” should only be used for DSN connection for Microsoft SQL Server in *nix or OS X operation system using unixODBC through FreeTDS protocol. You can learn more about how to use unixODBC with FreeTDS here

    unixODBC

    unixODBC requires additional environment variables in file conf.php

    1
    2
    putenv("ODBCINSTINI=/usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini");
    putenv("ODBCINI=/usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini");
     

    FreeTDS

    FreeTDS uses a configuration file called “freetds.conf”. Below is an sample of freetds.conf used by phpGrid during testing for your reference.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    /usr/local/Cellar/freetds/0.91/etc/freetds.conf

       # server specific section
        [global]
        # TDS protocol version
        ; tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        ; dump file = /tmp/freetds.log
        ; debug flags = 0xffff

        # Command and connection timeouts
        ; timeout = 10
        ; connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512

        [phpgridmssql]
        host = phpgridmssql.cbdlprkhjrmd.us-west-1.rds.amazonaws.com
        port = 1433
        tds version = 7.0
       /usr/local/Cellar/unixodbc/2.3.1/etc/odbc.ini

       [phpgridmssql]
        Description = MS SQL Server
        Driver = FreeTDS
        Server = phpgridmssql.cbdlprkhjrmd.us-west-1.rds.amazonaws.com
        TraceFile = /tmp/sql.log
        UID = mssqluser
        PWD = PASSWORD
        ReadOnly = No
        Port = 1433
       Database = sampledb
       /usr/local/Cellar/unixodbc/2.3.1/etc/odbcinst.ini

       [FreeTDS]
        Description = FreeTDS
        Driver = /usr/local/Cellar/freetds/0.91/lib/libtdsodbc.so
        Setup = /usr/local/Cellar/freetds/0.91/lib/libtdsodbc.so
        FileUsage = 1
        CPTimeout =
        CPResuse =
        client charset = utf-8
    Continue reading →
  • before_script_end

    before_script_end is not a function but a variable. It allows you to inject custom javascript before the end of jqGrid object closing script tag so all DOM elements are presented. This is considered an advanced way to manipulate your grid.

    The following code snippet demonstrates using this variable to insert a javascript that creates a export dropdown menu on the bottom toolbar.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    $dg3 = new C_DataGrid("SELECT * FROM products", "productCode", "products");

    $exportDropdown =<<< EXPORTDROPDOWN
    $('#products_pager1_left').append ('<div style=padding-right: 16px;>Export:\
        <select onchange=document.location.href=this.options[this.selectedIndex].value;>\
            <option>---</option>\
            <option value=/phpGridx/export.php?gn=products&export_type=excel>Excel</option>\
            <option value=/phpGridx/export.php?gn=products&export_type=pdf>PDF</option>\
            <option value=/phpGridx/export.php?gn=products&export_type=html>HTML</option>\
            <option value=/phpGridx/export.php?gn=products&export_type=csv>CSV</option>\
        </select></div>');
    EXPORTDROPDOWN;

    $dg->before_script_end = $exportDropdown;
    $dg->display();
    Continue reading →
  • $cust_prop_jsonstr

    $custo_prop_jsonstr is a variable, not a function. The variable type is JSONString (string in JSON format).

    The difference between $cust_prop_jsonstr and set_grid_property() is that the later is a function and must take an array as parameter. On the other hand, $cust_prop_jsonstr gives user the power to inject string directly into the jQgrid properties.

    Which one to use?

    The rule of thumb is to use $cust_prop_jsonstr when the property is form of string; else use set_grid_property() function. In general, you want to use set_grid_property().

    Remark:

    This is an advanced variable. In most cases, you do not need to use it unless you are already very familiar with jQgrid ins and outs that you want to manipulate your grid properties like a code ninja. :)

    Example:

    1
    2
    3
    4
    5
    // Here filters must be passed as string to data.php later sent through POST. It's not possible to use set_grid_property function which must take array params
    $dg->cust_prop_jsonstr .= 'postData: {filters:
                                \'{"groupOp":"AND","rules":[{"field":"status","op":"eq","data":"Shipped"}]}\'},'
    ;
    // add toolbar to top of the grid.
    $dg->cust_prop_jsonstr .= 'toppager:true,';
    Continue reading →
  • set_grid_property()
    • Parameters:
    • Description:
      •  Advanced method that  sets datagrid properties. The parameter must be an array. You must be rather familiar jqGrid API in order to take advantage of this method effectively. In most cases, you do not need to use this method.
    • Remark:
      • This method is completely difference from set_col_property() method that sets column property, a subset of grid property.
      • This is an advanced method. In most cases, you don’t need to call this method.
    • Example:
    1
    2
    3
    $dg -> set_grid_property(array('search'=>true));
    $dg -> set_grid_property(array('direction'=>'rtl'));
    $dg -> set_grid_property(array("altclass" => "alternatie_row_css_class"));

    custo_prop_jsonstr or set_grid_property?

    The difference between cust_prop_jsonstr and set_grid_property is that the later is a function and must take an array as parameter but cust_prop_jsonstr takes string parameter.

    Which one to use?

    The rule of thumb is to use cust_prop_jsonstr when the property is form of string; else use set_grid_property() function. In general, you want to use set_grid_property().

    Continue reading →
  • set_edit_condition()
    • Parameters:
      • $edit_conditoin: An array stores edit permission condition.
    • Description:
      • Set row-level permission condition for editing.
    • Remark:
      • This works for INLINE ONLY by hiding the edit icons using javascript, CSS
      • For that reason, developers should still validate user permission at the database or on the server side.
      • For complex conditions, use add_event and javascript. See row-level edit permission example.
    • Example:
    1
    2
    $dg -> enable_edit('INLINE', 'CRUD');
    $dg -> set_edit_condition(array('status' => '=="Shipped"', '&&', 'customerNumber' => '==114' ));

    The above code snippet generates javascript that validates Status and customerNumber value. If both are true, the edit icon will appear.

    phpGrid_Custom_Edit_Condition_without_using_set_edit_condition

    Continue reading →
  • DEBUG global constant

    Note: DEBUG global constant is only available starting version 6. For phpGrid version 5.5.5 and lower, see enable_debug() method please.

    phpGrid version 6 introduces a new global constant named “DEBUG” in conf.php. When set to true, it turns on the debug mode. When not defined, DEBUG value defaults to false.

    When DEBUG set to true:

    • When set to true, it displays phpGrid object as well as the current PHP session information. In addition, AJAX response data are also displayed 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.
    1
    2
    // In conf.php
    define('DEBUG', true);
    Continue reading →
  • THEME global constant

    Available starting phpGrid version 6, you can set a global constant “THEME” in conf.php to change all of your datagrid themes without calling set_theme() method individually.

    1
    2
    // set a global theme in conf.php (version 6+)
    define('THEME', 'bootstrap');

    Both Bootstrap 3 and 4 (7.4.5+) themes are now supported with ‘bootstrap‘ or ‘bootstrap4

    The premium themes are currently available in phpGrid commercial licenses.

    Continue reading →
  • set_sortablerow()
    • Parameters:
      • $sortable: true or false
    • Description:
      • Activate sortable row. Drag and drop row to sort.
    • Remark:
      • The sort does not persist. The sort is lost the grid or the page refreshes.
    • Example:
    1
    $dg -> set_sortablerow(true);
    Continue reading →
  • enable_columnchooser()
    • Parameters:
      • $enable: true or false
    • Description:
      • Enable column chooser.
    • Remark:
      • The column chooser does not persist. The columns displayed resets whenever grid or the page refreshes.
    • Example:
    1
    $dg -> enable_columnchooser(true);
    Continue reading →
  • enable_autoheight()
    • Parameters:
      • $autoheight: true or false. Default to false.
    • Description:
      • Use this method to set datagrid height as the same as the current window height.
    • Remark:
      • Do not use this method when multiple grids present on a single page
    • Example:
    1
    $dg -> enable_autoheight(true);
    Continue reading →
  • enable_global_search() *

    * Please note this feature is only available in commercial  edition.

    • Parameters:
      • $enable: true or false
    • Description:
      • Enables multi-fields global search on any searchable columns. When set to true, search can perform in one or more searchable columns. Multiple search terms are separated by space.
    • Example:
    1
    $dg -> enable_global_search(true);
    Continue reading →
  • set_pivotgrid()

    * Pivot grid is only available in commercial license 6.6+. 

    • Parameter(s):
      • $configuration: Pivot configuration array parameter. Please see pivot demo for more information.
    • Description:
      • Create pivot grid from an existing datagrid. It renders the regular datagrid first, and then pivots based on configuration settings. In essence, phpGrid creates pivot in the client side browser without requiring any server side resource.
    • Remark:
      • You will first see a loading icon while pivot is rendered before it is displayed. It’s by design.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    $dg->set_pivotgrid(
            array
            (
                "footerTotals" => 1,
                "footerAggregator" => "sum",
                "totals" => 1,
                "totalHeader" => "Grand Total",
                "totalText" => "Grand {0} {1}",
                "xDimension" => array
            (
            array
            (
                "dataName" => "status",
                "label" => "status",
                "sortorder" => "desc"
            ),
            array
            (
                "dataName" => "ProductName",
                "label" => "Product Name",
                "footerText" => "Total:"
            )

        ),

        "yDimension" => array
            (
                array
                (
                    "dataName" => "orderDate",
                    "sorttype" => "date",
                    "totalHeader" => "Total in {0}"
                )

            ),

        "aggregates" => array
            (
                array
                (
                    "member" => "status",
                    "aggregator" => "count",
                    "summaryType" => "count",
                    "label" => "{1}"
                )

            )
        )
    );
    Continue reading →
  • set_col_headerTooltip()
    • Parameter(s):
      • $col_name: Column name.
      • $tooltip: Tooltip text.
    • Description:
      • Set column tooltip
    • Example:

     

    1
    $dg -> set_col_headerTooltip('my_column', 'tooltip text');
    Continue reading →
  • load_form()

    * Paid version only.  Supported from version 7.0 and above.

    • Parameter(s):
      • $key: A database table primary key value.
    • Description:
      • Load an existing record from DB and display in edit form without the datagrid. The form will remain on the screen after submit.
    • Remark:
    • Example:
    1
    $dg -> enable_edit('FORM') -> form_only() -> load_form(8888);
    Continue reading →
  • redirect_after_submit()
    • Parameter(s):
      • $url: Redirect URL
    • Description:
      • Redirect form after submit.
    • Remarks:
      • It does not validate whether the submit is successful.
    • Example:
    1
    $dg -> redirect_after_submit('http://php.net')
    Continue reading →
  • add_form_tooltip()
    • Parameter(s):
      • $col_name: Name of column name for the tooltip
      • $tooltip_text: Tool tip text
      • $symbol: Tooltip symbol. Default to (?) with CSS class name “tooltip_sym”
    • Description:
      • Display tool tip for a specified column in modal form. The default tooltip CSS class name is “tooltip_sym”.
    • Example:
    1
    $dg -> add_form_tooltip('email', 'Got mail?');
    Continue reading →
  • add_form_group_header()
    • Parameter(s):
      • $before_col_name: Name of column name before which display group header text
      • $text: Group header text
    • Description:
      • Display text before specified column in modal form.
    • Example:
    1
    $dg -> add_form_group_header('employeeNumber', 'Employ Details');
    Continue reading →
  • form_only() *

    * Please note Form-only mode is only available in paid versions. It is supported from version 7.0 and above.

    • Parameter(s):
      • none
    • Description:
      • Display the add form only without the datagrid. The form will remain on the screen and reset after each successful submit.
    • Remark:
      • Must call enable_edit(‘FORM’) prior to form_only().
    • Example:
    1
    $dg -> enable_edit('FORM') -> form_only();
    Continue reading →
  • enable_pagecount()
    * This function is available in commercial licenses.
    • Parameters:
      • $page_count: boolean: true or false.
    • Description:
      • Enable page count in the pager. Default to true. Set to false to optimize performance for very large (e.g. millions) database table.
    • Example:

     

    1
    $dg -> enable_pagecount(false);
    Continue reading →
  • set_col_time() *

    * Note that currently it is not supported in the Lite and Basic edition.

    Set the edit type to be time and enable the time-picker

    1
    $dg -> set_col_time("time_from");

    It is recommended to also use the formatter function so that the time is set in the right value during the edit.

    1
    2
    $pg->set_col_property("time_from",
                array("formatter"=>"date", "formatoptions"=>array("srcformat"=>"h:i A","newformat"=>"h:i A")))
    Continue reading →
  • set_col_datetime()

    * Currently only supported in the commercial licenses 

    The function is almost identical to set_col_date() except it also has a time picker.

    • Parameters:
      • $col_name: column name
      • $srcformat: source date format, eg. “Y-m-d”
      • $newformat: new date form to be displayed, eg. “m/d/Y”
      • $datepicker_format: datepicker format, eg. “m/d/yy”
    • Description:
      •  Helper function to format the date using PHP date format options (http://php.net/manual/en/function.date.php). MySql always stores the date in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. However, we can still change the front-end date display format while conforming to its date standards.
    • Remark:
      • In most cases, you don’t need to call this method because phpGrid automatically formats the data with corresponding data type including the date.
      • Only use this method in order to display date format that is different from default date format.
      • To display time only, please use set_col_property instead.
    • Example:
    1
    2
    // Display datetimepicker. Used when data is DATETIME type.
    $dg -> set_col_datetime("myDateTime");
    Continue reading →
  • enable_dnd_grouping()

    * Please note this feature is only available in commercial licenses.

    • Parameter(s):
      • $dnd_grouping: boolean to enable/disable drag and drop grouping
    • Description:
      • Enable on the fly drag and drop grouping
    • Example:
    1
    $dg -> enable_dnd_grouping(true);
    Continue reading →
  • set_grid_style()

    * Currently only supported in the commercial licenses 

    set_grid_style() is a helper function for simple style update for header background, foreground, its font size, and body font size.

    • Parameters:
      • $parameter 1: header background color
      • $parameter 2: header foreground color
      • $parameter 3: header font size
      • $parameter 4: body font size
    • Description:
      • helper function for simple style update for header background, foreground, its font size, and body font size.
    • Remark:
      • This function is intended for users who are only new to CSS to provide very basic color and font style update.
      • To further customize the grid, it is recommended to use an alternative theme or simply use CSS.
    • Example:
    1
    $dg->set_grid_style('#ff2288', 'white', '16px', '13px');
    Continue reading →
  • set_ws_editurl()*

    * Available in commercial versions for IBM i DB2 only

    Details on IBM i RESTful Web Services

    • Parameter(s):
      • $edit_url: IBM i web service URL for edit
      • $delete_url: URL to IBM i web service for delete
    • Description:
      • Integrated Web Services for IBM i Web services for building IBM i applications. This function sets URL for both edit and delete to web services.
    • Example:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    // file_get_contents() context options
    $arrContextOptions=array(
      "ssl"=>array(
        "verify_peer"=>false,
        "verify_peer_name"=>false,
      ),
    );

    // fetch data from API
    $url = "https://example.com:440/web/services/GetAllCustomers/";
    $json = file_get_contents($url, false, stream_context_create($arrContextOptions));
    $json_output = json_decode($json, true);

    // load grid
    $dg = new C_DataGrid($json_output['CUSTOMER_IORcdsDS'], "CUSTNO", "CUSTOMER_IORcdsDS");

    $dg->enable_edit('FORM');

    // set WS edit end point (edit, delete)
    $dg->set_ws_editurl(
      'https://example.com:440/web/services/UpdateCustomer/updtcustno',
      'https://example.com:440/web/services/DeleteCustomer/dltcust'
    );

    $dg->display();
    Continue reading →
  • composer install common issues

    Since introduction of composer install in version 7.5.3, it has been an excellent way for dependencies management while minimizing file distribution size. Users can keep dependencies updated by running composer update without reaching out to us, a win-win.

    This post will document composer-related common issues reported by our users and remedies to address those issues.

    PHPExcel (deprecated) has now been replaced by PHPSpreadsheet. It requires two PHP extensions, depending versions of PHP, sometime they are not enabled by default. If they are missing, you are like to encounter the following error messages during composer install.

    1
    2
    - Root composer.json requires phpoffice/phpspreadsheet ^1.29 -> satisfiable by phpoffice/phpspreadsheet[1.29.0].
    - phpoffice/phpspreadsheet 1.29.0 requires ext-gd * -> it is missing from your system. Install or enable PHP's gd extension.

    To enable extensions, verify that the following are enabled in your php.ini files:

    – extension=gd
    – extension=zip

    You can also run `php –ini` in a terminal to see which files are used by PHP in CLI mode. Alternatively, but not recommended, you can run Composer with –ignore-platform-req=ext-gd to temporarily ignore these required extensions if you are sure they are already enabled.

    Remember always to restart web server after making any changes in php.ini.

    Continue reading →