dark

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:

IPCountryCityLatitudeLongitude
Loading data from server
IPCountryCityLatitudeLongitude
 

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

2 comments
  1. hi Johny,
    I have a question for this
    # 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));

    I know you set the table length is 25. How can I display the table length base on the user request like the original datatables option has dropdown list.

    I am looking forward to a favorable reply from you. Thank you.

  2. hi Johny,
    I have some questions about datatables relates with my problems.
    1. how can I change the table length value? In your example you set into 25. I do not know the variable name relates to the table length.
    2. how to combine data from 2 columns into 1 column and then put it in the JSON?

    I am looking forward to a favorable reply from you. Thank you.

Leave a Reply to Eko Budiharto Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous Post

Google GeoChart, JSON and Perl

Next Post

Postgresql 9.3: Creating an index on a JSON attribute

Related Posts