There are many ways to get your data into DataTables, and if you are working with seriously large databases, you might want to consider using the server-side options that DataTables provides. Basically all of the paging, filtering, sorting etc that DataTables does can be handed off to a server (or any other data source - Google Gears or Adobe Air for example!) and DataTables is just an events and display module.
The example here shows a very simple display of the CSS data (used in all my other examples), but in this instance coming from the server on each draw. Filtering, multi-column sorting etc all work as you would expect.
| Rendering engine | Browser | Platform(s) | Engine version | CSS grade |
|---|---|---|---|---|
| Loading data from server | ||||
| Rendering engine | Browser | Platform(s) | Engine version | CSS grade |
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php"
} );
} );
<?php
/* MySQL connection */
$gaSql['user'] = "*********";
$gaSql['password'] = "*********";
$gaSql['db'] = "*********";
$gaSql['server'] = "*********";
$gaSql['type'] = "mysql";
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}
/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<mysql_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
{
$sOrder .= fnColumnToField(mysql_real_escape_string( $_GET['iSortCol_'.$i] ))."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
}
$sOrder = substr_replace( $sOrder, "", -2 );
}
/* Filtering */
$sWhere = "";
if ( mysql_real_escape_string( $_GET['sSearch'] ) != "" )
{
$sWhere = "WHERE engine LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"browser LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"platform LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"version LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"grade LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%'";
}
$sQuery = "
SELECT id, engine, browser, platform, version, grade
FROM ajax
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$sQuery = "
SELECT id
FROM ajax
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$iTotal = mysql_num_rows($rResultTotal);
if ( $sWhere != "" )
{
$sQuery = "
SELECT id
FROM ajax
$sWhere
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$iFilteredTotal = mysql_num_rows($rResultFilterTotal);
}
else
{
$iFilteredTotal = $iTotal;
}
$sOutput = '{';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
$sOutput .= "'".$aRow['engine']."',";
$sOutput .= "'".$aRow['browser']."',";
$sOutput .= "'".$aRow['platform']."',";
$sOutput .= "'".$aRow['version']."',";
$sOutput .= "'".$aRow['grade']."'";
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';
echo $sOutput;
function fnColumnToField( $i )
{
if ( $i == 0 )
return "engine";
else if ( $i == 1 )
return "browser";
else if ( $i == 2 )
return "platform";
else if ( $i == 3 )
return "version";
else if ( $i == 4 )
return "grade";
}
?>
Please refer to the DataTables documentation for full information about its API properties and methods.