DataTables – Data from Ajax, Edit in Place Quite often, when it comes to tabular data and the need to put them on the screen in a convenient form with various additional features, we can seriously consider how exactly can this be accomplished? The most necessary functions usually are: to obtain data with AJAX, page navigation (pagination), data sorting by fields, searching, and filtering. The more desired features, the more complex system it will be. Of course, we can start realization of all necessary functions, one for another. It is clear that such a complex system can take a lot of time, however, it is quite possible that we will not have to ‘reinvent the wheel’.
DataTables –来自Ajax的数据，就地编辑经常，当涉及表格数据以及需要以具有各种附加功能的便捷形式将它们显示在屏幕上时，我们可以认真考虑如何实现这一目标？ 通常，最必要的功能是：使用AJAX获取数据，页面导航(分页)，按字段对数据进行排序，搜索和过滤。 所需的功能越多，系统将越复杂。 当然，我们可以开始实现所有必要的功能。 显然，这样一个复杂的系统可能会花费很多时间，但是，我们很有可能不必“重新发明轮子”。
现场演示
[sociallocker]
[社交储物柜]
下载资源
[/sociallocker]
[/ sociallocker]
In order not to invent anything new, I invite you to consider one of the ready-jquery plugin DataTables. In the past we already mentioned this plugin, but we have not studied it in detail. DataTables already solved many of the necessary tasks such as working with AJAX, page navigation, searching and sorting. However, there is no way to edit the data. This need may occur, for example, if you develop a page for the admin panel of your website. It could be a perfect solution when we need to work, for example with a list of members of the website. Moreover, the ability to edit in place (with validation) – could be the ideal solution.
为了不发明任何新东西，我邀请您考虑使用ready-jquery插件DataTables之一 。 过去我们已经提到过该插件，但是我们没有对其进行详细研究。 DataTables已经解决了许多必要的任务，例如使用AJAX，页面导航，搜索和排序。 但是，无法编辑数据。 例如，如果您为网站的管理面板开发一个页面，则可能会发生这种需求。 当我们需要工作时(例如，网站成员列表)，这可能是一个完美的解决方案。 此外，就地编辑(带有验证)功能可能是理想的解决方案。
Fortunately it is possible, and today we tell about how this can be implemented. We will take a few additional libraries: dataTables.editable, Jeditable + jQuery validation plugin. We just have to put everything together.
幸运的是，这是可能的，今天我们介绍如何实现这一点。 我们将添加一些其他库：dataTables.editable，Jeditable + jQuery验证插件。 我们只需要把所有东西放在一起。
HTML (HTML)
For a start, we will create the html table where we will load the data. Also, do not forget to include all needed JS libraries and CSS files.
首先，我们将创建html表，在该表中加载数据。 另外，不要忘记包含所有需要的JS库和CSS文件。
index.html (index.html)
<link href="css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<link href="css/styles.css" rel="stylesheet" type="text/css" />
<div class="table">
<h2>'pd_profiles' table</h2>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="pd_profiles" style="width:95%;margin:0 auto" width="100%">
<thead><th title="First Name">first_name</th><th title="Last Name">last_name</th><th title="Email">email</th><th title="Status">status</th><th title="Role">role</th><th title="Registration date">date_reg</th></thead>
<tbody><tr><td colspan="6" class="dataTables_empty">Downloading data about the users from the server</td></tr></tbody>
<tfoot><th title="First Name">first_name</th><th title="Last Name">last_name</th><th title="Email">email</th><th title="Status">status</th><th title="Role">role</th><th title="Registration date">date_reg</th></tfoot>
</table>
<div style="clear:both"></div><br /><hr />
<button id="btnDeleteMemRow">Delete record</button>
</div>
<script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/jquery.dataTables.editable.js"></script>
<script src="js/jquery.jeditable.js"></script>
<script src="js/jquery.validate.js"></script>
<script src="js/main.js"></script>
<link href="css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<link href="css/styles.css" rel="stylesheet" type="text/css" />
<div class="table">
<h2>'pd_profiles' table</h2>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="pd_profiles" style="width:95%;margin:0 auto" width="100%">
<thead><th title="First Name">first_name</th><th title="Last Name">last_name</th><th title="Email">email</th><th title="Status">status</th><th title="Role">role</th><th title="Registration date">date_reg</th></thead>
<tbody><tr><td colspan="6" class="dataTables_empty">Downloading data about the users from the server</td></tr></tbody>
<tfoot><th title="First Name">first_name</th><th title="Last Name">last_name</th><th title="Email">email</th><th title="Status">status</th><th title="Role">role</th><th title="Registration date">date_reg</th></tfoot>
</table>
<div style="clear:both"></div><br /><hr />
<button id="btnDeleteMemRow">Delete record</button>
</div>
<script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/jquery.dataTables.editable.js"></script>
<script src="js/jquery.jeditable.js"></script>
<script src="js/jquery.validate.js"></script>
<script src="js/main.js"></script>
For our demo (and our example) – I took one of the existing tables (mySQL) – ‘pd_profiles’. All required fields that we need to show I indicated in the table’s header.
对于我们的演示(和我们的示例)–我采用了现有表之一(mySQL)–'pd_profiles'。 我们需要显示的所有必填字段均已在表格标题中指示。
Java脚本 (Javascript)
Now let’s turn to our html table in a DataTable and make it editable
现在，让我们转到DataTable中的html表，使其可编辑
inc / main.js (inc/main.js)
$(function() {
var oMemTable = $('#pd_profiles').dataTable({
'bProcessing': true, 'bServerSide': true, 'sAjaxSource': 'service.php?action=getMembersAjx',
}).makeEditable({
sUpdateURL: 'service.php?action=updateMemberAjx',
'aoColumns': [
{
tooltip: 'First Name',
oValidationOptions : { rules:{ value: {minlength: 3 } },
messages: { value: {minlength: 'Min length - 3'} } }
},
{
tooltip: 'Last Name',
oValidationOptions : { rules:{ value: {minlength: 3 } },
messages: { value: {minlength: 'Min length - 3'} } }
},
{
tooltip: 'Email',
oValidationOptions : { rules:{ value: {minlength: 5 } },
messages: { value: {minlength: 'Min length - 5'} } }
},
{
tooltip: 'Member status',
type: 'select',
data: "{'passive':'passive','active':'active'}",
submit: 'Ok',
},
{
tooltip: 'Member role',
},
{
tooltip: 'date_reg',
oValidationOptions : { rules:{ value: {minlength: 3 } },
messages: { value: {minlength: 'Min length - 3'} } }
}
],
sDeleteURL: 'service.php?action=deleteMember',
sDeleteRowButtonId: 'btnDeleteMemRow',
});
});
$(function() {
var oMemTable = $('#pd_profiles').dataTable({
'bProcessing': true, 'bServerSide': true, 'sAjaxSource': 'service.php?action=getMembersAjx',
}).makeEditable({
sUpdateURL: 'service.php?action=updateMemberAjx',
'aoColumns': [
{
tooltip: 'First Name',
oValidationOptions : { rules:{ value: {minlength: 3 } },
messages: { value: {minlength: 'Min length - 3'} } }
},
{
tooltip: 'Last Name',
oValidationOptions : { rules:{ value: {minlength: 3 } },
messages: { value: {minlength: 'Min length - 3'} } }
},
{
tooltip: 'Email',
oValidationOptions : { rules:{ value: {minlength: 5 } },
messages: { value: {minlength: 'Min length - 5'} } }
},
{
tooltip: 'Member status',
type: 'select',
data: "{'passive':'passive','active':'active'}",
submit: 'Ok',
},
{
tooltip: 'Member role',
},
{
tooltip: 'date_reg',
oValidationOptions : { rules:{ value: {minlength: 3 } },
messages: { value: {minlength: 'Min length - 3'} } }
}
],
sDeleteURL: 'service.php?action=deleteMember',
sDeleteRowButtonId: 'btnDeleteMemRow',
});
});
In the initialization, we specify that data will be taken from the server by Ajax, then we made the table editable, after we specify the address to update the values of the fields. Also we have listed all our fields and validation rules for each field. In the end, we indicated ID of the ‘Delete’ button, and the URL to send the data.
在初始化中，我们指定数据将由Ajax从服务器获取，然后在指定地址以更新字段的值之后使表可编辑。 我们还列出了所有字段和每个字段的验证规则。 最后，我们指出了“删除”按钮的ID，以及发送数据的URL。
PHP (PHP)
As you already noticed, all AJAX requests work with service.php file. This is a special service file that works as a web service (that handles with AJAX requests).
您已经注意到，所有AJAX请求都可以使用service.php文件。 这是一个特殊的服务文件，用作Web服务(处理AJAX请求)。
service.php (service.php)
if ($_GET) {
require_once('classes/CMySQL.php');
switch ($_GET['action']) {
case 'getMembersAjx':
getMembersAjx();
break;
case 'updateMemberAjx':
updateMemberAjx();
break;
case 'deleteMember':
deleteMember();
break;
}
exit;
}
function getMembersAjx() {
// SQL limit
$sLimit = '';
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = 'LIMIT ' . (int)$_GET['iDisplayStart'] . ', ' . (int)$_GET['iDisplayLength'];
}
// SQL order
$aColumns = array('first_name', 'last_name', 'email', 'status', 'role', 'date_reg');
$sOrder = '';
if (isset($_GET['iSortCol_0'])) {
$sOrder = 'ORDER BY ';
for ($i=0 ; $i<(int)$_GET['iSortingCols'] ; $i++) {
if ( $_GET[ 'bSortable_'.(int)$_GET['iSortCol_'.$i] ] == 'true' ) {
$sOrder .= '`'.$aColumns[ (int)$_GET['iSortCol_'.$i] ].'` '.
($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .', ';
}
}
$sOrder = substr_replace($sOrder, '', -2);
if ($sOrder == 'ORDER BY') {
$sOrder = '';
}
}
// SQL where
$sWhere = 'WHERE 1';
if (isset($_GET['sSearch']) && $_GET['sSearch'] != '') {
$sWhere = 'WHERE 1 AND (';
for ($i=0; $i<count($aColumns) ; $i++) {
if (isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == 'true') {
$sWhere .= '`' . $aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch'])."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, '', -3 );
$sWhere .= ')';
}
$aMembers = $GLOBALS['MySQL']->getAll("SELECT * FROM `pd_profiles` {$sWhere} {$sOrder} {$sLimit}");
$iCnt = (int)$GLOBALS['MySQL']->getOne("SELECT COUNT(`id`) AS 'Cnt' FROM `pd_profiles` WHERE 1");
$output = array(
'sEcho' => intval($_GET['sEcho']),
'iTotalRecords' => count($aMembers),
'iTotalDisplayRecords' => $iCnt,
'aaData' => array()
);
foreach ($aMembers as $iID => $aInfo) {
$aItem = array(
$aInfo['first_name'], $aInfo['last_name'], $aInfo['email'], $aInfo['status'], $aInfo['role'], $aInfo['date_reg'], 'DT_RowId' => $aInfo['id']
);
$output['aaData'][] = $aItem;
}
echo json_encode($output);
}
function updateMemberAjx() {
$sVal = $GLOBALS['MySQL']->escape($_POST['value']);
$iId = (int)$_POST['id'];
if ($iId && $sVal !== FALSE) {
switch ($_POST['columnName']) {
case 'first_name':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `first_name`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'last_name':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `last_name`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'email':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `email`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'status':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `status`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'role':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `role`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'date_reg':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `date_reg`='{$sVal}' WHERE `id`='{$iId}'");
break;
}
echo 'Successfully saved';
}
exit;
}
function deleteMember() {
$iId = (int)$_POST['id'];
if ($iId) {
$GLOBALS['MySQL']->res("DELETE FROM `pd_profiles` WHERE `id`='{$iId}'");
return;
}
echo 'Error';exit;
}
if ($_GET) {
require_once('classes/CMySQL.php');
switch ($_GET['action']) {
case 'getMembersAjx':
getMembersAjx();
break;
case 'updateMemberAjx':
updateMemberAjx();
break;
case 'deleteMember':
deleteMember();
break;
}
exit;
}
function getMembersAjx() {
// SQL limit
$sLimit = '';
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = 'LIMIT ' . (int)$_GET['iDisplayStart'] . ', ' . (int)$_GET['iDisplayLength'];
}
// SQL order
$aColumns = array('first_name', 'last_name', 'email', 'status', 'role', 'date_reg');
$sOrder = '';
if (isset($_GET['iSortCol_0'])) {
$sOrder = 'ORDER BY ';
for ($i=0 ; $i<(int)$_GET['iSortingCols'] ; $i++) {
if ( $_GET[ 'bSortable_'.(int)$_GET['iSortCol_'.$i] ] == 'true' ) {
$sOrder .= '`'.$aColumns[ (int)$_GET['iSortCol_'.$i] ].'` '.
($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .', ';
}
}
$sOrder = substr_replace($sOrder, '', -2);
if ($sOrder == 'ORDER BY') {
$sOrder = '';
}
}
// SQL where
$sWhere = 'WHERE 1';
if (isset($_GET['sSearch']) && $_GET['sSearch'] != '') {
$sWhere = 'WHERE 1 AND (';
for ($i=0; $i<count($aColumns) ; $i++) {
if (isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == 'true') {
$sWhere .= '`' . $aColumns[$i]."` LIKE '%".mysql_real_escape_string($_GET['sSearch'])."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, '', -3 );
$sWhere .= ')';
}
$aMembers = $GLOBALS['MySQL']->getAll("SELECT * FROM `pd_profiles` {$sWhere} {$sOrder} {$sLimit}");
$iCnt = (int)$GLOBALS['MySQL']->getOne("SELECT COUNT(`id`) AS 'Cnt' FROM `pd_profiles` WHERE 1");
$output = array(
'sEcho' => intval($_GET['sEcho']),
'iTotalRecords' => count($aMembers),
'iTotalDisplayRecords' => $iCnt,
'aaData' => array()
);
foreach ($aMembers as $iID => $aInfo) {
$aItem = array(
$aInfo['first_name'], $aInfo['last_name'], $aInfo['email'], $aInfo['status'], $aInfo['role'], $aInfo['date_reg'], 'DT_RowId' => $aInfo['id']
);
$output['aaData'][] = $aItem;
}
echo json_encode($output);
}
function updateMemberAjx() {
$sVal = $GLOBALS['MySQL']->escape($_POST['value']);
$iId = (int)$_POST['id'];
if ($iId && $sVal !== FALSE) {
switch ($_POST['columnName']) {
case 'first_name':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `first_name`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'last_name':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `last_name`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'email':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `email`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'status':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `status`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'role':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `role`='{$sVal}' WHERE `id`='{$iId}'");
break;
case 'date_reg':
$GLOBALS['MySQL']->res("UPDATE `pd_profiles` SET `date_reg`='{$sVal}' WHERE `id`='{$iId}'");
break;
}
echo 'Successfully saved';
}
exit;
}
function deleteMember() {
$iId = (int)$_POST['id'];
if ($iId) {
$GLOBALS['MySQL']->res("DELETE FROM `pd_profiles` WHERE `id`='{$iId}'");
return;
}
echo 'Error';exit;
}
Our table needs only three functions: to get the list of users (getMembersAjx, this function also provides sorting, pagination and search fields), update records in mysql table (updateMemberAjx), and delete records (deleteMember)
我们的表只需要三个函数：获取用户列表(getMembersAjx，此函数还提供排序，分页和搜索字段)，更新mysql表中的记录(updateMemberAjx)和删除记录(deleteMember)
CSS (CSS)
To display the DataTable properly, we attached the ‘jquery.dataTables.css’ file. However, there is one slight drawback – it is not able to highlight the selected table rows. We fixed this little flaw with these additional styles:
为了正确显示DataTable，我们附加了“ jquery.dataTables.css”文件。 但是，有一个轻微的缺点–它无法突出显示所选的表行。 我们使用以下其他样式修复了此小缺陷：
css / styles.css (css/styles.css)
.table {
margin: 50px auto;
width: 90%;
}
table.display tr.even.row_selected td {
background-color: #B0BED9;
}
table.display tr.odd.row_selected td {
background-color: #9FAFD1;
}
.table {
margin: 50px auto;
width: 90%;
}
table.display tr.even.row_selected td {
background-color: #B0BED9;
}
table.display tr.odd.row_selected td {
background-color: #9FAFD1;
}
现场演示
结论 (Conclusion)
I hope that our lesson will provide you with an invaluable help in the understanding of that you can implement in your projects. This iis the perfect solution for complete work with tabulated data.
我希望我们的课程将为您提供宝贵的帮助，帮助您了解可以在项目中实施的方法。 这是完整处理列表数据的完美解决方案。
PS: I hope that you understand that in our online demo we disabled the full editing of data (and delete data). The fact is that this table is used in another our lesson. Currently the table is read only.
PS：我希望您了解，在我们的在线演示中，我们禁用了数据的完整编辑(和删除数据)。 事实是，该表已在我们的另一堂课中使用。 当前该表是只读的。
翻译自: https://www.script-tutorials.com/datatables-data-from-ajax-edit-in-place/
1806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
