Server side processing - no data displayed

pearly_030

Hi,

I don't speak english very well. I'm french.
My problem, I want to display, in a datatable, data from server with server side processing.
I always obtain the message "loading data from server" and no data.
I controled data in json_encode, they're OK
Can you help me ? Thanks.
My script
[code]
.......
$(document).ready(function() {
$("#min").datepicker({
"dateFormat": "dd-mm-yy",
"monthNames": ["Janvier","Février","Mars","Avril","Mai","Juin","Juillet","Aout","Septembre","Octobre","Novembre","Decembre"],
"dayNamesMin": ["Di","Lu","Ma","Me","Je","Ve","Sa"],
"showWeek": true,
"numberOfMonths": 2,
"weekHeader": "Sem"
});
$("#max").datepicker({
"dateFormat": "dd-mm-yy",
"monthNames": ["Janvier","Février","Mars","Avril","Mai","Juin","Juillet","Aout","Septembre","Octobre","Novembre","Decembre"],
"dayNamesMin": ["Di","Lu","Ma","Me","Je","Ve","Sa"],
"showWeek": true,
"numberOfMonths": 2,
"weekHeader": "Sem"
});

var oTable1 = $("#reservees").dataTable({
"sDom": \'<"top"pt><"bottom">\',
/*"sScrollY" : "100px",*/
"bPaginate": false,
"iDisplayLength": 10,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./server_processing.php",
"fnInitComplete": function ( oSettings ) {
oSettings.oLanguage.sZeroRecords = "Aucune réservation trouvée. Veuillez consulter la liste des véhicules disponibles ci-dessous"
}
});

......
[/code]

My server_processing.php code :

[code]
<?php


$aColumns = array( 'id_reservation', 'id_vehicule', 'qui', 'datedepart', 'heuredepart', 'dateretour', 'heureretour', 'sitedepart', 'destination', 'nbrepassagers' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id_reservation";

/* DB table to use */
$sTable = "reservations";

/* Database connection information */
$gaSql['user'] = ".......";
$gaSql['password'] = ".......";
$gaSql['db'] = ".......";
$gaSql['server'] = ".......";

/*
* MySQL connection
*/
$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'] ) && $_GET['iDisplayLength'] != '-1' )
{
$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 $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i


}
}

$output['aaData'][] = $row;

}

echo json_encode( $output );
?>

allan

What is the JSON return from the server? Did you test it with JSON lint?

Allan

pearly_030

Hi,
more informations about my problem:
In the fire bug, i've this :
"an is undefined"
on this line
"for ( var i=0, iLen=an.length ; i

pearly_030

Allan,

When I directly launch the script server_processing.php, it returns
{"sEcho":0,"iTotalRecords":"2","iTotalDisplayRecords":"2","aaData":[["1","1","V06737","2011-07-25","18:00:00","2011-07-27","18:00:00","1","2","2"],["2","2","V06820","2011-07-20","18:00:00","2011-07-26","18:00:00","1","2","1"]]}

All data of the database.

What is JSON lint ?

Thanks

fbas

There's a difference between running server_processing.php directly compared to the request sent by your client program (datatables). In the direct run, the PHP file does not have parameters that are passed to it in the query string (or by POST).

With the debugger, you can see exactly what url + query string is being called by your datatable and then run that directly against your server. you can also see the json sent back in the debugger. I suggest using firebug to view the AJAX call.

http://i.imgur.com/VnNXs.png (screen capture of FireBug) View the console, XHR and examine the parameters and response.

pearly_030

Hi fbas,

Thanks for your help.

I tried to examine parameters ans response with console ans XHR.
result : 0 request.

I ask me if next lines are OK ?
[code]
var oTable1 = $("#reservees").dataTable({
"sDom": \'<"top"pt><"bottom">\',
/*"sScrollY" : "100px",*/
"bPaginate": false,
"iDisplayLength": 10,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/Reservation_vehicules/server_processing.php",
"fnInitComplete": function ( oSettings ) {
oSettings.oLanguage.sZeroRecords = "Aucune réservation trouvée. Veuillez consulter la liste des véhicules disponibles ci-dessous"
}
});
[/code]

In the fire bug, i've always this error :
"an is undefined"
on this line
"for ( var i=0, iLen=an.length ; i

pearly_030

fbas and Allan,

I find my error in the line

"sDom": \'<"top"pt><"bottom">\',

I don't know exactly where but when I comment it, it works.

Many thanks for your participation.

emeric63

Yes, I had the same problem but I found out what my problem was.
All the parameters that you have set true need to be in the sDom.

for example when you have "bProcessing": true, the "r" MUST be inside the sDom
so try to put "sDom": \'<"top"pt><"bottom"r>\', and it should work

Here "r" was put in the bottom but you can put it where you want

----------------------------------------------------------

J'ai eu le même problème, tous les paramètres de la datatable qui sont activé (setté à true) doivent
figuré dans la chaine de caractère du sDom

Dans ton cas bProcessing est setté à true, il doit donc figurer dans le sDom
essai de mettre : "sDom": \'<"top"pt><"bottom"r>\', et cela devrait marcher

j'ai placé le "r" dans le bottom mais il peut être placé à l'endroit de ton choix
en espérant que cela t'aidera

pearly_030

Thanks for informations.
I've an other problem now.
Data are correctly displayed but my filters don't work.
In Firebug, aData are OK but the script stops on "var iDebut = aData[3];"
Can you help me ?
My code
[code]
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = document.getElementById("min").value;
var iMax = document.getElementById("max").value;

iMin=iMin.substring(6,10) + iMin.substring(3,5)+ iMin.substring(0,2);
iMax=iMax.substring(6,10) + iMax.substring(3,5)+ iMax.substring(0,2);

var iDebut = aData[3];
var iRetour = aData[5];
iDebut=iDebut.substring(6,10) + iDebut.substring(3,5)+ iDebut.substring(0,2);
iRetour=iRetour.substring(6,10) + iRetour.substring(3,5)+ iRetour.substring(0,2);

var iSiteDepart = document.getElementById("sitedepart").value;
var iDestination = document.getElementById("destination").value;
var iDepart = aData[7];
var iDest = aData[8];

if ( iMin == "" && iMax == "")
{
if (iSiteDepart == "" && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == iDest) {return true;}
if (iSiteDepart == "" && iDestination == iDest) {return true;}
}
else if ( iMin == "" && iRetour == iMax )
{
if (iSiteDepart == "" && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == iDest) {return true;}
if (iSiteDepart == "" && iDestination == iDest) {return true;}

}
else if ( iMin == iDebut && "" == iMax )
{
if (iSiteDepart == "" && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == iDest) {return true;}
if (iSiteDepart == "" && iDestination == iDest) {return true;}
}
else if ( iMin == iDebut && iRetour == iMax )
{
if (iSiteDepart == "" && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == "") {return true;}
if (iSiteDepart == iDepart && iDestination == iDest) {return true;}
if (iSiteDepart == "" && iDestination == iDest) {return true;}
}
return false;
});

$(document).ready(function() {
$("#min").datepicker({
"dateFormat": "dd-mm-yy",
"monthNames": ["Janvier","Février","Mars","Avril","Mai","Juin","Juillet","Aout","Septembre","Octobre","Novembre","Decembre"],
"dayNamesMin": ["Di","Lu","Ma","Me","Je","Ve","Sa"],
"showWeek": true,
"numberOfMonths": 2,
"weekHeader": "Sem"
});
$("#max").datepicker({
"dateFormat": "dd-mm-yy",
"monthNames": ["Janvier","Février","Mars","Avril","Mai","Juin","Juillet","Aout","Septembre","Octobre","Novembre","Decembre"],
"dayNamesMin": ["Di","Lu","Ma","Me","Je","Ve","Sa"],
"showWeek": true,
"numberOfMonths": 2,
"weekHeader": "Sem"
});

var oTable1 = $("#reservees").dataTable({
/*"sDom": \'<"top">rt<"bottom"i>\',*/
/*"sDom": \'<"top"><"bottom">\',*/

//"sScrollY" : "100px",
"bFilter": true, /*search*/
"bPaginate": true, /* flèches de pagination */
"iDisplayLength": 8,
"bLengthChange": false, /* Affichage de l\'information "Show xx entries" */
"bInfo": false, /* Affichage de l\'information showing xx to yy of zz */
"bVisible": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./server_processing.php",
"fnInitComplete": function ( oSettings ) {
oSettings.oLanguage.sZeroRecords = "Aucune réservation trouvée. Veuillez consulter la liste des véhicules disponibles ci-dessous"
},
"aoColumns": [
/*id*/ { "bSearchable": false,
"bVisible": false },
/*vehicule*/ null,
/*qui*/ null,
/*Date départ*/ null,
/*Heure départ*/ null,
/*Date retour*/ null,
/*Heure retour*/ null,
/*Site de départ*/ null,
/*Destination*/ null,
/*Nombre de passagers*/ null,
]
});

$("#min").keyup( function() { oTable1.fnDraw(); } );
$("#max").keyup( function() { oTable1.fnDraw(); } );
$("#sitedepart").keyup( function() { oTable1.fnDraw(); } );
$("#destination").keyup( function() { oTable1.fnDraw(); } );
$("#min").change( function() { oTable1.fnDraw(); } );
$("#max").change( function() { oTable1.fnDraw(); } );
$("#sitedepart").change( function() { oTable1.fnDraw(); } );
$("#destination").change( function() { oTable1.fnDraw(); } );


});
[/code]
Thanks

fbas

add to line 8:
[code]
console.log(aData);
[/code]

and see what aData contains in the debugger.

pearly_030

Hi fbas,
I added it and data are OK.
I added too
[code]
console.log(iMin);
[/code]
and data in debugger are always OK.
It does'nt work since I use ""sAjaxSource": "./server_processing.php"," to make the table.

A solution?
Thanks

fbas

are you sure that server_processing.php file exists at that location?

pearly_030

Yes.
All data are corectly displayed but only the filter doesn't work.
I'm trying to debug with console.log and seems OK.
I don't understand.

fbas

Oh.. afnFiltering doesn't work on server side code. when you decide to use server side processing, ALL processing like sorting, filtering, etc., is done on the server side.

Your server side script will have to handle filtering. when you use the datatable's fnFilter functions, it will call to the server side script with sSearch parameter or sSearch_{colnum} set and your SQL in the server side will have to perform the filtering.

If you're trying to do range filtering, you'll have to add some code to the server side.

pearly_030

OK fbas.
I didn't understand it in the doc where I read
"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."
So I will modify the server side.
Many thanks