Using DataTables In Drupal 7

The DataTables plugin for jQuery offers enhanced interaction for standard HTML tables. Developers are able to create highly interactive tables with dynamic sorting, filtering and pagination without having to write custom server side code. As always with jQuery plugins, there is a module that integrates the plugin with Drupal. The DataTables Drupal module integrates the plugin with Views. The Views integration allows developers to create “DataTables” tables with just Views and no custom code. The module also offers a theme function called theme_datatable($variables) for rendering tables programmatically.

In this tutorial, we’ll create an interactive table using the Views and DataTables module.

Getting Started

Download and install DataTables and Views module.

Download the DataTables plugin from their download page (1.9+). Extract the file directly into the DataTables module. The path to the plugin should be sites/all/modules/datatables/DataTables.

Fig 1.0

Views Integration

Now that we have everything setup, let’s go into Views and create a table.

Steps

1. Go to Structure -> Views (admin/structure/views) and click on the “Add new view” link.

2. Fill in the “Add new view” form with the value defined in Table 1.0.

Table 1-0. Create a new view

Option Value
View name DataTable
Machine name datatable
Show Content (default)
Of Type All (default)
Create a page Checked
Page title DataTable
Path datatable
Display format DataTables

Fig 1.1

Once you have filled in the form click on the “Continue & edit” button at the bottom of the page.

2. Add a few fields to the table. For this tutorial, I’ll add the Node Id, Title and Type field.

Fig 1.2

3. Click on the Full link in the Pager section and change the paper to “Display all items”. We need to turn off the Views pager because DataTables implements its own pager.

Save the view by clicking the Save button in the top right corner.

4. Go to /datatable to test out the view. If everything is working, you should see a table with pagination and a search filter.

Fig 1.3

5. Return back to the edit page for the view and click on the Settings link next to DataTables in the Format area.

Fig 1.4

6. Check the Sortable checkboxes for all the columns so you can sort each column.

Fig 1.5

7. Once you have finished configuring the table, scroll to the bottom and click on the “Apply (all displays)” button, and then save the view.

8. Refresh the views page, and now you should be able to sort by each column.

Fig 1.6

This is a simple use case if you want to learn more, go to the settings section and play around with some of the other options.

Programmatically Render Tables

In the last section, you saw how easy it was to create a table using Views. Now we’ll look at rendering a table programmatically. Rendering a table is pretty easy, the module offers a theme function called theme_datatable($variables). All we need to do is define an array in $variables, and that’s it.

In the example below, you can output the table in two ways. Using Drupal’s Render API or using the theme('datatable', $variables); theme function.

// Table settings.
$attributes['datatable_options'] = array(
  'bFilter'   => TRUE,
  'bInfo'     => TRUE,
);
// Define table columns
$header = array(
  array(
    'data' => t('Column 1'),
    'datatable_options' => array(
      'bSortable' => TRUE,
      'bSearchable' => TRUE,
    ),
  ),
  array(
    'data' => t('Column 2'),
    'datatable_options' => array(
      'bSortable' => TRUE,
      'bSearchable' => TRUE,
    ),
  ),
);
// Table data.
$rows = array(
  array('test1', 'test2'),
  array('test2', 'test3'),
  // ...
);

// Render using Drupal's render API.
$build['datatable'] = array(
  '#theme' => 'datatable',
  '#header' => $header,
  '#rows' => $rows,
  '#attributes' => $attributes,
);

// Or, render using a theme function.
$variables = array(
  'attributes' => $attributes,
  'header' => $header,
  'rows' => $rows,
);
$output = theme('datatable', $variables);

If you have any questions, please leave a comment.

Ivan Zugec

About Ivan Zugec

Ivan is the founder of Web Wash and spends most of his time consulting and writing about Drupal. He's been working with Drupal for 10 years and has successfully completed several large Drupal projects in Australia.

49 thoughts on “Using DataTables In Drupal 7”

  1. Hi , great tutorial, and really nice plugin.
    It took a while to get it to work in D7alpha due to confused casing of the install paths (dataTables vs DataTables ). I changed install file that caused the errors so it to worked pretty much as described in this tutorial . Thanks for the tips!

  2. Simple datatables is nice. But the real power of the DataTables jQuery module comes into play when working with larger datasets that would be painful to download in one gulp.

    I would LOVE to gain access to those kinds of DT features from within Drupal. Any idea how to get there?

    Thx,
    MrPete

    1. Unfortunately I don’t think you can do that with the module. You’ll have to implement DataTables yourself with custom JavaScript code.

  3. I found the editable views to be far superior to that to be honest. I just need a version that combines with datatables and VBO so I get full functionality. I am looking to emulate a spreadsheet as much as possible. Thanks for the comment.

  4. Hi friends,I want to implement individual column search to my datatable. i have been trying,but iam unable to get it.please help me how to implement individual column search for the datatable.my column names are eno,ename,subject.these data should come from database.

  5. Emmanuel Rivera

    Hello Ivan, nice read. I’m in the process of deciding what CMS I should use for a client. A factor that’s playing a strong hand in the decision is the integration of a third party database called DaCdb, and I was wondering if “Data Tables” has the same functions as the WordPress plugin called Table Press?

    1. I have never used TablePress before so I can’t give you my opinion about it. The best way to research both solutions is to install Drupal and WordPress, and see which table plugin is better.

  6. Thanks Ivan, that was a great tutorial.
    I’m fairly new to Drupal. After creating the basic website, my simple requirements are as follows:

    – Create a table with multiple columns, to hold data
    – Users would be able to view this Table, according to their permissions, be able to Add/Edit their OWN data (It’s added as a new row to the table)
    – Users should not be able to view any other user’s data (or Row of data in the Table)

    However simple this requirement looks, I wasn’t able to find any straightforward tutorial or solution for implementing this. Or maybe I was overwhelmed by the available options of modules.

    Would really appreciate guidance on this!

    Cheers!

    1. This can be achieved in a few ways. Here’s how I would tackle each point.

      *Create a table with multiple columns, to hold data*

      Create a custom content type and attach some fields to it. Think of a “table” as a content type and “columns” as the fields.

      *Users would be able to view this Table, according to their permissions, be able to Add/Edit their OWN data (It’s added as a new row to the table)*

      Once you have defined a content type and its fields, then use Views (https://drupal.org/project/views) to display the data as a table.

      You can control who has access to create and edit content by going to the “Permissions” page. Drupal supports “Edit own content” out of the box.

      *Users should not be able to view any other user’s data (or Row of data in the Table)*

      This can be handled in Views by using a contextual filter. You can create a view that’ll only display data that is been authored by the current logged in user.

      This means a user will only ever see their content if you add “Content: Author uid” as a contextual filter.

      There’s one thing you need to be careful of. By default, Drupal allows anonymous access to published content (permission: View published content).

      If you only want the author to access this data, then make sure users can’t access to data by going to ‘node/1’ for example.

      Look at the following two modules if you have custom permission requirements:

      – < https://drupal.org/project/acl>
      – <
      https://drupal.org/project/content_access>

      Hope this helps.

  7. Nice blog!
    I have seen on datatables web site that I can make the columns editable.
    How can I have this feauture using Drupal?
    How can I have this feauture using the above example?
    Bye!

    1. The Drupal module for “DataTables” doesn’t support editable columns. If you want editable columns, you’ll have to implement using custom code.

  8. My compliments to the website and the practical tutorials 🙂

    How can I implement calculations in the footer of the tables? Like sum, average, max, min etc.?
    I’ve implemented the table and it’s very nice even when working with groups.

    Thanks in advance!

      1. What a quick reaction!
        I’ve tried that already but It’s no working for me. Seems to be broken in D7.3. Ik get only 0 for all calculations.

        The looks of datatables is very nice, would like to have it in that. I just hoped it was possible.

        Thanks 🙂

        1. It’s a shame that Views Calc doesn’t work. Must be some compatibility issue with Datatables.

          Thanks for letting us know.

  9. I tried to follow the steps in the examples under drupal 7. Everything seems to work, except the columns do not show the sort mark and cannot be sorted, even they are all enabled (SORTABLE checked). Any help ?

    I also tried with Programmatically Render example, with 25 rows and attributes :
    ‘bScrollY’ => TRUE, ‘sScrollY’ => ‘200px’, ‘bPaginate’ => FALSE, but I don’t see a scroll window. Any help also ?

  10. Hi Ivan. Thanks for the informative article. I replicated what you have done above. Just wanted to ask about how we can put an “add” “delete” and “update” buttons to the view.
    I know about the New Content Type method but I have like 40 fields . Could be done in practice but does not seem to be the best way.

    1. Hi Omar,

      Have you tried adding the “Content: Edit link”? This field should give you the edit link.

      Cheers,
      Ivan

  11. Hi Ivan,

    Thanks for your blog, excellent quality.

    When I apply point 3:

    “3. Click on the Full link in the Pager section and change the paper to “Display all items”. We need to turn off the Views pager because DataTables implements its own pager.”

    I get a timeout when I display the view. The view returns 6000 lines, without pagination, can not be displayed.

    I see this option on the site “datatable” -> Deferred rendering for speed.

    But I was not born or inserted this code, do you have an idea?

    Thank you

    Thank you

  12. Hey, really clean and helpful tutorial.

    I have a question if you can help me.

    I installed datatables module on my drupal 7 website, but i have a custom html table that is generated onClick event from highcharts graph when user click on “View Data table” option, Please guide me how can i call datatable() function on this table so that my table is displayed nicely with all datatables options like search, sorting, pagination etc. ?

    Please note that this is not view or something in theme, this table is generated on jQuery onClick event, but i have full access to jQuery code that is creating this table.

    Please help….

    Thanks,

  13. Hi IVAN ZUGEC,
    I imported a CSV Excel file into my table.
    Now how can i apply pagination,search,sort and header/footer into the Table.
    I changed the page Views into Datatable and applied just what u said.
    But still nothing changed.

    1. Hi Sankar,

      You’ll need to debug this further. Without seeing the problem it’s hard for me to help.

      Look for PHP errors, JS errors or any type of errors.

      Cheers,
      Ivan

        1. Sorry I’m a bit confused, you added the same comment.

          “I did exactly in the video. Its nice.
          But after that how to add Datatable properties like pagination,search and other options into that table.
          Please tell me how to do.”

          Sorry but you’ll need to debug this further yourself. As I commented above:

          “Look for PHP errors, JS errors or any type of errors.”

        1. I do see the lack of a pagination and search box, but I do not know why it’s not appearing. There could be a lot of reasons why it’s not working:

          – Views not configured properly
          – Problems with the module
          – Problems with DataTables
          – JS errors
          – PHP errors

          Sorry but you’ll need to debug this further yourself.

          Here’s how I’d start debugging it:

          1. Look for JavaScript errors
          2. Look for PHP errors
          3. Search the DataTable issue queue for drupal.org for similar problems
          4. Read the documentation for the DataTables module (this article could be out of date)
          5. Search https://drupal.stackexchange.com/ for similar issue

  14. I used datatable to view. It’s okay for small data, but when we use for about 4000 rows. The view can’t load.
    How can I solve the problem?
    I used cache view, but not better.
    here is my error:
    Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 30133902 bytes) in docroot/includes/database/database.inc on line 2227

Leave a Comment

You have to agree to the comment policy.