Datatables and Perl (and a little bit of jQuery)

Recently I’ve stumbled on a pretty cool OpenSource project called ”datatables” (http://datatables.net/), which allows to easily create tables in HTML that can be:

• sorted
• searched
• paginated
• scroll infinitely
• themed
• …

And most important: it’s for free! I’ve always wanted to create an infinite scrolling table and now it’s just too easy:

$(document).ready( function() {
    oTable = $('#ip_data').dataTable( {
        "bProcessing":     true,
        "bServerSide":     true,
        "bPaginate":       true,  
        "bScrollInfinite": true,
        "bScrollCollapse": true,
        "sScrollY":        "200px",
        "sAjaxSource":     "get_ip_data.pl",
    } );
} );

And that’s it! Well, ok, you need to include the Javascript file and CSS files of the Datatables Project of course and you need to create the table in HTML.

For instance:

And then you will need a Perl script providing you the data for the table.
The below example allows to

• search the tables
• scroll infinitely
• sort on the columns

It also supplies the Datatables table with a total amount or rows in the database table.

This following script will be saved as ”get_ip_data.pl”

#!/usr/bin/perl
use strict; use warnings;
use DBI;
use JSON;
use CGI;

my @columns = qw/ip country_name city latitude longitude/;

my $q = CGI->new;
my $db = DBI->connect("dbi:mysql:host=localhost;db=testdb", 'testuser', 'xxxsecret');

my $params = $q->Vars;

# Get the total count of rows in the table
my $sql_count = "select count(id) from geo_data";
my $count = $db->selectrow_arrayref($sql_count)->[0];

# Start building up the database query
my @values;
my $sql = "select ip,country_name,city,latitude,longitude from geo_data";

# if a search parameter was supplied in the AJAX call, build the WHERE part in the SQL statement
if( $params->{sSearch} ){
    $sql .= ' WHERE ';
    $sql .= 'ip LIKE ? OR country_name LIKE ? or city LIKE ? or latitude LIKE ? or longitude LIKE ?';
    push @values, ('%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%');
}

# if a sorting parameter was supplied in the AJAX call, build up the ORDER BY part in the SQL statement
if( $params->{iSortingCols} ){
    $sql .= ' ORDER BY';
    foreach my $c (0 .. ( $params->{iSortingCols} -1 )){
        $sql .= ' ' . $columns[ $params->{"iSortCol_$c"} ] . ' ' . $params->{"sSortDir_$c"};
        $sql .= ','
    }
    $sql =~ s/,$//;
}

# Limit the output and also allow to paginate or scroll infinitely
$sql .= " LIMIT ? OFFSET ?";
push @values, (($params->{iDisplayLength} > 0 ? $params->{iDisplayLength} : 25), ( $params->{iDisplayStart} // 0));

# Fetch the data from the database
my $data = $db->selectall_arrayref($sql, { Slice => [] }, @values);

# Return the JSON object
print $q->header('application/json');
my $json = encode_json({ aaData => $data, iTotalRecords => $count, iTotalDisplayRecords => $count, sEcho => int($params->{sEcho}) });
print $json;

An example can be found overhere: http://www.moretrix.com/~insaniac/map/map.pl