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’.
Live Demo
[sociallocker]
download the sources
[/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.
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.
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.
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>
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.
Javascript
Now let’s turn to our html table in a DataTable and make it editable
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', }); });
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.
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).
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; }
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)
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:
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; }
Live Demo
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.
Nice PHP script pal
but the function updateMemberAjx() could be much shorter and more dynamic
there is no point in putting it in a switch since the syntax is identical for all of them besides the name
that way it will also be dynamic incase you add something or change the name
Hi Gooter,
Yes, it could be twice shorter, but I had to show you in easy-to-understand way.