DataTables logo DataTables

This site contains the legacy documentation for DataTables v1.9 and earlier for reference only.
DataTables 1.9 was End Of Life in 2014. Do not use it for new work.
The current release of DataTables can always be found on DataTables.net.

Server-side processing | Ruby (CGI) with MySQL

Important - Do not use this script for new development - it will not work! It is no longer maintained and does not support DataTables 1.10+'s server-side processing mode. This page is retained only for legacy users and it will likely be removed in the near future.

This is an example implementation of a server-side script which can be used with a Ruby scripting environment. It is configured as a CGI script (so remember to chmod 755 etc) but could readily be modified to be used in a Ruby scripting framework.

To use the code on your own server, simply change the $columns array to list the columns you wish to include from your database, set $indexColumn to a column which is indexed (for speed), $table to the table name, and finally fill in your database connection parameters to $databaseInfo. You may also have to change the #! line to point towards your Ruby engine.

Feature set

Code

#!/usr/bin/ruby -w

# Script:    DataTables server-side script for PHP and MySQL
# Copyright: 2010 - Allan Jardine
# License:   GPL v2 or BSD (3-point)

# Modules
require 'rubygems'
require 'cgi'
require 'active_record/vendor/mysql.rb'
#require "mysql"

# CGI header
puts "Content-Type: text/html\n\n"


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# Easy set varaibles
#

# Array of database columns which should be read and sent back to DataTables
$columns = [ 'engine', 'browser', 'platform', 'version', 'grade' ]

# Indexed column (used for fast and accurate table cardinality)
$indexColumn = "id"

# DB table to use
$table = "ajax"

# Database connection information
$databaseInfo = Hash[
  "host" => "",
  "user" => "",
  "pass" => "",
  "db"   => ""
]


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# If you just want to use the basic configuration for DataTables with PHP server-side, there is
# no need to edit below this line
#

class DataTablesServer
  @query = ""
  @json = ""
  @cgi = nil
  @dbh = nil
  
  def initialize()
    @cgi = CGI.new
    @dbh = Mysql.real_connect($databaseInfo['host'], $databaseInfo['user'], $databaseInfo['pass'],
      $databaseInfo['db'] )
    
    # Create the SQL queries
    limit = paging()
    order = ordering()
    filter = filtering()
    
    # Get the data
    query = "
		  SELECT SQL_CALC_FOUND_ROWS #{$columns.join(', ')}
		  FROM   #{table}
		  #{filter}
		  #{order}
		  #{limit}
    "
    resultData = @dbh.query( query );
    
    # Calculate number of rows in data set - after filtering
    query = "
      SELECT FOUND_ROWS()
    "
    cadinalityFiltered = @dbh.query( query ).fetch_row[0]
    
    # Calculate number of rows in data set without filtering
    query = "
      SELECT COUNT(#{$indexColumn})
      FROM #{table}
    "
    cadinality = @dbh.query( query ).fetch_row[0]
    
    # Output
    output = '{'
    output += '"sEcho": '+@cgi['sEcho'].to_i.to_s+', '
    output += '"iTotalRecords": '+cadinality+', '
    output += '"iTotalDisplayRecords": '+cadinalityFiltered+', '
    output += '"aaData": [ '
    
    while row = resultData.fetch_hash do
      output += '['
      for i in (0..$columns.length-1)
        if $columns[i] != "version"
          output += '"'+row[ $columns[i] ].escape_single_quotes+'",'
        else
          output += (row[ $columns[i] ]=="0") ?
            '"-",' :
            '"'+row[ $columns[i] ]+'",'
        end
      end
      output = output.chop
      output += '],'
    end
    
    output = output.chop
    output += '] }';
    
    puts output
  end
  
  
  #
  # filtering
  # Create the 'WHERE' part of the SQL string
  #
  def filtering()
    filter = ""
    if @cgi['sSearch'] != ""
      filter = "WHERE "
      for i in (0..$columns.length-1)
        filter += "#{$columns[i]} LIKE '%#{@cgi['sSearch']}%' OR "
      end
      filter = filter.chop.chop.chop
    end
    return filter
  end
  
  
  #
  # ordering
  # Create the 'ORDER BY' part of the SQL string
  #
  def ordering()
    order = ""
    if @cgi['iSortCol_0'] != "" && @cgi['iSortingCols'].to_i > 0
      order = "ORDER BY  "
      for i in (0..@cgi['iSortingCols'].to_i-1)
        order += "#{$columns[ @cgi['iSortCol_'+i.to_s].to_i ]} "+
          "#{@dbh.escape_string( @cgi['sSortDir_'+i.to_s] )}, "
      end
    end
    
    return order.chop.chop
  end
  
  
  #
  # paging
  # Create the 'LIMIT' part of the SQL string
  #
  def paging()
    @limit = ""
    if @cgi['iDisplayStart'] != "" && @cgi['iDisplayLength'] != -1
      @limit = "LIMIT #{@dbh.escape_string( @cgi['iDisplayStart'] )}, "+
        "#{@dbh.escape_string( @cgi['iDisplayLength'] )}"
    end
    return @limit
  end
  
  def sendOutput()
    
  end
end

# Add an escape method to string
class String
  def escape_single_quotes
    self.gsub(/'/, "\\\\'")
  end
end

# Perform the server-side actions for DataTables
dtserver=DataTablesServer.new()
dtserver.sendOutput()