DataTables editing example

jcrawford

Hey Allan,

Is there any way you could put up some sample code as to how the editable_ajax.php file works? I am not sure how it posts the data when integrated with DataTables but am interested in implementing this into a server side data table.

jcrawford

I have the above question figured out, however I am still left with one question. Is there a way to tell DataTables that I don't want certain columns to be editable? Or if I do have them be a drop down list of values rather than a text box?

jcrawford

ok so I can see by looking at the jEditable documentation that you can in-fact have drop downs, however it only shows how to implement this without using DataTables.

Allan,

Could you show an example of how you would add drop down box to specific columns? Also could you show an example of how to make a certain column non-editable? I don't want to allow users to change the row id for instance.

Thanks,
Joseph Crawford

fbas

@jcrawford

the makeEditable() function accepts an aoColumns object, which you've probably already found in order to pass null objects to the columns you want not editable.

Each of those objects in the aoColumns can be (I believe) any valid jEditable definition.

The drop-down set up you're looking for is really quite simple. jEditable allows you to specify drop down lists as array maps in the "data" field, if you specify the "type" to be "select"
[code]
{
type: 'select',
onblur: 'submit',
data: "{'':'Please select...', 'A':'A','B':'B','C':'C'}"
}
[/code]

see this page for DataTables specific ideas for jEditable defs: http://code.google.com/p/jquery-datatables-editable/wiki/CustomCellEditors

or ask more follow up questions and I'll try to assist

jcrawford

hello fbas,

I read over the documentation at that link you provided however I cannot use it in that manner because I am using server side data.

I understand that in order to use the jEditable with server side you must define the handlers in the fnDrawCallback function. here is what I currently have.

[code]
var oTable = $('#promos').dataTable( {
"bProcessing": true,
"bFilter": false,
"bServerSide": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "/admin/mktoolviewpromotionsajax/",
"aaSorting": [[ 1, "desc" ]],
"oLanguage": {
"sZeroRecords": "No Promotions Found."
},

"sDom": '<"H"Tfr>t<"F"ip>',
"oTableTools": {
"sSwfPath": "/admin-assets/TableTools.swf",
"aButtons": [
"copy", "print",
{
"sExtends": "collection",
"sButtonText": "Save",
"aButtons": [ "csv", "xls", "pdf" ]
}
]
},
"fnDrawCallback": function() {
/* Apply the jEditable handlers to the table */
$('td', oTable.fnGetNodes()).editable( '/admin/mktoolviewpromotionseditajax/', {
"callback": function( sValue, y ) {
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate( sValue, aPos[0], aPos[1] );
},
"submitdata": function ( value, settings ) {
return {
"row_id": this.parentNode.getAttribute('id'),
"column": oTable.fnGetPosition( this )[2]
};
},
"height": "14px"
} );
},
"fnServerData": function( sSource, aoData, fnCallback) {
$.getJSON(sSource, aoData, function(json) {
if(jQuery.isEmptyObject(json)) location.href = '/admin/login/';
else fnCallback(json);
});
},
} );
[/code]

I did try the .makeEditable method on that page you provided but it was not working out. I also defined my aoColumns in the datatable outside the fnDrawCallback but it did not listen to the types I sent in, when I sent null it still allowed editing. A select element was text etc.

fbas

I should clarify. To use the makeEditable() routine, you have to install and reference the plug-in files. This adds the routine and the ability to use the intialization object in that routine.

Using makeEditable is basically a better way to replace using the fnDrawCallback method to intialize jeditable.

However, you should be able to use the same settings in the .editable() call in fnDrawCallback, including setting the "data" to the mapped array, and set the "type" to "select"

I don't have time to test this, but I think all you need are these changes (adding "data" and "type"):
[code]
"fnDrawCallback": function() {
/* Apply the jEditable handlers to the table */
$('td', oTable.fnGetNodes()).editable( '/admin/mktoolviewpromotionseditajax/', {
"callback": function( sValue, y ) {
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate( sValue, aPos[0], aPos[1] );
},
"submitdata": function ( value, settings ) {
return {
"row_id": this.parentNode.getAttribute('id'),
"column": oTable.fnGetPosition( this )[2]
};
},
"height": "14px",
"type": "select",
"data": "{'':'Please select...', 'A':'A','B':'B','C':'C'}" // creates drop-down list with 4 elements
} );

[/code]

jcrawford

ok so I have this implemented with your method of using the makeEditable. Everything is working fine except when I change the value of a column which is a drop down. Every time I submit it the page shows me a JS alert. Any idea how I can get this alert to go away?

allan

What does the alert say? Perhaps it would be doing a search / grep through the code for "alert" and see what would be triggering it, and just remove it there.

Allan

p.s. I totally agree btw - I think an editable example will help... Added to the list!

fbas

From my experience, I believe you would only receive an 'alert' from the system if the value you retrieve from the server (after your attempt to update) doesn't match what the user submitted (which would be the value of your select drop-down, I believe). so make sure your 'update' script on the server side returns a value

without seeing your project I can't be sure this is the issue, but it might point you in the right direction.

what is the value of the alert? what is the value you are submitting? what does your update script (on the server side) look like?

jcrawford

goodness my bad, i didn't have it echoing out a response at all it's working now.

fbas

excellent

http://www.mytinyphone.com/uploads/users/anaverry/173700.jpg

jcrawford

is there a way that i can add classes to the form elements so that I can have them styled with JQueryUI and also have the ability to use plugins such as datepicker?

fbas

yes. I've done this.

1. adding classes is trivial. add a "cssclass" attribute to your editable init object
[code]
{ // qaid
placeholder : ' ',
indicator: '',
tooltip: 'Dbl Click to edit',
onblur: "submit",
cssclass : 'qaid'
},
[/code]

2. use .live() to attach your datepicker or other validator/formatter
my example uses an auto-suggest box, but changing it to a datepicker is trivial
[code]
$(".qaid").live("focus", function() {
// autosuggest for pub code
var options = {
script:"qa_code.php?allowspace&",
varname:"id",
json:true,
shownoresults:false,
offsety: "30px",
maxresults:6,
minchars: 2,
timeout: 20000,
cache: false
};

i = 0;
$('input', this).each( function () {
id = 'pubcode_'+i;
this.id = id;
i++;
new bsn.AutoSuggest(id, options); // attach auto-suggest to this element with options
});
});
[/code]

fbas

I had intended to write up a walkthough/tutorial of the jeditable plugin on my blog. Maybe it's time I get around to it.

jcrawford

ok i seem to be getting this and i am not quite sure why. All data is showing in my table properly also all data is posting properly to the add endpoint.

[code]
DataTables warning (table id = 'promos'): Requested unknown parameter '9' from the data source for row 3
[/code]

jcrawford

also quick question on the add new record dialog. Is there a way to spice that up so that I could have 2 columns side by side or is that all auto done by jQuery UI?

jcrawford

Also I am not sure how to convert that code to date picker, the only way I have found it to be created is $('.class').datepicker(options);

I tried this.datepicker(options) but that said there was no datepicker method on this.

fbas

Not sure what's causing your "unknown parameter '9'" error. can't say without seeing a link and the debugger.

I had actually never used the jeditable add record dialog. I made my own (using fancybox to display my add dialog page) before I realized that the jeditable plugin provided anything. You can completely customize your own dialog if you're willing to write your own.

I used fancybox to open a modal window containing an iframe. the iframe source points to another server page of mine (so the add record dialog is whatever HTML/javascript I put in it) with some logic to close the fancybox container when cancelled or complete.

fbas

Depends on which datepicker product you are using and how they require you to invoke it. Do you have a link to the datepicker source or API?

for example, in my project I used keith wood's datepicker v 4.0.6 and Josh Bush's masked-input. http://keith-wood.name/datepickRef.html and http://digitalbush.com/projects/masked-input-plugin/

I invoke it like this (applying a mask as well as the calendar picker):
[code]$('#daterecv_start').datepick({ "dateFormat": "yyyy-mm-dd" }).mask('9999-99-99');[/code]

jcrawford

oh see i thought you had to invoke it with new like you did.

This is the code I used that made it work :)

[code]
$(".datepicker").live("focus", function() {
var options = {
dateFormat: "yy-mm-dd",
};

$('input', this).datepicker(options);
});
[/code]

now one issue I am having is when I try to use the selectmenu plugin. I inject it just like I did for the date picker, however it does not go away when I click away to force a blur or even when I tried making onchange: 'submit' in the code.

Here is the code I am using to create the item

[code]
$(".promoTypeCol").live("focus", function() {
$('select', this).selectmenu({
speed: 400,
maxHeight: 400,
menuWidth: 100,
width: 100,
});
});
[/code]

here is my full table code, the promo type column is col 1

[code]
$(document).ready( function() {
var oTable = $('#promos').dataTable( {
"bProcessing": true,
"bFilter": false,
"bServerSide": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "/admin/mktoolpromotionsajax/",
"aaSorting": [[ 1, "desc" ]],
"oLanguage": {
"sZeroRecords": "No Promotions Found."
},

"sDom": '<"H"Tfr>t<"F"ip>',
"oTableTools": {
"sSwfPath": "/admin-assets/TableTools.swf",
"aButtons": [
"copy", "print",
{
"sExtends": "collection",
"sButtonText": "Save",
"aButtons": [ "csv", "xls", "pdf" ]
}
]
},

"fnServerData": function( sSource, aoData, fnCallback) {
$.getJSON(sSource, aoData, function(json) {
if(jQuery.isEmptyObject(json)) location.href = '/admin/login/';
else fnCallback(json);
});
},
} ).makeEditable({
sUpdateURL: '/admin/mktoolpromotionseditajax/',
sAddURL: "/admin/mktoolpromotionsaddajax/",
sDeleteURL: "/admin/mktoolpromotionsdeleteajax/",

oAddNewRowButtonOptions: {
label: "Add...",
icons: { primary: 'ui-icon-plus' }
},
oDeleteRowButtonOptions: {
label: "Remove",
icons: { primary: 'ui-icon-trash' }
},
oAddNewRowOkButtonOptions: {
label: "Confirm",
icons: { primary: 'ui-icon-check' },
name: "action",
value: "add-new"
},
oAddNewRowCancelButtonOptions: {
label: "Close",
class: "back-class",
name: "action",
value: "cancel-add",
icons: { primary: 'ui-icon-close' }
},
oAddNewRowFormOptions: {
title: 'Add a new promotion - form',
show: "blind",
hide: "explode"
},
"aoColumns": [
null,
{
cssclass: 'promoTypeCol',
type: 'select',
data: "{0:'Select', 1:'coins-fif', 3:'bucks-fif'}",
onchange: 'submit',
fnOnCellUpdated: function(sStatus, sValue, settings){
$.jGrowl('Saved Promotion Type.');
}
},
{
fnOnCellUpdated: function(sStatus, sValue, settings){
$.jGrowl('Saved Promotion Name.');
}
},
{
fnOnCellUpdated: function(sStatus, sValue, settings){
$.jGrowl('Saved Promotion Code.');
}
},
{
cssclass: 'datepicker',
onblur: 'submit',
toolTip: 'Double click to edit.'
},
{
cssclass: 'datepicker',
onblur: 'submit',
toolTip: 'Double click to edit.'
},
{
cssclass: 'datepicker',
onblur: 'submit',
toolTip: 'Double click to edit.'
},
{},
{},
{}
]
});
[/code]

fbas

I don't think there's such a value as onchange in jeditable

but you can set onblur to either 'submit', 'cancel', or 'ignore'

see "Miscellaneous Options" on http://www.appelsiini.net/projects/jeditable

jcrawford

here is my json output for diagnosing the DataTables error above. I think it may have something to do with the row id element I added?

[code]
{
"sEcho": 1,
"iTotalRecords": "4",
"iTotalDisplayRecords": "4",
"aaData": [
{
"DT_RowId": "6",
"0": "6",
"1": "coins-fif",
"2": "Another Test Promo",
"3": "test393982",
"4": "2011-10-30 00:00:00",
"5": "2011-11-30 00:00:00",
"6": "2011-11-30 00:00:00",
"7": "100",
"8": "1",
"9": "1"
},
{
"DT_RowId": "1",
"0": "1",
"1": "bucks-fif",
"2": "testing this",
"3": "45dfg9809fgd",
"4": "2011-10-20 12:00:00",
"5": "2011-10-31 00:00:00",
"6": "2011-10-31 12:00:00",
"7": "20",
"8": "80",
"9": "10"
},
{
"DT_RowId": "3",
"0": "3",
"1": "bucks-fif",
"2": "Joe's Test Again",
"3": "sdlfskj38472",
"4": "2011-10-19 12:00:00",
"5": "2011-11-04 12:00:00",
"6": "2011-10-31 12:00:00",
"7": "500",
"8": "1",
"9": "2"
},
{
"DT_RowId": "7",
"0": "7",
"1": "bucks-fif",
"2": "My Test",
"3": "test29292",
"4": "2011-10-31 00:00:00",
"5": "2011-11-15 00:00:00",
"6": "2011-11-15 00:00:00",
"7": "200",
"8": "1",
"9": "50"
}
]
}
[/code]

jcrawford

@fbias,

I did try onblur: "submit", when using JQUery UI nothing happens when the component loses focus, it just remains displaying in the table row.

jcrawford

I should add that I ONLY get the following error when I click the button which posts the form to my ajax which adds a record. Not quite sure why it is happening only then.

The add script is just supposed to echo out the id of the newly created row which I have done
everything works upon loading the page. Only issue is seeing this error:

[code]
DataTables warning (table id = 'promos'): Requested unknown parameter '9' from the data source for row 3
[/code]

It's always parameter 9 and the row is the id of the row created.

fbas

I wonder if perhaps the editable plugin is not compatible with the object form of aaData.

or if it's converting the column fields to numbers before trying to access them using numeric keys.. ?

Is there a reason you aren't using the array form? If you use the array form, DT_RowId would not be specified but it would take the first value, which is what you were using anyway.

jcrawford

AHHHHH The issue seems to be because my table has the row ID column showing, in the add form I am not passing the ID through so I only have elements 1-9 rather than 0-9 as I am not posting the id to the add action because that is generated by MySQL. I wonder if it is not compatible with rows that show the ID?

jcrawford

yep I had to add a hidden form field to the form to make it work properly

[code]

[/code]

jcrawford

ok so there are still 2 small issues I need to figure out how to fix.

1.) When using jQueryUI selectmenu the onblur: 'submit', no longer submits the data to the server
2.) When using jQueryUI datepicker for a text field, as soon as I click the arrows to switch months in the datepicker the edit field goes away but the date picker stays up.

I am not quite sure what to do about issue #1 above. For issue #2 I think I have to use 'ignore' for the blur method and use a submit button, that would fix the date picker issue but I don't like needing a submit button for every column, any other way around this?

What about issue #1?

fbas

try capturing the selectmenu's select event and triggering a "change" or "blur"

http://www.w3hobbyist.com/javascript-ajax/jquery-ui-selectmenu-callback-examples/

[code]
$('#my_select_element').selectmenu({
select: function(event){
$(event.target).trigger('blur');
}
});
[/code]

jcrawford

fbas,

This worked great for the drop down. Any thoughts on how I can make the date picker work in a similar fashion? Currently I have to have the following for my date pickers:

[code]
{
cssclass: 'datepicker',
onblur: 'ignore',
toolTip: 'Double click to edit.'
},
[/code]

I have to have on blur be ignored otherwise they cannot click to change from month to month without it submitting the form because the month change buttons are outside the text box which triggers the on blur.

I am not sure if there are other ways to make this work as I thought about making the component only submit with the on select event for the date picker, however I am not quite sure how I would get the form element to force a submit because I can't seem to inspect the element in Firebug without it going back to static form where I cannot see the form to see if there is an id or class in which I can grab it by.

I think if I can get the form and force it to submit only on select then that should work for me.

jcrawford

Also is there a way to have editable work on an entire row rather than column by column? I think it would make more sense to double click the row and have the entire row be editable with a save button.

Going to have an admin action log and having an entry for every column edited doesn't make too much sense :)