Show datatable using server processing

davisoski

Hi,

I'm newbie and I'm trying to show my first datatable using server side processing.

Right now I have solved the error Undefined index: sEcho in server_processing.php, and I get the values from datatable "ajax" included in 1.9.4 package.


I have a page called index.php (for my test):

index.php:

require_once("../../server_side/scripts/server_processing.php");



/*Initialization scripts here, everything works ok */


/* http://datatables.net/blog/Getting_started_with_DataTables%3A_First_steps */




$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../../server_side/scripts/server_processing.php"
} );
} );


I get the data from database correctly (see below).


What missing?

Thanks

dump($$output):

Array
(
[iTotalRecords] => 57
[iTotalDisplayRecords] => 57
[aaData] => Array
(
[0] => Array
(
[0] => Trident
[1] => Internet Explorer 4.00000 /* Changed by me to make some test */
[2] => Win 95+
[3] => 4
[4] => X
)

[1] => Array
(
[0] => Trident
[1] => Internet Explorer 5.0
[2] => Win 95+
[3] => 5
[4] => C
)

[2] => Array
(
[0] => Trident
[1] => Internet Explorer 5.5
[2] => Win 95+
[3] => 5.5
[4] => A
)

[3] => Array
(
[0] => Trident
[1] => Internet Explorer 6
[2] => Win 98+
[3] => 6
[4] => A
)

[4] => Array
(
[0] => Trident
[1] => Internet Explorer 7
[2] => Win XP SP2+
[3] => 7
[4] => A
)

[5] => Array
(
[0] => Trident
[1] => AOL browser (AOL desktop)
[2] => Win XP
[3] => 6
[4] => A
)

[6] => Array
(
[0] => Gecko
[1] => Firefox 1.0
[2] => Win 98+ / OSX.2+
[3] => 1.7
[4] => A
)

[7] => Array
(
[0] => Gecko
[1] => Firefox 1.5
[2] => Win 98+ / OSX.2+
[3] => 1.8
[4] => A
)
etc.....


)

)

allan

You need to return the sEcho parameter as well. See: http://datatables.net/usage/server-side

Allan

davisoski

Hi

thanks for answer.

I have no $_GET in my index.php page so I don't know which value (integer) is:

I have modified server_processing.php like this:

[code]
$output = array(
"sEcho" => intval("1"),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
[/code]
I have tried the value for: echo json_encode( $output ); in http://jsonlint.com/ and is correct.

I have followed this thread:
http://datatables.net/forums/discussion/7127/secho-error-from-server_processing.php/p1


Any comment?.

Hoping to use these datatables....

jefffan24

The $_GET variable will be on server_processing.php not index.php so change $output to:

[code]
$output = array(
"sEcho" => $_GET['sEcho'],
"iTotalRecords" => $iTotal.
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
[/code]

By returning sEcho from the PHP file that returns the results (and it matches the one datatables came up with on page load) it lets it know that the request is valid.

davisoski

Hi.

thanks for comment.


If I do what you suggest I get:
Notice: Undefined index: sEcho in C:\xampp\htdocs\reg\server_side\scripts\server_processing.php on line 143

and in firebug:
{"sEcho":null,"iTotalRecords":"57","iTotalDisplayRecords":"57","aaData":[["Trident","Internet Explorer 4.00000","Win 95+",........

and this is my index.php

[code]
<?php
// File Location: /site/login/index.php

require_once("../tpl_unsecure.php");
require_once("../../_lib/_classes/class.session.php");
require_once("../../_lib/_base/handlers.php");
require_once("../../server_side/scripts/server_processing.php");

if ($_POST) { // check for http post vars
$sUser = $_POST["user"];
$sPass = $_POST["pass"];


// validate user name
if (!validUser($sUser)) {

catchErr("Introduzca un nombre de usuario válido");
$FORMOK = false;
}

// validate user password
if (!validPass($sPass)) {

catchErr("Introduzca una contraseña válida");
$FORMOK = false;
}

// if forms variables validated
if ($FORMOK) {

// echo "FORMOK
";
// assign array values
$aArgs["User Name"] = $sUser;
$aArgs["Password"] = $sPass;

$oSess = new session;

if ($sPath = $oSess->login($aArgs)) {
echo $test;
}
}
} else { // post vars not sent
$sUser = null;
}

// Add all header info (javascritps includes, jquery, .....)
setHeader();
openPage();
?>








$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/../server_side/scripts/server_processing.php"
} );
} );






<?php closePage(); ?>



/*demo page css*/
body{ font: 62.5% "Trebuchet MS", sans-serif; margin: 50px;}
.demoHeaders { margin-top: 2em; }
#dialog_link {padding: .4em 1em .4em 20px;text-decoration: none;position: relative;}
#dialog_link span.ui-icon {margin: 0 5px 0 0;position: absolute;left: .2em;top: 50%;margin-top: -8px;}
ul#icons {margin: 0; padding: 0;}
ul#icons li {margin: 2px; position: relative; padding: 4px 0; cursor: pointer; float: left; list-style: none;}
ul#icons span.ui-icon {float: left; margin: 0 4px;}

[/code]


somethig missing.... but what???

[code]
<?php

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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('engine', 'browser', 'platform', 'version', 'grade');


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

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

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

/* REMOVE THIS LINE (it just includes my SQL connection user/pass) */

/* include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" ); */


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* 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
*/
$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])] . "` " .
mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
}

$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++) {
$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 die(mysql_error());

/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$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 die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];

$sQuery;



/*
* Output
*/
/* "sEcho" => intval($_GET['sEcho']), */
$output = array(
"sEcho" => $_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;
}

// dump($output);

echo json_encode($output);
?>
[/code]


thanks

jefffan24

Ok wow a lot to go through here:

Line 7:
[code]
require_once("../../server_side/scripts/server_processing.php");
[/code]

Why are you requiring it in your index page and using it as the ajax request? Its one or the other, not both.


Put this as far down in the page (but before the closing tag as you can. The further down it goes the less problems your likely to have because of unrendered HTML.
[code]


$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/../server_side/scripts/server_processing.php"
} );
} );


[/code]

Secondly
[code]
$(function(){
[/code]

Is short hand for $(document).ready(){ just so you know, you don't have to type so much.

Finally, change the last line to:

[code]
echo json_encode($_GET);
[/code]

and tell me what it returns.

Just in-case your doing this: You won't be able to see the results datatables is seeing unless you pass the exact same parameters it is passing. Meaning if you go to server_processing.php in your browser and don't pass it any parameters you are not going to get the same results as an ajax request from datatables.

Let me know if you don't know how to view ajax requests as they happen and see their responses and I'll tell you (I'd rather not type it out if I don't have to).

davisoski

Hi

Eliminated Line 7:
require_once("../../server_side/scripts/server_processing.php");

Moved
$(document).ready(function() {

close to and out of the

in server_processing.php added:

echo json_encode($output) . "
";
echo "json_enconde($_GET ):
";
echo json_encode($_GET);

and this is the result:

nothing

After eliminate

require_once("../../server_side/scripts/server_processing.php");

nothing works.

with
require_once("../../server_side/scripts/server_processing.php");

I get:
Notice: Undefined index: sEcho in C:\xampp\htdocs\reg\server_side\scripts\server_processing.php on line 144
json_enconde(Array ):
[]


and finally, as I told in the my first post, I newbie, and you right:

I don't know how to view ajax requests as they happen and see their responses.

thanks again for your time

jefffan24

What your seeing is because your loading the page with require, which isn't what we want. Your seeing those errors printed on index.php (which won't happen through ajax). So follow these instructions and tell me what you see

If your using chrome (best if you use this as the instructions are fit to this browser, you will be able to follow along accurately), firefox (need firebug) or Opera (not entirely sure) this feature I'm describing should be fairly close to one of them. IE works but please for the love of god don't use that for development. Please do these in order, do not skip a step.

1. Remove the require_once for server_processing.php, we don't want to include it on index.php, we want to be able to call it through ajax (Which is what "sAjaxSource": "/../server_side/scripts/server_processing.php" is doing).

2. Change this in server_processing.php:

[code]
echo json_encode($output) . "
";
echo "json_enconde($_GET ):
";
echo json_encode($_GET);
[/code]

to this:
[code]
echo json_encode($_GET);
[/code]

I don't care about $output right now.

3. Open up one of the following browsers: Chrome/Firefox/Opera and go to your modified index.php file (from step 1).

4. Press the F12 key and in the box that comes up should be something along the lines of network. This will let us see files as they are loaded in real time. Now, after you click network there should be a row of text on a grey bar near the bottom of this box that popped up and one of the words should be XHR. I want you click on this, this will show only ajax requests. If you can't find it, its ok but it will make it easier to watch the file we want.

5. Refresh the page, this will allow for the network to catch all the files including the ajax update. I want you to find server_processing.php, once you see it click on it. After you click on it the box should change a little, and there should be 4 or 5 small tabs. One of them should say response, I want you to click on this.

6. Copy the text, it will look like 1 long string wrapped in {}. I want it all.

7. Come back here and let me know what it put out!

davisoski

Hi again.

I can't see the server_processing.php in chrome (after F12). See image.

I attach a pdf with a image.

https://dl.dropbox.com/u/66063007/xhr.pdf

it should be possible to share a skeleton project (as simple as possible) which use the table included in v1.9.4???

thanks again.

davisoski

Hi.

I have attached a simplified project of mine.

I use the table in: ..\datatables\examples\examples_support (ajax.sql) so, If you just change parameters in server_processing.php it should be work.


Just take a look at site/login/index.php, where I want to show my datatable and where I'm getting troubles.

[quote]
https://dl.dropbox.com/u/66063007/jeffan24.rar
[/quote]

Thanks

jefffan24

Ok so the first thing you are missing is your not giving dataTables a "outline" to fill in. It has no clue how to make the table with your current code so you need to change you index.php in the login folder to this:

[code]



Engine
Browser
Platform
Version
Grade












[/code]

You need th's inside of the thead tag to give datatables the column names, it knows not to change these.

Then inside the tbody you can need to pass it the same amount of columns so it knows where to put the data. They can be empty as I showed.

Secondly we just need to fix the end of your server_processing file to take out the dump() and remove the $_GET from json_encode, the end of the file should look like this:

[code]
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);
[/code]

This should make it work for you.

davisoski

Hi Jeffan24.

Finally my datatable is working great.

Thanks for comments, help and time. I'll try to learn as much I can to use this component.

Best regards.

jefffan24

Good to hear you got it working, it took me a while to get it my first time, but once you understand it, considering its "power" its fairly easy to work with.

Just gotta know how to read and return the data :)

jstenson

I'm in the same boat as Davis here this is my first time working with json and data tables. I can't seem to get this code to execute to the end. I have a postgres database that I'm trying to use with this code any help would be appreciated
<?php
include "./connect.php";
print "**".$whoami ."**\n";
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* 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( 'term', 'general_description', 'criteria', 'reasoning', 'notes', 'employee', 'department', 'date_updated', 'ownership', 'acronym', 'future_enhancements', 'rec_type', 'date_entered', 'data_source' );

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

/* DB table to use */
$sTable = "misdatadictionary.tbldatadictionaryindex";


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* PG connection
*/
$gaSql['link'] = pg_connect($conn_string) or
die( 'Could not open connection to server' );

$dbc = pg_connect($conn_string) or die("Could not connect to database.");

/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".pg_escape_string( $_GET['iDisplayStart'] ).", ".
pg_escape_string( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);


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