Data is shown, but search and row select don't work on the data.

CyberE

Hi I'm new at DataTables, JSON and jQuery so I hope you can help me.

I have this combobox which has some categories in it. Based on the categories a query will be executed and returns the data and refreshes a DataTable. Of course without a page refresh.

In the head of the HTML I have the following:
[code]
jQuery(document).ready(function() {
jQuery('#category_select').bind('change', function(){
reBuildGameListPerCat(jQuery('#category_select').val(), jQuery('#sid').val(), jQuery('#mod').val());
});

oTable = jQuery('#adm_games_per_cat').dataTable({
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": false,
"bAutoWidth": true,
"aaSorting": [[ 5, "asc" ]],
"oLanguage": {
"sProcessing": "Bezig met verwerken...",
"sLengthMenu": 'Toon 1002505001000Alles rijen',
"sZeroRecords": "Geen resultaten gevonden",
"sInfo": "_START_ tot _END_ van _TOTAL_ rijen",
"sInfoEmpty": "Er zijn geen records om te tonen",
"sInfoFiltered": "(gefilterd uit _MAX_ rijen)",
"sInfoPostFix": "",
"sSearch": "Zoek:",
"sUrl": "",
"oPaginate": {
"sFirst": "Eerste",
"sPrevious": "Vorige",
"sNext": "Volgende",
"sLast": "Laatste"
}
}

});
});

[/code]

CyberE

the table and combobox on the HTML/PHP are as followed:
[code]



 
 



Platform:


--- Kies platform ---';
while($row_category = mysql_fetch_array($result_category, MYSQL_ASSOC))
{
echo ''.$row_category["category_oms"].'';
}
echo'










Titel
Uitgever
Ontwikkelaar
Releasedatum





[/code]

CyberE

The changing of the combobox calls a javascript function:
[code]
function reBuildGameListPerCat(CatId, Sid, Mod){
oTable.fnClearTable();
jQuery.getJSON("../php/adm_games_per_cat.php?catid="+CatId+"&sid="+Sid+"&mod="+Mod,
function(data){
jQuery.each(data.games, function(index, row){
var tblRow =
""
+""+ row.gfview +""
+""+ row.gfactive +""
+""+ row.gfedit +""
+""+ row.gfdelete +""
+""+ row.game_titel +""
+""+ row.uitgever +""
+""+ row.ontwikkelaar +""
+""+ row.release_date +""
+""
jQuery(tblRow).appendTo("#adm_games_per_cat tbody");
});
});
}
[/code]

The above javascript calls a PHP file to build the query and the result...
[code]
<?php

$catid = $_GET[catid];
$sid = $_GET[sid];
$mod = $_GET[mod];

if ($catid == 0)
{
$result = mysql_query("SELECT games.game_id, games.game_titel, games.release_date, games.category_id, games.ontwikkelaar, games.uitgever, games.active, category.category_oms FROM games, category WHERE games.game_id > 0 AND category.category_id = games.category_id ORDER BY games.game_titel ASC" ) or die(mysql_error());
}
else
{
$result = mysql_query("SELECT games.game_id, games.game_titel, games.release_date, games.category_id, games.ontwikkelaar, games.uitgever, games.active, category.category_oms FROM games, category WHERE games.game_id > 0 AND category.category_id = games.category_id AND games.category_id = ".$catid." ORDER BY games.game_titel ASC" ) or die(mysql_error());
}
$data = array();

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

if ($row[2] == NULL)
{
$releasedate = 'Onbekend';
}
else
{
$releasedate = bepaal_geb_datum($row[2]);
}

if ($row[6] == 'Y')
{
$active = 'N';
}
else
{
$active = 'Y';
}

$data_row = array
(
'gfview' => '',
'gfactive' => '',
'gfedit' => '',
'gfdelete' => '',
'game_titel' => $row[1],
'uitgever' => $row[5],
'ontwikkelaar' => $row[4],
'release_date' => $releasedate
);
// array in array maken gelijk met talenbestand, en de key van de hoofdarray games noemen
$data['games'][] = $data_row;
}
//print json_encode($data);
echo json_encode($data);
?>
[/code]

The rows are added to the table, but you can't search on it, because the table then displays "No matching records found".

Could you please help me?

CyberE

Still no luck... :(

Any suggestions?

allan

Use fnAddData ( http://datatables.net/api#fnAddData ) rather than writing the HTML in yourself.

Allan

CyberE

Thanks Allan,

Could you please help me building this in, in the example? I really don't know where to put it...

allan

Remove the var tblRow =... append... block you have at the moment, and just replace it with something like in the example code for the fnAddData documentation (i.e. pass in an array of data to be added).

Allan

CyberE

Thanks a lot, it works...

I changed te javascript to this, and it works like a charm.

[code]
function reBuildGameListPerCat(CatId, Sid, Mod){
oTable.fnClearTable();
jQuery.getJSON("../php/adm_games_per_cat.php?catid="+CatId+"&sid="+Sid+"&mod="+Mod,
function(data){
jQuery.each(data.games, function(index, row){
oTable.fnAddData( [
""+row.gfview,
""+row.gfactive,
""+row.gfedit,
""+row.gfdelete,
""+row.game_titel,
""+row.category_oms,
""+row.uitgever,
""+row.ontwikkelaar,
""+row.release_date ] );
});
});
}
[/code]

allan

Nice one :-). Thanks for posting back your code!

Allan

CyberE

In additions to this...is there a way to speed things up?

The actual query takes less than 120ms, (according to FireBug) but it takes about 1 or 2 minutes until the table displays on screen. During the filling of the table, the browser freezes up entirely.

allan

Yes, pass 'false' as a second parameter to fnAddData - that will stop it from redrawing every time you add a new row. Then when you are finished with your loop do a call to fnDraw.

Allan

CyberE

Allan,

I tried the following, but then the table doesn't redraw at all...

[code]
function reBuildGameListPerCat(CatId, Sid, Mod){
oTable.fnClearTable();
jQuery.getJSON("../php/adm_games_per_cat.php?catid="+CatId+"&sid="+Sid+"&mod="+Mod,
function(data){
jQuery.each(data.games, function(index, row){
oTable.fnAddData( [
row.gfview,
row.gfactive,
row.gfedit,
row.gfdelete,
row.game_titel,
row.category_oms,
row.uitgever,
row.ontwikkelaar,
row.release_date ], false );
});
});
oTable.fnDraw();
}
[/code]

allan

You need the redraw inside your callback function :-). Otherwise it's just redrawing after you've sent the getJSON - not after you've added the data...!

Allan

CyberE

Like this? It still takes about 1 or 2 minutes to display a 1000 row table.

[code]
jQuery('#category_select').bind('change', function(){
reBuildGameListPerCat(jQuery('#category_select').val(), jQuery('#sid').val(), jQuery('#mod').val());
oTable.fnDraw();
});
[/code]

CyberE

In order to try and make things faster I changed a part of the script and placed a large number of code in one function and trying to push the JSON data into a javascript array.

The function is called when a dropdown box is changed...and the JSON returned is correct as I used it before to populate the datatable, but now it does not show any data, could you give me a hint on how to proceed or what am I doing wrong?

[code]
function reBuildGameListPerCat(CatId, Sid, Mod){
var dataObj = new Array();

jQuery.getJSON("../php/adm_games_per_cat.php?catid="+CatId+"&sid="+Sid+"&mod="+Mod,
function(data){
jQuery.each(data.games, function(row, index){
dataObj.push([
row.gfview,
row.gfactive,
row.gfedit,
row.gfdelete,
row.game_titel,
row.category_oms,
row.uitgever,
row.ontwikkelaar,
row.release_date ]);
});
});

dtObj = $('#adm_games_per_cat').dataTable({
"bPaginate": true,
"sPaginationType": "full_numbers",
"bLengthChange": false,
"bFilter": true,
"bSort": true,
"bDestroy": true,
"aoColumns": [
{ "bSortable": false },
{ "bSortable": false },
{ "bSortable": false },
{ "bSortable": false },
{ "bSortable": true },
{ "bSortable": true },
{ "bSortable": true },
{ "bSortable": true },
{ "bSortable": true }
],
"aaSorting": [[5,'asc']],
"aaData": dataObj,
"bInfo": true,
"bAutoWidth": false,
"oLanguage": {
"sInfo" : "Resultaten _START_ tot _END_ van _TOTAL_ worden getoond.",
"sInfoEmpty" : "Resultaten 0 tot 0 van 0 worden getoond.",
"sSearch" : "",
"sZeroRecords" :"Geen resultaten gevonden",
"sInfoFiltered" : "(gefilterd uit _MAX_ records totaal)",
"oPaginate" : { "sFirst" : "Eerste",
"sPrevious" : "Vorige",
"sNext" : "Volgende",
"sLast" : "Laatste" }
}
});
}
[/code]

allan

Your table is being created before your Ajax request as returned - so no data is available to be added. i.e. if you console.log( dataObj ) it will be an empty array when you initialise the table.

Just more the initialisation of the table into the getJSON callback :-)

Allan

CyberE

Thanks a million...it works! Over 8000 records within 2 seconds on screen...Nice!

[code]
function reBuildGameListPerCat(CatId, Sid, Mod){
var dataObj = new Array();

jQuery.getJSON("../php/adm_games_per_cat.php?catid="+CatId+"&sid="+Sid+"&mod="+Mod,
function(data){
jQuery.each(data.games, function(row, index){
dataObj.push([
this.gfview,
this.gfactive,
this.gfedit,
this.gfdelete,
this.game_titel,
this.category_oms,
this.uitgever,
this.ontwikkelaar,
this.release_date ]);
});
dtObj = jQuery('#adm_games_per_cat').dataTable({
"bPaginate": true,
"sPaginationType": "full_numbers",
"bLengthChange": false,
"bFilter": true,
"bSort": true,
"bDestroy": true,
"aoColumns": [
{ "bSortable": false },
{ "bSortable": false },
{ "bSortable": false },
{ "bSortable": false },
{ "bSortable": true },
{ "bSortable": true },
{ "bSortable": true },
{ "bSortable": true },
{ "bSortable": true }
],
"aaSorting": [[5,'asc']],
"aaData": dataObj,
"bInfo": true,
"bAutoWidth": false,
"oLanguage": {
"sInfo" : "Resultaten _START_ tot _END_ van _TOTAL_ worden getoond.",
"sInfoEmpty" : "Resultaten 0 tot 0 van 0 worden getoond.",
"sSearch" : "",
"sZeroRecords" :"Geen resultaten gevonden",
"sInfoFiltered" : "(gefilterd uit _MAX_ records totaal)",
"oPaginate" : { "sFirst" : "Eerste",
"sPrevious" : "Vorige",
"sNext" : "Volgende",
"sLast" : "Laatste" }
}
});
});

}
[/code]

allan

Superb! bDeferRender in DataTables 1.8 (currently beta) should make it even faster ;-) - http://datatables.net/beta/1.8/examples/ajax/defer_render.html

Allan

CyberE

Implemented DataTables 1.8 and it did go faster... up to 10.000 records on screen within 1 sec.