是否有用于 JQUERY 网格控件（JQGRID 或数据表）的免费 PHP 包装器或后端脚本

Question

我希望实现一个带有 PHP 后端的最佳实践 JQUERY 网格控件。我希望它能够支持大型数据集和内联单元格编辑。

我正在努力寻找一个与控件集成并抽象出这些东西的复杂性的 PHP 后端脚本。我希望能够以最少的配置实现基于底层 MYSQL 表的网格。确实，我不可能是唯一一个希望能够指定基本数据库详细信息和表/sql 并拥有一个操作网格的人。

• JQGrid 有一个付费的 PHP 脚本后端，您可以使用它，这听起来像我想要的......只是它很昂贵。 http://www.trirand.net/documentation/php/index.htm

• 还有一种更便宜的方法试图做同样的事情，但它不起作用。 http://azgtech.wordpress.com/2010/08/01/ jqgrid-php-datagrid/

• 我也研究过 DataTables，但是一旦添加内联编辑功能，开发 PHP 后端就开始成为自己的项目。

我花了很长时间研究这个问题，并研究了各种解决方案。

有人知道有一个功能强大、免费、易于使用且具有完整 PHP 后端脚本的解决方案吗？

我已经包含了付费 JQGrid PHP 控件的一些描述，其中包含我正在寻找的功能类型。

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

您查看过 phpGrid 吗？它是一个 PHP jqgrid 包装器。只需两行代码即可开始。这是您正在寻找的东西吗？

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

Answer 2

我不久前写了这个小库，作为一个充满酒精的周末项目，它有点混乱，但它将提供您正在寻找的一些默认后端功能。]

    <?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;
}


?>

这旨在允许开发使用标准模板的网格数据表，下面是一个示例模板。

<?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;

?>

我已经扩展了 Flexgrid 以适应字段格式化、提交数据和添加字段事件，但我一生都找不到它。如果我这样做，我会发布它。

免责声明： $_POST 在整个 grid.php 中被随意使用。建议更换为更合适的东西。

Answer 3

我刚刚找到了一个 github 项目，它似乎非常适合于此： https://github.com/ lampjunkie/php-datatables

它为 Jquery Datatables 插件提供了一个包装器，还处理初始设置和 ajaq 数据提要。它采用面向对象的方法，并且设计看起来非常合乎逻辑。
您还可以在包中找到示例项目。