MooDataTable

So what exactly is this?

MooDataTable is, as the name suggests, a Data table for MooTools 1.2. Some of its features includes:

  • Smart Pagination
  • Column Sorting
  • Request data via Ajax, one page at a time
  • Works with any Server-Side Script, as long as it returns a JSON dataset
  • Easy to style via CSS

What does it looks like?

Yes, of course, a Demo! You can see it in action here.

How does it work?

A General idea

Well first, here's a general idea of how it works, then I'll go into details… Basically, the script sends a request to a server-side script of your choice (php, ruby etc..) with the following parameters: page, perPage, sort, sortOrder… This is all you need to be able to grab the data from a database, and return a JSON dataset, that MooDataTable will then parse, and display!… It's pretty simple, as long as the JSON you return is correctly formatted.

Step by Step instructions

So, what do you need to do in order to create a new MooDataTable? It's quite easy… just follow these simple steps…

Creating a MooDataTable object…

First, you need to include all the required scripts and CSS, then you need to create a MooDataTable object. This is what it should look like:

<head>
    <script src="mootools-1.2-core-nc.js" type="text/javascript" charset="utf-8"></script>
    <script src="mootools-1.2-more.js" type="text/javascript" charset="utf-8"></script>
    <script src="moodatatable[ver].js" type="text/javascript" charset="utf-8"></script>
    <link rel="stylesheet" href="css/moodatatable.css" type="text/css" media="screen" title="no title" charset="utf-8" />
</head>

Obviously [ver] should be replaced with the version of MooDataTable you are using… Next, you have to create a new MooDataTable object, like this:

window.addEvent('domready', function() {
    myDataTable = new MooDataTable('myTable', {
        headers: [] // your headers.. (see below) 
        // more optional options...
    });
});

There, you have it! You created a Data Table! It was simple wasn't it? Now, here's a detailed list of every parameters and options that the constructor accepts:

Constructor Parameters

The constructor takes to parameters: element and options

element
(string or HTML object) The id of a Div element or the element itself, where you want the data table to appear.
options
(object) The options object (described below)

Constructor Options Object

Here's a list of all acceptable options with their descriptions…

url
(String, Required) The url of the server-side script that will receive the ajax requests.
method
(String, Optional, Default: 'post') The ajax request method ('post' or 'get').
perPage
(Number, Optional, Default: 15) The number of results to display per page.
headers
(Object array, Required) An array of objects representing your column headers, look at the example below to see how it should be formatted.
width
(Number, Optional, Default: 500) The width of the Data Table.

Headers option

The "headers" option is where you will define the column headers, by passing an array of object.. the format is really simple (all of these are required):

id
(String, Required) This is the id of your header, i.e. The name you gave to this column in your Database.
caption
(String, Required) This is the caption, it will be used in the table header so, basically this is the "pretty name"… if your id is "client_email", the caption could be something like: "Client Email"…
sortable
(Boolean, Required) Whether you want this column to be sortable or not.

And, that's pretty much all there is to it! Here's an example using all the options:

myDataTable = new MooDataTable('myTable', {
    url: "post.php",                                   // The url of my ServerSide script
    method: "get",
    headerIds: [
        {id: "id", caption: "ID", sortable: true},
        {id: "name", caption: "Name", sortable: true},
        {id: "email", caption: "Email", sortable: false}
    ], 
    perPage: 15,
    width: 550
});

Requesting the Data

This is the most important part of all… what exactly is being sent to your server-side script, and what are you expected to return…

What is being sent to your script

First, here's a list of all the parameters that are being sent to your script (either via GET or POST).

page
The active page number
perPage
How many records to display per page
sort
The column ID (from the headerIds array) used to sort the data.
sortOrder
The sort order (eighter "DESC" or "ASC")

A complete example would look like this: page=3&perPage=15&sort=name&sortOrder=DESC

Note: If you are sending the data via get, an additional parameter (named "bust") will be added.. this is only a timestamp and is used as a cache buster for IE…

What should you return

You are expected to return a JSON object with the following information:

total
The total amount of records
page
The current page
rows
The rows (an array of string arrays).

Here's a valid JSON result:

{
    "total": 220,
    "page": 2,
    "rows": [
        ["100", "John Doe", "lorem@ipsum.org"],
        ["101", "Jane Doe", "lorem@ipsum.org"],
        ["102", "Bobby Doe", "lorem@ipsum.org"],
        ["103", "Chuck Doe", "lorem@ipsum.org"]
    ]
}

If any of the properties are missing, you will get an error. Also note that, your row array should contain the same amount of element as your headerIds and headerCaptions arrays (i.e. if you have 3 column headers, you should have 3 cells per rows!).

And, that's it! It's pretty much everything you need to know to make it work! As for your server-side script, well here's a simple example that would do the job… adapt it to your data obviously (Note: This is a php example, but it could be any language you want)

<?php
        // Connect to your database
    $db = mysql_connect($dbhost, $dbuser, $dbpass);
    mysql_select_db($dbname, $db);
 
        // Grab the total amount of results...
    $entriesCount = mysql_query("SELECT COUNT(*) FROM `dummy`");
    $counta = mysql_fetch_row($entriesCount);
    $count = $counta[0];
 
        // Grab the important variables
    $page = $_GET['page'];
    $perPage = $_GET['perPage'];
    $sort = $_GET['sort'];
    $sortOrder = $_GET['sortOrder'];
    $start = ($page * $perPage) - ($perPage);
    // Execute your mysql query...
    $entries = mysql_query("SELECT * FROM `dummy` ORDER BY $sort $sortOrder LIMIT $start, $perPage");
    // Format the result in JSON
    $result = "{\"total\": $count,
\"page\": $page,
\"rows\": [
";
    while($entry = mysql_fetch_assoc($entries)) {
        $result = $result . "[\"$entry[id]\", \"$entry[name]\", \"$entry[email]\"],\n";
    }
 
    $result = substr($result, 0, strlen($result)-2);
    $result = $result."\n]}";
    // Close the DB connection and echo the result...
    mysql_close($db);
    echo $result;
?>

And that's pretty much all you need to know! Now, onto style customization…

Styling the datatable

MooDataTable comes with a sample CSS file. If you know your way around CSS it should be pretty easy to customize it to your likings… Here's a list of important CSS classes and what they represent…

div.moo-table
The moo-table class will be added to your DIV element
div.moo-table table thead th.moo-active-column
This represents the column currently used for sorting.
div.moo-table table thead th.moo-active-asc
In addition to the previous class, this class will be added if the results are sorted in an Ascending order.
div.moo-table table thead th.moo-active-desc
Same as above, but for Descending sorting.
div.moo-table table tbody tr.moo-table-even
Applied to Even rows.
div.moo-table table tbody tr.moo-table-odd
Applied to Odd rows.
div.moo-table table tbody tr.moo-table-last td
You can use this selector to style the LAST row of the table (in case you want a different border for that last row etc..)
div.moo-table tfoot span.moo-foot-right span.moo-active-page
This selector represents the currently active page in the page list…

This is pretty much all the important CSS selectors you should know about. For the rest, just look at the sample CSS file that comes with MooDataTable and you should be able to customize it to your likings.

Additional Info

There is one public method that you can use and it's reloadActivePage() It can be used if, for example, you want to add a "delete row" option and you need to refresh the current page (after a record has been deleted for example). For example:

myTable.reloadActivePage();

Download

You can grab the latest version on my GitHub… I stopped offering a .zip package because I make changes to the script all the time so it's easier to just grab it on GitHub… Basically just go here:
http://github.com/SilverTab/moodatatable/ and click on the "Download" button…

Comments

You can post any comments/questions at the official blog post for MooDataTable located here.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License