Tag Archives: dataTables

jQuery DataTables: How to Disable Sorting on a DataTable Instance Programming 18 FEB 2013

DataTables is a fantastic plug-in for the jQuery Javascript library that I use for pretty much all my web development projects. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table – in other words, it creates useful dynamic, with oodles of extra functionality, tables out of plain old vanilla HTML tables.

Today’s question is how to disable sorting on a DataTable instance when it initializes.

Turns out, this is pretty easy to achieve, simply by turning off the bSort property during the method call. In practice:

$('#campaignlinkstable').dataTable({
"bProcessing": true,
"bSort": false
});

Yup, pretty much as easy as that.

(Note that you can further refine your sorting by turning bSort back on and making use of the aaSorting column definition.)

Nifty.

How to Refresh a DataTable without Losing Your Current Page or Ordering CodeUnit 28 MAY 2012

DataTables is a fantastic plug-in for the jQuery Javascript library that I use for pretty much all my web development projects. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table – in other words, it creates useful dynamic, with oodles of extra functionality, tables out of plain old vanilla HTML tables.

Anyway, there is often a need to be able to refresh your datatable after applying some action to the source data, and the usual method of forcing a refresh is by calling the standard fnDraw() function on the table object. However, whilst this will redraw the table, it will basically reset you completely, removing any filtering and jumping back to page 1 if you have a table with pagination.

So not ideal.

Thankfully one of the DataTables forum guys came up with a handy bit of extension code which introduces a new function called fnStandingRedraw() which extends the datatables API and allows you to fresh the datatable without losing your current view.

To implement, simply put the following code into a .js file and load it after you load the standard datatables.js library:

$.fn.dataTableExt.oApi.fnStandingRedraw = function(oSettings) {
    //redraw to account for filtering and sorting
    // concept here is that (for client side) there is a row got inserted at the end (for an add)
    // or when a record was modified it could be in the middle of the table
    // that is probably not supposed to be there - due to filtering / sorting
    // so we need to re process filtering and sorting
    // BUT - if it is server side - then this should be handled by the server - so skip this step
    if(oSettings.oFeatures.bServerSide === false){
        var before = oSettings._iDisplayStart;
        oSettings.oApi._fnReDraw(oSettings);
        //iDisplayStart has been reset to zero - so lets change it back
        oSettings._iDisplayStart = before;
        oSettings.oApi._fnCalculateEnd(oSettings);
    }
     
    //draw the 'current' page
    oSettings.oApi._fnDraw(oSettings);
};

Now on your page code, you can simply ask for a refresh by doing this:

//var oTable1 = $('#mytable').dataTable();
oTable1.fnStandingRedraw();

Nifty.

Related Link: http://datatables.net/

jQuery DataTables: How to destroy a DataTable instance CodeUnit 16 MAY 2011

I really do love the awesome DataTable plugin which creates useful dynamic, with oodles of extra functionality, tables out of plain old vanilla HTML tables. Today’s quick tip however looks at how one destroys an already instantiated DataTable instance, say because you want to recreate it at a later stage in a completely different format but want to reuse the selector currently bound to the existing table.

Enter the useful bDestroy option which you can add to your constructor. Given this option set to true, DataTables with first brutally murder any existing elements attached to the selected selector before recreating the desired DataTable. If no existing table is found attached to the given selector, then the DataTable is created as it normally would.

An example:

$(document).ready(function() {
	$('#example').dataTable( {
		...
	} );
	
	/* Some time later.... */
	$('#example').dataTable( {
		...
		"bDestroy": true
	} );
} );

Simple and very useful. In other words, nifty.

Related Link: http://www.datatables.net/usage/options

jQuery DataTables with Lots of Columns Crashes in IE CodeUnit 16 FEB 2011

If you are using the brilliant jQuery DataTables plugin to present your data in nifty dynamic tables, and are using it in a server-side loading context, you have no doubt encountered the issue when a table with a lot of columns (more than 20) fails to load (in other words stays in “processing” mode) when browsing using Internet Explorer (IE).

Mysteriously enough, it does however work in both Firefox and Chrome.

Although difficult to debug, the issue at hand is actually quite simple – essentially IE’s built-in GET size limit is kicking in at the most inopportune of times, meaning that incomplete JSON is heading back towards your page.

The simple solution is to force DataTables to use POST instead of GET for your server-side table, and to do this you need to add the following to your datatable definition:

$(document).ready(function() {
	$('#example').dataTable( {
		"bProcessing": true,
		"bServerSide": true,
		"sAjaxSource": "server_processing_post.php",
		"fnServerData": function ( sSource, aoData, fnCallback ) {
			$.ajax( {
				"dataType": 'json', 
				"type": "POST", 
				"url": sSource, 
				"data": aoData, 
				"success": fnCallback
			} );
		}
	} );
} );

As you can see from the above, the fnServerData variable allows you to specify the type of the ajax call being used, which means we can now force it to POST and thus eliminate our Internet Explorer large column table problem for good!

Nifty.

Related Link: http://datatables.net/

jQuery DataTables: How to Force Specific Column Widths CodeUnit 16 AUG 2010

I’ve mentioned the wonderful jQuery plugin DataTables a number of times before, the awesome little trick that instantly transforms any HTML table fed to it into a fully sortable, paginated, searchable and zebra-striped table, requiring the most minimum of coding to implement.

Today’s tip looks at how you can specify what column widths to use in your DataTables implementation, instead of relying on the default auto column widths assigned by the plugin.

Firstly, you need to turn off DataTable’s built in auto column width calculator by feeding it a false value for the bAutoWidth switch when initialising your DataTable object. Next, you need to feed the column widths you wish to use directly into the initialization by means of the aoColumns definitions, making use of the sWidth switch to specify the width.

Looking at this in code, you should now have:

$('#activitylog').dataTable( {
"bAutoWidth": false,
"aoColumns": [{"sWidth":"60%"},{"sWidth":"20%"},{"sWidth":"20%"}]
} );

Nifty.

Related Link: http://datatables.net/

jQuery DataTables: How to Use jQuery ThemeRoller CSS Styling on Your Table CodeUnit 11 AUG 2010

Widget styling is a long, laborious and tedious job for the most part, which is exactly why it is so great that the guys behind jQuery UI came up with the ThemeRoller concept which basically enforces a strict set of rules in terms of generic widget class styling.

As a bonus, the awesome jQuery DataTables plugin (which I religiously use on all my work-related HTML tables nowadays) comes with the option to enable jQuery ThemeRoller styling with a simple switch of an initialisation parameter!

To enable jQuery UI ThemeRoller support in your datatable, simply add the following option during construction:

$(document).ready( function() {
	$('#example').dataTable( {
		"bJQueryUI": true
	} );
} );

And that is all there is to it. Make sure you include a ThemeRoller stylesheet link in your page and your nifty DataTables implementation should be using all those nifty ThemeRoller styling elements!

Nice! :)

jQuery: Bind an onClick Event to an Item in a DataTables Row CodeUnit 14 MAY 2010

As it has quickly become apparent on this site, my two current favourites in the land of web development is the fantastic jQuery javascript library and the brilliant jQuery-based DataTables plugin that gives you instant dynamic table features applied to any bog standard HTML table.

The problem that I’m solving today arose from my desire to include a positive/negative flagging system to data being displayed in a DataTables view. So in each data row I added a column that contained both a greyed out happy and sad smiley face, on which the user will be able to click in order to color the appropriate face and thus indicate a state for that particular row of data.

The idea is simple enough. Click on the smiley face and the script will then fire off an ajax post to a script handler which will fiddle in the database and return the correct state which in turn changes the image accordingly – pretty standard AJAX stuff in other words.

However, herein lay the problem.

Because the DataTables was being fed through a server-side script, in other words the actual table data is loaded by the DataTables plugin itself, my jQuery function that was supposed to interact with the clicking of a smiley face couldn’t work because the elements it was meant to interact with simply didn’t exist when the page’s initial DOM tree was being loaded.

So how to solve this?

Well the solution obviously lies in binding the newly created elements to my existing click handler function, but the question remains – how exactly does one do this if the table data is being loaded through a jQuery function?

Well the answer lies with the DataTables-provided callback function entitled fnDrawCallback, which is called on every ‘draw’ event – in other words the perfect little hook to attach your jQuery code, which is to interact with the DataTables-loaded DOM data, to.

So how does one use this callback functionality then? Well the magic all gets stuffed into the DataTables constructor function. First, we declare the function that is to be run on the click event happening. Then, we bind this function to the click event for the desired elements in the standard jQuery manner. And then for the crucial point, this binding is done as part of the fnDrawCallback declaration in your dataTable constructor call.

So let’s see this in action:

//onClick handler function
function flagsmileysadclick()
{
	$(this).load('ajax_set_smiley_flag.php',{'smileyid':$(this).attr('id')});
}

//DataTables constructor
oTable = $('#commentstable').dataTable({
"bProcessing": true,
"bServerSide": true,
"iDisplayLength": 50,
"bLengthChange": false,
"sAjaxSource": "datatables_comments_list.php",
"sPaginationType": "full_numbers",
"aaSorting": [[ 0, "desc" ]],
"fnDrawCallback": function() {
  //bind the click handler script to the newly created elements held in the table
	$('.flagsmileysad').bind('click',flagsmileysadclick);
}
});

And that’s actually it. Simple, but effective, and a nice introduction to the handy fnDrawCallback feature of DataTables.

How to Disable Sorting on a Column in jQuery Datatables Plugin CodeUnit 19 APR 2010

I’ve mentioned the wonderful jQuery plugin Datatables a number of times before, the awesome little trick that instantly transforms any HTML table fed to it into a fully sortable, paginated, searchable and zebra-striped table, requiring the most minimum of coding to implement.

Today’s quick tip is on how to disable sorting on specific columns, in the event when it simply doesn’t make sense for a user to be able to sort by that particular column in your table.

As per usual, the trick lies in setting this up at table initialization, making use of the handy bSortable property that can be applied to a column. The default value for this property is true, meaning you can specifiy it as null and it will remain sortable, but if you wish to remove the ability to sort then you need to explicity set it to false for that column. Seeing this in action, let’s have a look at the example below. If you can imagine that we have a four column table, I’m now going to turn off sorting for column three: 

$('#example').dataTable( {
"aoColumns": [
null,
null,
{ "bSortable": false },
null
]
} );

And that’s really it. Pretty simple, after all!

To protect this man's sanity, turn off sorting now!

Related Link: http://datatables.net

jQuery DataTables: How to Sort by a Specific Column on Load CodeUnit 04 APR 2010

I somehow missed this breezing through the available documentation on the DataTables website, but basically the question arose on how to force a table that has just been loaded (with Datatables applied naturally) to be sorted in a particular manner by default?

Well it turns out that the answer to this question is pretty simple indeed.

When constructing your dataTable object you can pass it an initializer parameter named aaSorting which essentially allows you to set which columns to sort on and also which direction to sort in.

Take a look at this sample dataTables initialization below:

$('#example').dataTable( {
		"bProcessing": true,
		"bServerSide": true,
		"iDisplayLength": 150,
		"bLengthChange": false,
		"sAjaxSource": "reservations_list.php",
		"aaSorting": [[ 5, "desc" ]]
	} );

If you look at the last parameter being fed to the constructor you’ll spot our aforementioned aaSorting being sneaked in there with an array value that is setting column six to be ordered in a descending fashion.

(You’ll note I say column six because obviously this is array based manipulation and as we all know, javascript utilizes zero-indexed arrays.)

And that is pretty much it. aaSorting allows us to set the default sorting pattern for any dataTables object.

Nice.