DataTable only displays first 10 rows of table with 30000 rows

homerjsimpson

Hi, I'm using dataTables on a website to display the rows from a table between some selected dates. The selection works but for some reason only 10 rows of the entire table are accessible. I'm sure I'm doing something wrong because I'm just starting using Datables, but I don't know what. Can someone help me? If I add bServerside : true to the code all the 30000 rows are accessible but unfortunately the date selection stops working. Below the code:

[code]






<?php header("Cache-Control: no-cache"); ?>

Leveringen


@import "<?php echo base_url(); ?>css/demo_page.css";
@import "<?php echo base_url(); ?>css/demo_table.css";
@import "<?php echo base_url(); ?>css/custom-theme/jquery-ui-1.8.9.custom.css";




#theadRow > th > span {margin-right:2em;display:block;}

.hideElement {
display:none;
}

input.datepicker {
width:7em;
}

div.dateControlBlock {
float:right;
}

#endBlock {
height:5ex;
}


























var siteurl = getBaseURL();


$(document).ready(function()
{


oTable = $('#boeken_table').dataTable
(

{
"bJQueryUI" : true,
"aoColumns" : [
{ "sTitle": "Leerling nummer", "sWidth": "10%" },
{ "sType": "string", "sTitle": "Naam"},
{ "sType": "string", "sTitle": "Boek nummer", "sWidth": "10%" },
{ "sType": "string", "sTitle": "ISBN", "sWidth": "10%" },
{ "sType": "string", "sTitle": "Titel", "sWidth": "25%" },
{ "sType": "string", "sTitle": "Aantal", "sWidth": "10%" },
{ "sType": "date", "sTitle": "Datum geleverd", "sWidth": "14%" },
{ "sType": "string", "sTitle": "Richting", "sWidth": "10%" },
{ "sType": "string", "sTitle": "Klas", "sWidth": "10%" }

],
"oLanguage" : {
"sLengthMenu": 'Weergeven per '+
'5'+
'10'+
'20'+
'50'+
' resultaten'
},
"iDisplayLength" : 10,

"aaSorting": [[6,'asc']],



"fnInitComplete": function() { $("#boeken_table_filter input").focus(); },//focus op search veld moet in documents.ready functie staan
'sAjaxSource' : siteurl + "sis/index.php/main/listener_leveringenlijst",
'fnServerData': function(sSource, aoData, fnCallback)
{
$.ajax
({
'dataType': 'json',
'type' : 'POST',
'url' : sSource,
'data' : aoData,
'success' : fnCallback
});
}

});

} );



$("#genereer_excel_button").click(function()
{
$("#genereer_excel_button").fadeOut('slow', function() {
// Animation complete.
});
$.post(siteurl + "sis/index.php/main/leveringtoexcel", {}, function(retData){
$("#genereer_excel_button_div").html("download rechtermuisknop -> opslaan als...");
});
});









// The plugin function for adding a new filtering routine
$.fn.dataTableExt.afnFiltering.push(
function(oSettings, aData, iDataIndex){
var dateStart = parseDateValue($("#dateStart").val());
var dateEnd = parseDateValue($("#dateEnd").val());
// aData represents the table structure as an array of columns, so the script access the date value
// in the first column of the table via aData[6]
var evalDate= parseDateValue(aData[6]);

if (evalDate >= dateStart && evalDate <= dateEnd) {
return true;
}
else {
return false;
}

});

// Function for converting a dd/mm/yyyy date value into a numeric string for comparison (example 08/12/2010 becomes 20101208
function parseDateValue(rawDate) {
var dateArray= rawDate.split("-");
var parsedDate= dateArray[2] + dateArray[1] + dateArray[0];
return parsedDate;
}


$(function() {


var $dTable= oTable;


// The dataTables plugin creates the filtering and pagination controls for the table dynamically, so these
// lines will clone the date range controls currently hidden in the baseDateControl div and append them to
// the feedbackTable_filter block created by dataTables
$dateControls= $("#baseDateControl").children("div").clone();
$("#feedbackTable_filter").prepend($dateControls);

// Implements the jQuery UI Datepicker widget on the date controls
$('.datepicker').datepicker(
{showOn: 'button', buttonImage: '/sis/images/calendar.gif', buttonImageOnly: true }

);

// Create event listeners that will filter the table whenever the user types in either date range box or
// changes the value of either box using the Datepicker pop-up calendar
$("#dateStart").keyup ( function() { $dTable.fnDraw(); } );
$("#dateStart").change( function() { $dTable.fnDraw(); } );
$("#dateEnd").keyup ( function() { $dTable.fnDraw(); } );
$("#dateEnd").change( function() { $dTable.fnDraw(); } );

});







Overzicht leveringen van tot en met








Bezig met laden...









Export




[/code]

allan

Is your listener_leveringenlijst file returning all 30'000 rows? Can you run your table thought the debugger ( http://debug.datatables.net ) so I can see the table's configuration please.

btw - you probably went to enable bDeferRender with that many rows on first draw.

Allan

homerjsimpson

Hi Allan,

Thanks for responding. I've run the debugger: http://debug.datatables.net/ejidax

I hope you can tell me what I'm doing wrong. My problem is I'm updating a site originally created by someone else. I found in a file called Datatablesdoc.php the following code:

[code] $sLimit = "LIMIT ";

if ($this->ci->input->post("iDisplayStart") && $this->ci->input->post("iDisplayLength") != "-1")
$sLimit .= $this->ci->input->post("iDisplayStart") . ", " . $this->ci->input->post("iDisplayLength");
else {
$iDisplayLength = $this->ci->input->post("iDisplayLength");

if (empty($iDisplayLength))
$sLimit .= "0,10";
else
$sLimit .= "0," . $iDisplayLength;
}[/code]

If I change $sLimit .="0,10"; to for intstance $sLimit .="0,30000" the table loads the first 30000 rows, but I don't understand why I should change it there and how to do this when it can't be telled beforehand how many rows the table will have.

I hope this make sense to you.

I've added bDeferRender thanks.

Alex

allan

The use of LIMIT like that and the input parameter iDisplayLength suggests that your script is expecting server-side processing - but your table is currently configured for client-side processing.

I think it would probably be best to read these two parts of the documentation to understand the difference between client-side and server-side processing in DataTables, then you can decided which you want and modify your script to suit:

http://datatables.net/usage/#data_sources
http://datatables.net/usage/server-side

Allan

homerjsimpson

Thanks for your quick response. The problem is the site has several tables and the others are server-side. This is the only client-side table. Is this possible or should I change this one to server-side as well.

Alex

homerjsimpson

And one more question if I should change it to server-side, how can I make the date filter I wrote in the above code working? That'sactually the reason I changed this table to client-side processing because when I add "bServerside": true to the code it ignores the date filter. I do understand why, but I don't know how to change the script to use is on a server-side processed table.

Alex