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>
Javascript
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',
});
});
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;
}
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;
}
VIEW DEMO..............................
***************************************************************************