DataTables – Data from Ajax, Edit in Place

DataTables – Data from Ajax, Edit in Place

2 102130
DataTables – Data from Ajax, Edit in Place
DataTables - Data from Ajax, Edit in Place

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.

SIMILAR ARTICLES

Understanding Closures

0 21865

2 COMMENTS

  1. 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

Leave a Reply