Datatable jQuery plugin example


<link rel="stylesheet" href="/jquery/themes/pepper-grinder/jquery-ui.css">
<script src="/jquery/jquery-1.10.2.js"></script>

<style type="text/css" title="currentStyle">
	@import "/jquery/datatables/media/css/demo_page.css";
	@import "/jquery/datatables/media/css/demo_table.css";
</style>

<script type="text/javascript" language="javascript"
		src="/jquery/datatables/media/js/jquery.dataTables.js"></script>

top of this page

Set up Html

At first, put the html code to apply JQuery datatable plugin.


<div id="demo" style="width:80%;">
<table cellpadding="0" cellspacing="0" border="0" class="display" 
			id="example" width="100%">
	<thead>
		<tr>
			<th>Rendering engine</th>
			<th>Browser</th>
			<th>Platform(s)</th>
			<th>Engine version</th>
			<th>CSS grade</th>
		</tr>
	</thead>
	<tbody>
		
	</tbody>
	<tfoot>
		<tr>
			<th>Rendering engine</th>
			<th>Browser</th>
			<th>Platform(s)</th>
			<th>Engine version</th>
			<th>CSS grade</th>
		</tr>
	</tfoot>
</table>

</div>

In this code, by surrounding with div tag, set the width of datatable widget. And set header and footer in this table.

The tbody section is blank. The record will be loaded later.

top of this page

Load datatable plugin

The JavaScript in the "index.html" loads the datatable plugin. The source code is below.


	$(document).ready(function() {
		var oTable = $('#example').dataTable({
			"bProcessing": false,
			"bServerSide": true,
			"sAjaxSource": "/samples/gridtable/serverData.alns"
		});
	} );

In this script, set the url to get Json data into "/samples/gridtable/serverData.alns".

top of this page

Server side program

The source code to get JSon data of records is below.


$OFFSET = $IN.iDisplayStart;
$LIMIT = $IN.iDisplayLength;


// iSortCol_0;
$ORDER[0] = "recordid";

if($IN.iSortCol_0 == 0){
	$ORDER[0] = "engine";
}
else if($IN.iSortCol_0 == 1){
	$ORDER[0] = "browser";
}
else if($IN.iSortCol_0 == 2){
	$ORDER[0] = "platform";
}
else if($IN.iSortCol_0 == 3){
	$ORDER[0] = "version";
}
else if($IN.iSortCol_0 == 4){
	$ORDER[0] = "grade";
}
$orderDirection = $IN.sSortDir_0;
$ORDER[0].ORDER = $orderDirection;

if($IN.sSearch != null && $IN.sSearch != ""){
	$likeStr = "%" + $IN.sSearch + "%";
}

SELECT * INTO aaData
FROM
	ajax
WHERE
	ajax.engine LIKE $likeStr OR
	ajax.browser LIKE $likeStr OR
	ajax.platform LIKE $likeStr
ORDER BY @ORDER
LIMIT $LIMIT OFFSET $OFFSET
ADJUST_WHERE = true;


SELECT count(*) AS cnt INTO recordsCount
FROM
	ajax;


$iTotalRecords = $recordsCount[0].cnt;
$iTotalDisplayRecords = Array.size(@aaData);


$json = GridTable.getJSon($IN.sEcho, $iTotalRecords, 
			$iTotalDisplayRecords, @aaData);

return $json;

In this script, it supports

Paging of the record
Full text search

The parameter "$IN.iDisplayStart" indicate the start position of the database record, and "$IN.iDisplayLength" does the limit of records in a page.

The "$IN.sSearch" is inputed word for full text search.

top of this page

Install script

This example uses database table, so you have to setup it, if you manually do. The install script is at "/install/install.alns".

install script

The source code of the install script is below.


function DridTableData.ddl()
{
	BEGIN;
	
	CREATE TABLE ajax (
	  recordid int,
	  engine varchar(255) NOT NULL default '',
	  browser varchar(255) NOT NULL default '',
	  platform varchar(255) NOT NULL default '',
	  version double NOT NULL default '0',
	  grade varchar(20) NOT NULL default '',
	  
	  PRIMARY KEY (recordid)
	);	
	COMMIT;

	BEGIN;	
	
	$idx = 1;
	
	DELETE FROM ajax;
	
	INSERT INTO ajax ( recordid, engine, browser, platform, version, grade )
		 VALUES ( $idx, 'Trident', 'Internet Explorer 4.0', 'Win 95+', '4', 'X' );
	$idx++; INSERT INTO ajax ( recordId, engine, browser, platform, version, grade )
		 VALUES ( $idx, 'Trident', 'Internet Explorer 5.0', 'Win 95+', '5', 'C' );
	$idx++; INSERT INTO ajax ( recordid, engine, browser, platform, version, grade )
		 VALUES ( $idx, 'Trident', 'Internet Explorer 5.5', 'Win 95+', '5.5', 'A' );
	(... skipped ...)
	$idx++; INSERT INTO ajax ( recordid, engine, browser, platform, version, grade )
		 VALUES ( $idx, 'Other browsers', 'All others', '-1', '-1', 'U' );
	
	COMMIT;

	
	return 0;
}

This script makes "ajax" table and insert demo data.

Testing automation

This example is testable code, there is more example to do testing automation. Please take a look at Testing Datatable example.