Datatables Ajax Pagination Not Working

DataTables is a powerful jQuery plugin for creating table listings and adding interactions to them. It provides searching, sorting and pagination without any configuration. In this article we’ll go through the basics of DataTable and how to use some of the advanced features.

1. Datatables Ajax Pagination Not Working
2. Datatable Ajax Data
3. Ajax Pagination Script

Setting up DataTables

I have implemented the ajax datatable but I can't able to use pagination. For example I have totally 11 records. Pagination size is 2 After some filtering process my 'recordsFiltered' count is 6. But it Showing '1 to 2 of 2 entries (filtered from 11 total entries)' In pagination I have only single page. It should be 3 pages.

1. Hi All, I am currently working in a project that uses datatables where the values i retrieve from DB as the no. Of records ranges to around 10k i want to use server side pagination. I had tried some but it is not working.
2. My Pagination is not working. The Bootstrap works but the records are all dumped into one page. = 5 means that I'm forcing the datatables to only display 5.
3. I love DataTables.js. It makes working with HTML tables a breeze and increases functionality such as search, sorting and pagination. DataTables.js → How to update your data object for AJAX.

The first step is to obtain the library from the DataTables website. 1.9.4 is the current stable version and 1.10 is in development now. If you don’t want to download the files, you can also make use of the Microsoft CDN. As it is a jQuery plugin you should include the jQuery library too, preferably the latest version.

Naming conventions

Before we move on, it’s useful to understand the conventions used in the library. DataTables uses Hungarian notation for naming variables, which basically adds certain prefix to its name that helps to understand type of data held by the variable.

n – Variable represents a node.
o – Variable represents an object.
a – Variable represents an array.
s – variable is a string.
b – Boolean
f – Float
i – Variable holds an integer value.
fn – Variable represents a function.

Sometimes you will see multiple prefixes used together, like ao, which represents an array of objects.

Getting started

DataTables can work with data from a verity of sources. It can directly work on an HTML table or we can specify data as an array while initialization. Or it can work on data coming from an Ajax source.

In this article we’ll create a listing of websites in the SitePoint network. We’ll start with the listing of a few site names, and then we’ll add more columns and features. Here we have an HTML table with one column that lists the names of just three sites. Let’s see what DataTables can do with a minimal setup.

Copy-paste this code to an html page and open it up in a browser. You will see a nice looking table as shown below.

Now you know the power of DataTable and what we can accomplish with it. With this most basic setup, we can search for site names from the top search box and sort them by clicking on the column name. How difficult would be if you were trying to implement those features on your own?

Here we’ve first included the DataTables CSS file in the top and Js in the bottom. $('#example').dataTable() will instantiate the DataTable on the table with the id example.

Now we need to list more sites with more details about each site. Let’s say we have a list in our hand, with details like site name, URL, type and last updated but in a JavaScript array instead of an HTML table. Type can be blog, forum or marketplace and the last updated column will have null value for sites that are not blogs.

Datatables Ajax Pagination Not Working

Here we want to show N/A instead of null values in the last updated column and make URLs a clickable link.

We’ll first create an HTML table, with the required column headings and an empty table body.

Now let’s apply DataTables to this table.

Now our table will be as shown below. We have a clickable link in the URL column of all records and the last modified column looks pretty neat.

We have given the json array using the aaData option of DataTables. If you look at the code, you’ll see a “aoColumnDefs” option passed to the DataTables, which have an array of some objects. aoColumnDefs property basically decides how each column should be rendered in the table. With this property, you can modify any arbitrary column in the table, specified by the aTargets property of the object.

The value in aTargets array can be the class name specified in the column heading, an index of the column (starting from 0 for left to right or negative index for right to left) or “_all” to match all columns of the table. “sTitle“:”Site name” for the first column will override the column heading of the first column.

Note that the sorting icon is missing in the second column, because sorting based on the URLs doesn’t make much sense. I’ve removed it by setting bSortable:false for the corresponding column. mRender is a powerful option that can be used to alter the data displayed. This attribute can be given in a number of ways ways. When used an integer value, it will use that as the index to the data array. When a string is given, it will use the property of the json object with that name.

These are useful especially when we are displaying json data returned from the server. mRender can also be a function, which will be called when rendering each cell of that column. The function will get three parameters, value, type and the entire row and it should return what we want render in its place. Our function just created a link to that URL and returned it.

Likewise for the last updated column, we are checking the value in the third column of each row. If it is a Blog, function will return the formatted date else it will return “N/A”.

The “sType” attribute helps us to specify the expected type of that column, which decides how the values will be sorted for that column. By default, DataTables supports four types: string, numeric, date and HTML. You can extend it by defining custom types. Here we’ve specified the type as “date” and hence it will be using a date based sorting. Otherwise it will consider the column as just string and sorting may not make any sense.

Server side processing

In the above examples we have worked with a plain HTML table and a json array directly given in the client side. Sometimes the database will have too many records and it may not be wise to fetch them all at once and apply DataTables to it. Instead we can use the server-side processing feature provided by the library and fetch the only records that are currently displayed in the front-end.

Each time the table is rendered, DataTables will send a request with number of parameters to the server, including the starting point, display length, search data, sorting column etc. The server can use them in an SQL query to filter the data and send them back to the client. DataTables expect a few attributes in the returned json data to display them correctly in the front-end. They are:

iTotalRecords – Number of total records in the table, before applying the filters.
iTotalDisplayRecords – Number of records returned after applying the filters.
sEcho – An un-altered copy of the sEcho sent from the client.
aaData – Array of data from the server.

And the json response from server may look something like:

In order to render this, we’ll first create an html table with the corresponding column headings.

Here we’ve just told DataTables to load the data from server by setting bServerSide to true. Once it’s enabled, we should also provide a data source, using sAjaxSource property. In the json data returned from the server, by default DataTables will look for the aaData to get the data to be displayed in the table. You can override this by specifying the attribute name in sAjaxDataProp property.

In this case we are getting an array of json objects, so we should map which key should be displayed in which column. We have used aoColumns instead of aoColumnDefs. Both do the same thing, but in a different way. When using aoColumns the length of array should be equal to number of columns in HTML table so that we should provide a mapping for each column.

In the json response editor field is an object with name, email and phone fields. “mData“: “editor.name” tells DataTables to take the name property of the editor object. Note that phone and email are arrays, which will be automatically concatenated using commas, or we can write a custom mRender function to make it look better.

Datatable Ajax Data

There is more…

Apart from the above-mentioned features, DataTables provides lots of really useful functionalities and we can extend them by using or writing our own plugins. It also provides a simple API to manipulate the table once it’s created.

This code will log value data in a row when we click on it. fnGetData basically accepts a TR/TD node or the index of row and return the value in that row or cell.

Sometimes it will be useful to save the current state of a table when we switch from one page to another page. You can enable state saving by setting “bStateSave” : false, during initialization. By default the state will be saved in a cookie. DataTables allows us to define callback function to override this, so that we can store it in Localstorage or in the server itself. Here is an example that’ll save the state in Localstorage.a

Here fnStateSave will be called to save the state as soon as the table is rendered. Next time before rendering the table, it will call fnStateLoad and load the previously stored state. Note: in version 1.10, DataTables will be using localStorage as the default store to save the state.

Summary

DataTables is a highly flexible and feature rich library to work with tables and listing. The library provides a lot of configuration options and a simple API. It is impossible to discuss all the functionalities in a single article. So we have briefly discussed some of the most useful features and how to use them.

Please try out them and let me know if you have any issues. I’ll try my level best to help.

Ajax Pagination Script

Take your jQuery skills to the next level with SitePoint’s premium subscription service. You’ll find dozens of books and courses on JavaScript and jQuery — and more added every month!

I am new to jquery datatables, I had a simple function which call ajax and map the response into datatables, that is working but , the pagination is not working properly , any suggestion would be greate. Here is my code.

I got this output , the problem is , Show entries is not working and i only have 11 record and i got 1,2,3,4 page number which should only be 1, 2 and only 10 record need to be there . What did i miss here ?

2 Answers

Try to use this property in your datatable declaration

Not the answer you're looking for? Browse other questions tagged jquerypaginationdatatables or ask your own question.