Monday, 19 January 2009

jQuery - inline editing/ add new row with tablesorter plugin

In this tutorial I go over some cool things that you can do with jQuery and the tablesorter plugin. In particular inline editing while maintaining sort functionality and being able to add a new row while maintaing the ability to inline edit and sort the new row correctly. After we insert a new row we also want to update our zebra striping.

The tablesorter plugin is easy to implement and handles things such as zebra striping and custom parsers with ease. If you haven't had a chance to use it yet I suggest you look at it here and go through some of the documentation and try it out for yourself.

To view a working example take a look here

I start with a file called sorter.php and an array of animals which is the data I will be displaying in my table:

$animals = array(
0 => array(
'name' => 'Cow',
'age' => '4',
'location' => 'Barnyard'
),


Then I loop through my animals and create the rows for my table as such:

$tablerows = array();

foreach ($animals as $key => $animal) {
$tablerows[] = <<<EOT
<tr class="dataline">
<td class='name'>{$animal['name']}</td>
<td class='age'>{$animal['age']}</td>
<td class='location'>{$animal['location']}</td>
<td><a href="#" class="editlink"><img alt="Edit" style="border: 0px none; margin-left: 5px;" src="images/icons/page_edit.gif"/></a></td>
<td><a href="#" class="removelink"><img alt="Remove" style="border: 0px none; margin-left: 5px;" src="images/icons/bin.gif"/></a></td>
</tr>
<tr class="editline" style="display:none;">
<form>
<td><input type="text" name="editname" value="{$animal['name']}" /></td>
<td><input type="text" name="editage" value="{$animal['age']}" /></td>
<td><input type="text" name="editlocation" value="{$animal['location']}" /></td>
</form>
<td colspan="2">
<a href="#" class="savelink">Save</a> | <a href="#" class="cancellink">Cancel</a>
</td>
</tr>
EOT;
}


You can see for each animal there are two rows with classes dataline and editline. dataline contains the row as it is seen in the table, editline is hidden and is the row that will be displayed during our inline edit. I displayed the css inline to the editline row as placing it in the css file screws up the zebra stiping for some reason, when I have it inline the same zebra striping class is applied to both the dataline and editline rows which is what we need.

Later we will add a click function to the editlink and cancellink links to toggle our data/edit views using jQuery. Clicking on the savelink link will update our row. Note that for this tutorial I am not connecting to a database or using funky AJAX calls; it should be straightforward looking at the code where this should go and is outside the scope of this tutorial (if you would like suggestions on doing this drop me an email). The remove link also currently just links to a call to the remove() function for simplicity purposes.

Lets have a look at the rest of our sorter.php file:

<div id="content">
<p id="updatemessage"></p>
<button id="addrowbutton" name="addrow">Add row</button>
<table id='mySortable' class='tablesorter'>
<thead>
<tr>
<th>Name</th>
<th>Age</th>
<th>Location</th>
<th>Edit</th>
<th>Delete</th>
</tr>
<tr id="addrow" style="display:none;"> <!-- Our add new row -->
<form>
<td><input type="text" name="addname" value="" /></td>
<td><input type="text" name="addage" value="" /></td>
<td><input type="text" name="addlocation" value="" /></td>
<td>
<a href="#" id="saveadd">Save</a> | <a href="#" id="canceladd">Cancel</a>
</td>
</form>    
</tr>
</thead>
<tbody>
<?php foreach ($tablerows as $row) { echo $row; } ?>
</tbody>
</table>
</div>


OK, we have our updatemessage where messages will be displayed whenever an item is added, updated or deleted. We also have a button to add a new row.

The next interesting thing to look at is the <thead> section. You can see that it has two tr's. As commented the second tr is for our addrow functionality. We could just clone a row each time we wanted to add a new row and clear out the fields; I think this is the easier way around, it's stuck inside the thead so it doesn't interfere with the sorting.

Later we will add our save functionality to this which will clone the top row from the table and update the fields accordingly.

I'm not going to over everything in our javascript file but if you would like to view the source look here

Here is the call to the tablesorter plugin:

$("#mySortable").tablesorter({
widgets: ['zebra'],
headers: {
0: {
sorter:'inline'
},
1: {
sorter:'inline_number'
},
2: {
sorter:'inline'
},   
3: { 
sorter: false 
}, 
4: {
sorter: false 
} 
}
});


You can see here we have attached our "zebra" widget which will place alternating classes on each line of our table. You will see for each column I have specified a sorter. For the Edit and Delete columns I have specified to remove sorting from them. You can also see for the other columns I have specified custom sorters. The purpose for this is because our rows for inline editing interfere with the natural table sorting.

Here is our inline custom parser:

$.tablesorter.addParser({ 
   id: 'inline', 
   is: function(s) { 
   return false; 
}, 
format: function(s) { 
   var pattern = 'value=(?:\"?)([^"]*)(?:\"?)\\s';
   matches = s.match(pattern);

if (matches != null) {
   jQuery.makeArray(matches);
   result = matches[1];
} else {
   result = s;
}
return result; 
}, 
type: 'text' 
});

This uses a regular expression to see if what is passed in is our edit row with something along the lines of: "<input type="text" value="Cow" name="editname"/>". If that is the case it just grabs what is inside value="" and passes that back to use for sorting, if there is no match it just uses the value.

The rest should be pretty straight forward. The only other special thing to note is the last line of the add new line function $("#saveadd").click(function() {
the call $("#mySortable").trigger("applyWidgets", "zebra"); just says to reapply the zebra striping once the new line has been added.

This should cover what is going on, if you have anything specific you would like expained just leave a comment. Take time to have a look at the source including the javascript file here

Enjoy,
Tim.

14 comments:

  1. Hi!

    Very nice enhancement!! Thanks!
    What about Ajax support and writing the new row to a MYSQL database?
    I'm quite new to jQuery, but it should be possible to add this to your coding, right?

    Maybe you something ready otherwise I'm thankful for any hint and/or suggestion how to do this...

    Keep up the good work!
    Daniel

    ReplyDelete
  2. Hi Daniel,

    Thanks for your comment.

    The place to stick the ajax call is inside the

    $(".savelink").click(function() {

    and

    $("#saveadd").click(function() {


    As you can see from the example I just grab the value from the inputs and change the row. If necessary you can use a jQuery AJAX call to a php script and post this information to your database.

    You can use jQuery's AJAX post to do this, check out some of the examples here at jQuery. Just click on examples to see how it's done.

    You can then use your php script to grab the posted variables and insert into the database. You could even return a success / fail message.

    Another hint is to use firebug with firefox browser, that way you can see what is sent and returned with AJAX calls.

    If you still need more info let me know.

    Tim.

    ReplyDelete
  3. @Tim,
    May I ask for this demo's all source(+mysql+php) code:
    http://comp345.awardspace.com/sorter/sorter.php

    Thanks for the article.

    ReplyDelete
  4. Hello Tim,

    Great, work, I have benefited a lot from this pattern, it helps a lot in many areas.

    If I may suggest the following:

    1) the links for "save" and "cancel" flickers the page and the whole page scrolls to the top. I suggest replacing the JQuery function added on click with a "javascript:saveRow(rowId)" which is added during HTML creation or with JQuery on page ready. this way the page does not not scrolls beside that it is a lot

    2) Removing the row does not actually remove the whole row from the cached sortable table. I could not figure how to fix that problem (especially if you use pagination), so simply I reloaded the table from the server on successful delete after the AJAX was executed on the server. If any body has a simpler solution, please share. That was a tough one.


    I like to congratulate you on the simplicity of the design. Good job.


    Salah M.

    ReplyDelete
  5. @HMERT - Hi, don't have an email address to contact you, I went to your site and couldn't find an address. You can now find the source code to this example here

    @Salah - Thanks for your encouragement. I have found this plugin to be useful in my own work. I haven't had a chance to look at the caching issue with removing the row. Perhaps you could post a link or send me an email with this issue and we can both work on a solution.

    ReplyDelete
  6. Hi Tim!

    Great job,i have learn a lot by this, and also using your concept in my tables,but it is giving me error msg while i m going to save edit/add content..
    as
    Exception... "'Syntax error, unrecognized expression: [@name='editname']' when calling method: [nsIDOMEventListener::handleEvent]" nsresult: "0x8057001e (NS_ERROR_XPC_JS_THREW_STRING)" location: "unknown" data: no]
    on firebug,
    firefox ver 3.0
    Mac os
    Ill be thankful for your kind help.

    ReplyDelete
  7. Hi Tim!

    Great job,i have learn a lot by this, and also using your concept in my tables,but it is giving me error msg while i m going to save edit/add content..
    as
    Exception... "'Syntax error, unrecognized expression: [@name='editname']' when calling method: [nsIDOMEventListener::handleEvent]" nsresult: "0x8057001e (NS_ERROR_XPC_JS_THREW_STRING)" location: "unknown" data: no]
    on firebug,
    firefox ver 3.0
    Mac os
    Ill be thankful for your kind help.

    ReplyDelete
  8. if you delete all row then you add new row it won't come out

    ReplyDelete
  9. @mayank: glad this was helpful. Try this [name='editname'] instead of [@name='editname']. From the jQUery site: "In jQuery 1.3 [@attr] style selectors were removed (they were previously deprecated in jQuery 1.2). Simply remove the '@' symbol from your selectors in order to make them work again." Or try a different version of jQuery to see if that helps.

    @Anonymous: This is not supposed to be a full working demonstration. You should be able to take the main idea and do what needs doing. If you have a specific question let me know.

    ReplyDelete
  10. $(".removelink").click(function() {
    var datapos = $(this).parent().parent().prevAll().length;

    $("#mySortable tbody tr:eq(" + datapos + ")").remove(); // Remove data row
    $("#mySortable tbody tr:eq(" + datapos + ")").remove(); // Remove edit row

    $("#updatemessage").text("Row remove success").fadeOut(4000, function() {
    $(this).css('display','block').text("");
    });
    });

    Should be as :

    $(".removelink").click(function() {
    var datapos = $(this).parent().parent().prevAll().length;
    var editpos = datapos + 1;

    $("#mySortable tbody tr:eq(" + datapos + ")").remove(); // Remove data row
    $("#mySortable tbody tr:eq(" + editpos + ")").remove(); // Remove edit row

    $("#updatemessage").text("Row remove success").fadeOut(4000, function() {
    $(this).css('display','block').text("");
    });
    });

    ReplyDelete
  11. Hi Richard, you will find that the index of the edit row will be the same as the data row once the data row has been removed, thus not needing to increase the index by one.

    Check it out with firebug, you can call each step manually.

    ReplyDelete
  12. Hello Tim,

    This is indeed a great demo.

    It would be very much appreciated if you could add the piece of code that displays the newly added row once all the existing rows have already been removed.

    Thanks and keep up the great work!
    Pete

    ReplyDelete
  13. May I ask for this demo's all source
    such as sorter.js file.

    ReplyDelete
  14. Hi Tim,

    Fantastic Job, as i am new to jquery
    i am unable save and add new data to mysql database, can u please provide any code to save the edited, deleted and new row in to database with out page reloading. Very Very thanks in Advance. My Email is "shivanath.k@gmail.com".

    ReplyDelete