我希望用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后端脚本吗？
我已经包含了对付费JQGridPHP控件的一些描述，其中包含了我正在寻找的功能。
JQGRid PHP这是一个为PHP开发人员和团队创建的全新产品，它从根本上减少了jqGrid的开发时间，并使其变得有趣和简单。该产品将提供商业许可证，其中包括快速准确的技术支持、源代码和基于订阅的好处。亮点包括：-支持jqGrid的所有功能-不需要复杂的javascript–一切都在PHP中处理-PHP组件自动处理数据检索、分页、排序、搜索和所有CRUD操作（创建、读取、更新、删除）。不需要自定义代码。-只需几行PHP就可以得到一个功能齐全的网格-支持导出到Excel-支持的数据库引擎：MySql、PostgreSQL、Microsoft SQL server
您已经签出phpGrid了吗。它是一个PHP jqgrid包装器。开始只需要两行代码。这是你要找的东西吗？
$dg = new C_DataGrid(“SELECT * FROM orders”, “orderNumber”, “orders”);
$dg -> display();
我不久前写了这个小库，作为一个充满酒精的周末项目，它有点乱，但它将提供您正在寻找的一些默认后端功能。]
<?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中被肆意使用。建议用更合适的东西替换它。
我刚刚发现了一个github项目，它似乎真的很适合：https://github.com/lampjunkie/php-datatables
它为Jquery Datatables插件提供了一个包装器，还处理初始设置和ajaq数据馈送。它采用了一种面向对象的方法，并且似乎设计得非常符合逻辑。您也可以在包中找到一个示例项目。