Is there a free PHP Wrapper or backend script for a JQUERY Grid Control (JQGRID or Datatables)

Question

I am looking to implement a best-practice JQUERY grid control, with a PHP backend. I want it to be able to support large datasets, and inline cell editing.

I am struggling to find a PHP backend script which is integrated with the control and abstracts away the complexities of this stuff. I want to be able to implement a grid based on an underlying MYSQL table with a minimum of configuration. Really I can't be the only one who wants to be able to specify basic database details and table/sql and have an operating grid.

• JQGrid has a paid PHP script backend you can use which sounds like what I want...only its expensive. http://www.trirand.net/documentation/php/index.htm

• There's also a cheaper thing trying to do the same thing, but it doesn't work. http://azgtech.wordpress.com/2010/08/01/jqgrid-php-datagrid/

• I've also looked at DataTables, but once you add the inline-editing capabilities, developing the PHP backend started becoming its own project.

I have spend ages researching this, and mucking about with various solutions.

Does anybody know of a powerful, free, easy to use solution which has a complete PHP backend script?

I've included some of the description of the paid JQGrid PHP control, which contains the kind of functionality I'm looking for.

JQGRid PHP
This is a brand new product created for PHP developers and teams that radically decreases development time with jqGrid and makes it fun and easy. The product will be offered with commercial licenses that will include fast and accurate technical support, source code and subscription based benefits.
Highlight points include:
- all functionality jqGrid has is supported
- no need for tricky javascript – everything is handled in PHP
- the PHP component automatically handles data retrieval, paging, sorting, searching and all CRUD operations (create, read, update, delete). No need for custom code.
- you can have a fully functional grid with just a few lines of PHP
- export to Excel is supported
- database engines supported: MySql, Postgre SQL, Microsoft SQL server

Answer 1

Have you checked out phpGrid. It's a PHP jqgrid wrapper. It just two lines of code to get started. Is that something you are looking for?

$dg = new C_DataGrid(“SELECT * FROM orders”, “orderNumber”, “orders”);
$dg -> display();

Answer 2

I've just found a github project which seems to be really great for this: https://github.com/lampjunkie/php-datatables

It provides a wrapper for Jquery Datatables plugin, handles the initial setup and the ajaq data feeds also. It has an object-oriented approach and seems to be very logically designed. You also can find an example project in the package.

Answer 3

I wrote this small library a while back as a a booze filled weekend project a while ago, it's a bit messy but it'll provide some of the default back end functionality that you're looking for.]

    <?php
/*
Grid.php
Lirbary of function for the translation of Array() objects into json
*/
/*--- INCLUDE DEPENDENCIES ---*/

include_once "lib/sql.php";

/*--- HEADER UPDATE ---*/
header("Content-type: application/json");

/*--- GLOBALS ---*/

/*Default sort order*/
define('GRID_DEFAULT_SORT_ORDER', "ASC");

/*Default page index*/
define('GRID_DEFAULT_PAGE', 0);

/*Default results per page*/
define('GRID_DEFAULT_RP', 15);

/*--- FUNCTIONS ---*/

/*
Build Grid Queries
desc:    Build the queries used to provide the grid results
params:
    $select: 2D associative array
        field:         name of field 
        alias:         field alias
    $from: table data is to be selected from
return:    An array containing the query to select entries and the query to get the result count
*/
function buildGridQueries($select, $from)
{
    /*Create select statement*/
    $selectStr = "SELECT ";
    for($i = 0; $i < count($select);$i++){

        /*append field name to str*/
        $selectStr .= $select[$i]['field'];

        /*If an alias has provided*/
        if(isset($select[$i]['alias']))
            $selectStr .= " AS '". $select[$i]['alias']. "'";

        /*if current field is not the last */
        if($i < count($select) - 1)
            $selectStr .= ", ";
    }

    /*Create from statement*/
    $fromStr = " FROM $from";

    /*Set sort by value by $_POST value if available or by $select[0][field]*/
    $sortStr = " ORDER BY ";
    if(isset($_POST['sortname']))
        if($_POST['sortname'] == "undefined")
            $sortStr .= $select[0]['field'];
        else
            $sortStr .= $_POST['sortname'];
    else
        $sortStr .= $select[0]['field'];

    /*Set sort order by $_POST if available or by ASC*/
    if(isset($_POST['sortorder']))
        $sortStr .= " ". $_POST['sortorder'];
    else
        $sortStr .= " ". GRID_DEFAULT_SORT_ORDER;

    /*Set query conditional WHERE statement if passed*/
    $queryStr = "";
    if(isset($_POST['qtype'])&&isset($_POST['query']))
        if($_POST['query']!= "")
            $queryStr .= " WHERE ". $_POST['qtype']. " LIKE '%" . $_POST['query']. "%' ";

    /*Create limit statement by passed values or by defaults*/
    $limitStr = " LIMIT ";
    if(isset($_POST['page']))
        if($_POST['rp'])
            $limitStr .= ($_POST['page'] - 1) * $_POST['rp']  . ",". $_POST['rp'];
        else
            $limitStr .= $_POST['page'] . ", ". GRID_DEFAULT_RP;
    else
        $limitStr .= GRID_DEFAULT_PAGE. ", ". GRID_DEFAULT_RP;


    /*return queries array*/
    return Array("query" => $selectStr. $fromStr. $queryStr. $sortStr. $limitStr,
                 "count" => "SELECT COUNT(id) AS 'total'  $fromStr $queryStr ");
}

/*
Commit Data
desc:    Commit data edits (Passed by a client side flexigrid object
params:
    $table:     table name
    $rows:         rows array of data to be committed
    $index:        Field name of index column, used in where statement
return: An array of update results for each passed row;
*/
function commitGridData($table,$rows,$indexField, $sqlConnection)
{

    /*Declare return array*/
    $return = Array();

    /*With every row which is to be committed*/
    foreach($rows as $row){


        /*Create update statement base and iterate through cells*/
        $statement = "UPDATE $table SET ";
        for($i = 0;$i<count($row['fields']);$i++){

            /*If value is a string check to see if it's a date*/ 
            if(!is_numeric( $row['fields'][$i]['value'])){

                /*Encapsulate str it isn't a date, convert to time if it is*/
                $val = "'".  $row['fields'][$i]['value']. "'";
            }else
                $val =  $row['fields'][$i]['value'];

            /*Append field name and value to statement*/
            $statement .= $row['fields'][$i]['field'] . "=". $val;

            /*Append delimiter to the statement if this cell is not the last in $orw*/
            if($i<count($row['fields']) - 1)
                $statement .= ", ";
        }

        if($row['entryIndex'] < 0)
            $row['entryIndex'] = mysqlCreateEntry($table, $sqlConnection);


        /*Append where statement*/
        $statement .= " WHERE $indexField = ". $row['entryIndex'];

        /*Update row information*/
        $return[] = Array("id" => $row['tableId'], "success" => mysqlQuery($statement, $sqlConnection), "error" => mysql_error());
    }

    /*Return result set*/
    return $return;
}
/*
Generate Grid Array
desc:    generate Array object which is compatible with FlexiGrid when it is json encoded
params:
    $queries:    Queries for retrieving data entries and result set size
    $fields:     2D associative array  or false to prevent inline  generation
        field:         name of field 
        alias:         field alias
        $sql:         An Sql connection identifier
return:    An array of FlexiGrid properties
*/
function generateGridArray($queries, $fields, $sqlConnection)
{
    /*Get the total number of results matching the search query*/
    $res = mysqlQuery($queries['count'], $sqlConnection);
    $total = mysql_fetch_assoc($res);

    /*Get matching result set*/
    $res = mysqlQuery($queries['query'], $sqlConnection);

    /*Create result FlexGrid-compatible Array*/
    $data =Array( 
        "page" => (isset($_POST['page']))? $_POST['page']: 1, 
        "total" => $total['total'], 
        "width" => 500,
        "height" => (isset($_POST['rp']))? $_POST['rp']: mysql_num_rows($res) * 20 + 45,
        "title" => " ",
        "propertyCount" => count($fields),
        "rows" => sqlToGridRows($fields, $res));

    /*If initial request (no $_POST  equals passive data collection by the client side*/
    if(count($_POST) < 1 ){
        $data['colModel'] =  sqlToGridColModel($fields, $res);
        $data['searchitems'] =  arrayToGridSearchItems($fields);
    }

    /*Return*/
    return $data;
}
function sqlToGridRows($fields, $sqlResult)
{
    /*Identify the entry index column*/
    $fieldTypes = Array();
    foreach($fields as $field){

        /*if the field  is the entry index*/
        if(isset($field['entryIndex']))

            /*Set field as entryIndex*/
            $entryIndexCol = (isset($field['alias']))?  $field['alias']: $field['field'];
    }

    /*Iterate through result set*/
    $return = Array();
    for($i = 0;$i < mysql_num_rows($sqlResult);$i++){

        /*Get entry data*/
        $row = mysql_fetch_assoc($sqlResult);

        /*modify values based on fieldType*/
        foreach($fields as $field){

            /*If the fieldType value is set, no changes otherwise*/
            if(isset($field['fieldType'])){

                /*Field type specific formating*/
                switch ($field['fieldType']){
                    /*Format field as a date*/
                    case "date":

                        /*Update by either field label if the label key exists in row or use alias*/
                        if(isset($row['field']))
                            $row[$field['field']] = date("d/m/Y", $row[$field['field']]);
                        else
                            $row[$field['alias']] = date("d/m/Y", $row[$field['alias']]);
                    break;
                    case "time":
                        if(isset($row['field']))
                            $row[$field['field']] = date("H:i", $row[$field['field']]);
                        else
                            $row[$field['alias']] = date("H:i", $row[$field['alias']]);
                    break;
                }
            }
        }
        /*if the entry index column was identified*/
        if(isset($entryIndexCol)){

            /*Set entryIndex value*/
            $entryIndex = $row[$entryIndexCol];

            /*remove the entryIndexCol from the row*/
            unset($row[$entryIndexCol]);
        }else

            /*Set the entry index as the default*/
            $entryIndex = $i;

        /*Create entry*/
        $entry = Array("id" => $i, 
                        "entryIndex" => $entryIndex,
                       "cell" => $row);

        /*iterate $fields and replace aliased keys with field names*/
        for($m = 0;$m < count($fields);$m++){

            /*if field has an alias update the  key value*/
            if(isset($fields[$m]['alias'])){

                /*Replace and rebuild field->cell*/
                $cell = Array();
                foreach($entry['cell'] as $key => $val){

                    /*if culprit cell change key*/
                    if($key == $fields[$m]['alias'])
                        $cell[$fields[$m]['field']] = $val;
                    else
                        $cell[$key] = $val;
                }

                /*Update entry->cell value*/
                $entry['cell'] = $cell;
            }
        }

        /*Map sql result to grid table row structure*/
        $return[] = $entry;
    }

    /*Return grid-ready array*/
    return $return;
}

function sqlToGridColModel($fields, $sqlResult)
{
    /*Iterate through result set*/
    $return = Array();
    for($i = 0;$i < mysql_num_fields($sqlResult);$i++){

        /*Replace aliased cell name attributes with associted field name*/
        $alias = mysql_field_name($sqlResult, $i);
        $name = false;
        $isEntryIndex = false;
        for($m = 0;$m < count($fields);$m++){

            /*If current field has an alias which equals $name, replace name with $field[[$m]['field']*/ 
            if(isset($fields[$m]['alias'])){

                /*if field has an alias*/
                if($fields[$m]['alias'] == $alias){
                    $name = $fields[$m]['field'];
                }
            }else{
                if($fields[$m]['field'] == $alias){
                    $name = $fields[$m]['field'];
                }
            }

            /*Add field data etc*/
            $fieldData = false;
            if(isset($fields[$m]['fieldType'])){

                /*Get field type*/
                $fieldType = $fields[$m]['fieldType'];

                /*Attach select options to field if available*/
                if($fieldType == "select")

                    /*Set default field type*/
                    $fieldData = $fields[$m]['fieldData'];                
            }else
                $fieldType = "input";

            /*If the field is the entry index flag it for exclusion*/
            if($name){

                /*If the field is the entry index*/
                if(isset($fields[$m]['entryIndex']))
                    $isEntryIndex = true;

                /*Exit for loop*/
                $m = count($fields);
            }
        }
        /*If no name was set (alias is also name)*/
        if(!$name)
            $name = $alias;

        /*If the field is to be included in the column model*/
        if($isEntryIndex == false){

            /*Append column data to return*/
            $return[] = Array("display" => $alias, "name" => $name, 
                         "width" => 200, "sortable" => "true", 
                         "fieldType" => $fieldType, "fieldData" => $fieldData);    
        }
    }

    /*Return grid-ready array*/
    return $return;
}

function arrayToGridSearchItems($fields)
{

    /*iterate fields*/
    $return = Array();
    for($i = 0;$i < count($fields);$i++){

        /*if field has an alias use it for the display name*/
        $alias = (isset($fields[$i]['alias']))? $fields[$i]['alias']: $fields[$i]['field'];

        /*If field is not the entry index*/
        if(!isset($fields[$i]['entryIndex']))

            /*Create searchitem and append to return*/
            $return[] = Array("display" => $alias, 
                            "name" => $fields[$i]['field'], 
                            "isdefault" => ($i == 0)? "true": "false");
    }

    /*return*/
    return $return;
}


?>

This is designed to allow the development of Grid data tables using a standard template, Below is an example template.

<?php

/*include grid lib*/
include "lib/grid.php";

/*Create sql connection*/
$sqlConnection = mysqlConnect($_USER->sqlUser, $_USER->sqlPass);
/*----------------------*/


/*--- Create fieldData ---*/
$userTypes = Array(
    Array("value" =>0, "text" =>  "Staff"),
Array("value" => 1, "text" => "Manager"));
/*----------------------*/


/*---
    Define selection array
        Create field selection and rules Array. Defines output.
---*/
$array = Array();
$array[] = Array("field" => "id", "entryIndex" => true);            /*Entry index is the Sql row id, isn't show in table but is used for commits*/
$array[] = Array("field" => "username", "alias" => "User Name");
$array[] = Array("field" => "name", "alias" => "Name");
$array[] = Array("field" => "address", "alias" => "Address");
$array[] = Array("field" => "postcode", "alias" => "Postcode");
$array[] = Array("field" => "tel", "alias" => "Telephone");
$array[] = Array("field" => "mobile", "alias" => "Mobile");
$array[] = Array("field" => "email", "alias" => "Email Address");
$array[] = Array("field" => "user_type", "alias" => "User Type", "fieldType" => "select", "fieldData" => $userTypes);

$table = "staff";

/*---
    Commit data template
        Inlcude a the following if 
---*/
/*If an action is to be carried out prior to data load*/
if(isset($_GET['method'])){

    /*If transaction is a data commit*/
    if($_GET['method'] == "commit"){

        /*Check that edit requests were sent*/
        if(isset($_POST['rows'])){

            /*Pre-update validation*/
            foreach($_POST['rows'] as &$row){
                /*Any preprocessing for entries prior to commit*/
            }
            echo json_encode(commitGridData($table, $_POST['rows'], "id", $sqlConnection));
            exit;
        }
    }
}


/*Buildd queries - Opportunity to debug queries, alternatively pass to generateGridArray*/
$queryStrs = buildGridQueries($array, "staff");

/*Generate grid data*/
$resArray = generateGridArray($queryStrs, $array, $sqlConnection);

/*Pre grid build extend and/or alter settings*/
if(count($_POST) < 1){
    $resArray['buttons'] = Array(Array("name" => "Add", "bclass" => "add", "onpress" => "add"));
    $resArray['title'] = "Staff Details";
    $resArray['editable'] = true;
}
echo json_encode($resArray);
exit;

?>

I've extended Flexgrid to accommodate for field formatting, committing data and adding field events but I can't for the life of me find it. I'll post it if I do.

Disclaimer: $_POST is used recklessly throughout grid.php. It is recommended that it's replaced with something more fitting.