I have a database of 56000 records whom i need to implement data table server side code. The problem is that i have done all the necessary steps to accomplish this task but still all in vain, returning me full set of data.
The main problem is when my page loads it loads all the 56000 records on runtime and cause the page to halt in non ending state.
So, once data table will properly implemented this problem goes automatically. I want this operation done on the run time. Once the page loads, it should show pagination and split all the 56000 records in paging pages. limit should be 50.
The search and sorting is also not working.
Here is my JS function:
<link href="//cdn.datatables.net/1.10.0/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script src="http://legacy.datatables.net/release-datatables/media/js/jquery.js"></script>
<script src="//legacy.datatables.net/release-datatables/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" language="javascript" class="init">
$(document).ready(function() {
$('#rounded-corner').dataTable( {
"processing": true,
"serverSide": true,
"ajax": "<?php echo WEB_URL; ?>assets/common/authorsServerSide.php",
} );
} );
</script>
Here is my HTML code:
<table id="rounded-corner" summary="2007 Major IT Companies' Profit" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th scope="col" class="rounded-company">Sr No.</th>
<th scope="col" class="rounded">Author Name</th>
<th scope="col" class="rounded">Birth Year</th>
<th scope="col" class="rounded">Death Year</th>
<th scope="col" colspan="2" class="rounded-q4">Action</th>
</tr>
</thead>
<tbody>
<?php $count = 1; while($AuthorRecs = $RecordSet->fetch_object()){?>
<tr>
<td><?php echo $count++; ?></td>
<td><?php print stripslashes(html_entity_decode($AuthorRecs->a_name)); ?></td>
<td><?php print $AuthorRecs->a_birth; ?></td>
<td><?php print $AuthorRecs->a_death; ?></td>
<td><a href="editAuthors?a_ID=<?php echo $AuthorRecs->a_ID; ?>"><img src="../assets/styles/admin/images/user_edit.png" alt="" title="" border="0" /></a></td>
<td><a href="process/adminProcess.php?page=deleteAuthors&a_ID=<?php echo $AuthorRecs->a_ID; ?>" class="ask"><img src="../assets/styles/admin/images/trash.png" alt="" title="" border="0" /></a></td>
</tr>
<?php } ?>
</tbody>
</table>
And this is my server side code:
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine, 2012 - Chris Wright
* License: GPL v2 or BSD (3-point)
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'a_ID', 'a_name', 'a_birth', 'a_death' );
/* Indexed column (used for fast and accurate table cardinality) */
//$sIndexColumn = "a_ID";
/* DB table to use */
$sTable = "authors";
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "";
$gaSql['db'] = "giftcardbooks";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
/*
* MySQL connection
*/
if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) )
{
fatal_error( 'Could not open connection to server' );
}
if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
{
fatal_error( 'Could not select database ' );
}
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}
/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" )
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
?>
