limi.eu

HTML Table Management With jQuery

Introduction:

Here an example of how to manage tables (e.g. add/delete rows, edit entries) with jQuery and serverside database storage is shown.

The great Font Awesome is used for the action icons.

How it looks like:

Add Row

How it works:

// the file that handles POST requests and database actions
var db_file = "js-table.php";

// some HTML templates
var Template_first_name_Cell = '<input type="text" class="t_first_name">';
var Template_last_name_Cell = '<input type="text" class="t_last_name">';
var Template_birthday_Cell = '<input type="date" class="t_birthday">';
var Template_phone_Cell = '<input type="tel" class="t_phone">';
var Template_email_Cell = '<input type="email" class="t_email">';
var Template_actionCell_addNew = '<a class="save" href="#"><i class="fa fa-floppy-o" title="save"></i></a>&nbsp; &nbsp;'
                                +'<a class="delete_new" href="#"><i class="fa fa-trash-o" title="delete"></i></a>';
var Template_actionCell_edit = '<a class="save_edit" href="#"><i class="fa fa-floppy-o" title="save"></i></a>&nbsp; &nbsp;'
                              +'<a class="cancel_edit" href="#"><i class="fa fa-undo" title="cancel"></i></a>';
var Template_actionCell_normal = '<a class="edit_row" href="#"><i class="fa fa-pencil" title="edit"></i></a>&nbsp; &nbsp;'
                                +'<a class="delete_row" href="#"><i class="fa fa-trash-o" title="delete"></i></a>&nbsp; &nbsp;'
                                +'<a class="duplicate_row" href=#"><i class="fa fa-share fa-rotate-90" title="duplicate"></i></a>';

var Template_Row = '<tr>'
                  +'<td>' + Template_first_name_Cell + '</td>'
                  +'<td>' + Template_last_name_Cell + '</td>'
                  +'<td>' + Template_birthday_Cell + '</td>'
                  +'<td>' + Template_phone_Cell + '</td>'
                  +'<td>' + Template_email_Cell + '</td>'
                  +'<td>' + Template_actionCell_addNew + '</td>'
                  +'</tr>';

// original values for "cancel edit"
var first_name = '';
var last_name = '';
var birthday = '';
var phone = '';
var email = '';

// add event handlers to action links
function addEvents() {
    // first delete event handler from all action buttons
    $(".save").off();
    $(".save_edit").off();
    $(".cancel_edit").off();
    $(".delete_row").off();
    $(".edit_row").off();
    $(".duplicate_row").off();
    // then add event handlers to all (also the new added) links again

    // save a new entry
    $(".save").on("click", function() {
        var tableRow = $(this).closest("tr");

        var first_name = $(tableRow).find(".t_first_name").val();
        var last_name = $(tableRow).find(".t_last_name").val();
        var birthday = $(tableRow).find(".t_birthday").val();
        var phone = $(tableRow).find(".t_phone").val();
        var email = $(tableRow).find(".t_email").val();

        var first_name_cell = $(tableRow).find("td:first");
        var last_name_cell = $(tableRow).find("td:nth-child(2)");
        var birthday_cell = $(tableRow).find("td:nth-child(3)");
        var phone_cell = $(tableRow).find("td:nth-child(4)");
        var email_cell = $(tableRow).find("td:nth-child(5)");
        var action_cell = $(tableRow).find("td:last");

        if(first_name!="" && last_name!="" && birthday!="" && phone!="" && email!="") {
            // send data to database
            $.post( db_file,
                    { "action":"save", "last_name":last_name, "first_name":first_name,
                      "birthday":birthday, "phone":phone, "email":email },
                    function(data) {
                        $(first_name_cell).html(first_name);
                        $(last_name_cell).html(last_name);
                        $(birthday_cell).html(birthday);
                        $(phone_cell).html(phone);
                        $(email_cell).html(email);
                        // id of the new row from database
                        $(action_cell).html(Template_actionCell_normal+'<span id="'+data.id+'"></span>');
                        addEvents();
                    }, "json"
            );
        } else {
                alert('Please fill all input fields!');
        }
    });

    // save an edited row
    $(".save_edit").on("click", function() {
        var tableRow = $(this).closest("tr");

        var first_name = $(tableRow).find(".t_first_name").val();
        var last_name = $(tableRow).find(".t_last_name").val();
        var birthday = $(tableRow).find(".t_birthday").val();
        var phone = $(tableRow).find(".t_phone").val();
        var email = $(tableRow).find(".t_email").val();

        var first_name_cell = $(tableRow).find("td:first");
        var last_name_cell = $(tableRow).find("td:nth-child(2)");
        var birthday_cell = $(tableRow).find("td:nth-child(3)");
        var phone_cell = $(tableRow).find("td:nth-child(4)");
        var email_cell = $(tableRow).find("td:nth-child(5)");
        var action_cell = $(tableRow).find("td:last");

        var id = $(action_cell).find("span").attr("id");

        if(first_name!="" && last_name!="" && birthday!="" && phone!="" && email!="") {
            // send data to database
            $.post( db_file,
                    { "action":"save_edit", "last_name":last_name, "first_name":first_name,
                      "birthday":birthday, "phone":phone, "email":email, "id":id
                    }, function() {
                        $(first_name_cell).html(first_name);
                        $(last_name_cell).html(last_name);
                        $(birthday_cell).html(birthday);
                        $(phone_cell).html(phone);
                        $(email_cell).html(email);
                        $(action_cell).html(Template_actionCell_normal+'<span id="'+id+'"></span>');
                        addEvents();
                    }
            );
        } else {
                alert('Please fill all input fields!');
        }
    });

    // Delete new appended row. Because there's no data saved, delete DOM elements only
    $(".delete_new").on("click", function() {
        $(this).closest("tr").remove();
    });

    // Delete a row with data. Because there's data, besides deleting the DOM elements
    // send the data about to delete to PHP too
    $(".delete_row").on("click", function() {
        var id = $(this).closest("span").attr("id");

        $(this).closest("tr").remove();
        // send data to database
        $.post(db_file, { "action":"delete_row", "id":id }, function(){
            // some error handling
        });
    });

    // edit a row
    $(".edit_row").on("click", function() {
        var table = $(this).closest("table");

        // abort if a not (yet) saved row (i.e. an input field) is found
        if ($(table).find("input").length > 0) {
            alert("Please save or delete\nnot yet saved rows in the table!");
            return;
        } else {
            var tableRow = $(this).closest("tr");

            var first_name_cell = $(tableRow).find("td:first");
            var last_name_cell = $(tableRow).find("td:nth-child(2)");
            var birthday_cell = $(tableRow).find("td:nth-child(3)");
            var phone_cell = $(tableRow).find("td:nth-child(4)");
            var email_cell = $(tableRow).find("td:nth-child(5)");
            var action_cell = $(tableRow).find("td:last");

            // save values globally in case of "cancel edit"
            first_name = $(first_name_cell).html();
            last_name = $(last_name_cell).html();
            birthday = $(birthday_cell).html();
            phone = $(phone_cell).html();
            email = $(email_cell).html();

            var id = $(action_cell).find("span").attr("id");

            $(first_name_cell).html(Template_first_name_Cell).find(".t_first_name").val(first_name);
            $(last_name_cell).html(Template_last_name_Cell).find(".t_last_name").val(last_name);
            $(birthday_cell).html(Template_birthday_Cell).find(".t_birthday").val(birthday);
            $(phone_cell).html(Template_phone_Cell).find(".t_phone").val(phone);
            $(email_cell).html(Template_email_Cell).find(".t_email").val(email);
            $(action_cell).html(Template_actionCell_edit+'<span id="'+id+'"></span>');

            $(tableRow).find("td:first input").focus();

            addEvents();
        }
    });

    // abort edit
    $(".cancel_edit").on("click", function() {
        var tableRow = $(this).closest("tr");

        var first_name_cell = $(tableRow).find("td:first");
        var last_name_cell = $(tableRow).find("td:nth-child(2)");
        var birthday_cell = $(tableRow).find("td:nth-child(3)");
        var phone_cell = $(tableRow).find("td:nth-child(4)");
        var email_cell = $(tableRow).find("td:nth-child(5)");
        var action_cell = $(tableRow).find("td:last");

        var id = $(action_cell).find("span").attr("id");

        // fill in globally saved values
        $(first_name_cell).html(first_name);
        $(last_name_cell).html(last_name);
        $(birthday_cell).html(birthday);
        $(phone_cell).html(phone);
        $(email_cell).html(email);
        $(action_cell).html(Template_actionCell_normal+'<span id="'+id+'"></span>');

        addEvents();
    });

    // duplicate row
    $(".duplicate_row").on("click", function() {
        var table = $(this).closest("table");

        // abort if a not (yet) saved row (i.e. an input field) is found
        if ($(table).find("input").length > 0) {
            alert("Please save or delete\nnot yet saved rows in the table!");
            return;
        } else {
            var tableBody = $(table).find("tbody");
            var tableRow = $(this).closest("tr");
            var tableRowIndex = parseInt( $(tableRow).index() );
            // save value of current row
            var first_name = $(tableRow).find("td:first").html();
            var last_name = $(tableRow).find("td:nth-child(2)").html();
            var birthday = $(tableRow).find("td:nth-child(3)").html();
            var phone = $(tableRow).find("td:nth-child(4)").html();
            var email = $(tableRow).find("td:nth-child(5)").html();
            // insert new row after current row
            $(tableRow).after(Template_Row);
            // find position of new inserted row
            var tableRows = $(tableBody).children();
            var tableRowNew = tableRows[tableRowIndex+1];
            // insert saved values into new row
            $(tableRowNew).find(".t_first_name").val(first_name);
            $(tableRowNew).find(".t_last_name").val(last_name);
            $(tableRowNew).find(".t_birthday").val(birthday);
            $(tableRowNew).find(".t_phone").val(phone);
            $(tableRowNew).find(".t_email").val(email);

            addEvents();
        }
    });

} // end addEvents()


// on DOM-ready
$(function(){
    // add event handler to button "Add Row"
    $("#add_row").on("click", function() {
        // abort if a not (yet) saved row is found
        if ($("#table_limi input").length > 0) {
            alert("Please save or delete\nnot yet saved rows in the table!");
            return;
        }
        // append new row to table ".table_limi"
        $("#table_limi tbody").append(Template_Row);
        $("#table_limi tbody tr:last td:first input").focus();
        addEvents();
    });

    addEvents();
});

A possible PHP file for handling of the POST requests and database actions could look like this (without any error handling and so on ;-).

js-table.php:

<?php
    switch ($_POST['action']) {
        case "save":
            $sql = "INSERT INTO `table` VALUES `firstname`=".$_POST['first_name'].", .... ";
            $pdo->query($sql);
            $ret['id'] = $pdo->lastInsertId();
            // return data to JS
            echo json_encode($ret);
            break;
        case "delete_row":
            $sql = "DELETE FROM `table` WHERE `ID`=".$_POST['id'];
            $pdo->query($sql);
            break;

        .....
    }
?>