Server Side Processing, more info

fbas

Can you explain more on server side processing?

I copied and used the code from http://datatables.net/examples/data_sources/server_side.html on my own database (minor changes, obviously, to fit my database columns), with sAjaxSource set.

The next/previous buttons did not function, and my custom sorting routines were ignored (I guess that's in favor of asking the server to sort?). other items, like changing number of displayed rows, work great.

when I set bServerSide to false, the buttons work and sorting routines work, but it's much slower (could just be performance of the sorting routines). I'd like to make server side functionality work, though, since it appears without bServerSide I'll have to manage the query with LIMIT myself. [database has 22800 entries, but I've set a default LIMIT in the php server side code.]

other options I'm using, which I'm assuming don't conflict: ColReorder

server is apache, mysql, php, on some unix platform. data is proprietary, so I'm reluctant to provide a link.

allan

What server-side processing script did you use - something like this demo one I presume: http://datatables.net/development/server-side/php_mysql ?

With ColReorder you need to give each column a name with the sName parameter as shown here: http://datatables.net/release-datatables/extras/ColReorder/server_side.html .

It would be worth looking at the JSON reply from the server to make to it is similar to the ones that examples return. Also the server-side processing documentation is here: http://datatables.net/usage/server-side .

Allan

fbas

Yes, that's the php code I used (http://datatables.net/development/server-side/php_mysql).

I'm not very concerned about the sorting, but if I can get the next/previous buttons to work, I'd be happy. Currently, the query returns just the number of rows in the the drop down list. The table correctly reports that is has 10 of 10 records and so the buttons don't run for more data, but I'd like to enable the buttons to submit the next LIMIT query. I'm guessing there is a simple way to do this but I just haven't seen it yet.

allan

Can you show me the JSON that is being returned form the server? You can remove the aaData content if you want - it's the rest of it I'm interested in.

Allan

fbas

yes. I used the same php, and aside from the aaData, it's rather simple:

{"sEcho":0,"iTotalRecords":"22785","iTotalDisplayRecords":"50","aaData":...}

the 50 is the artificial default limit I've thrown in the php to keep queries short, but with bServerSide set true, clearly the value is overridden in $_Get/$_Post values. If there's a way I can get it from the debugger from a real (ajax) call, let me know and I'll paste it here.

allan

Okay so two things:

1. sEcho should never be 0 - in the first draw it is '1', and increments by one for each draw there after. You need to echo back what DataTables is sending (cast as an integer for security).

2. Unless you have a filter applied iTotalRecords and iTotalDisplayRecords should be exactly the same. If DataTables wants to know how many records you passed back it can do aaData.length :-). The documentation for these parameters should explain it a bit more.

Allan

allan

p.s. use Firebug to see the XHR's going in and out of the browser - that might explain the sEcho issue.

fbas

I realize that helping debug this without seeing the page or code is a bit problematic, so I've made an abbreviated version of what I'm working on:

http://www.beg.utexas.edu/qa_/test_db_json.php

which pulls from

http://www.beg.utexas.edu/qa_/db_json.php (?iDisplayStart=0&iDisplayLength=10)

on the test_db_json page, what I'd like to change is the buttons on the bottom. since this query has gotten all it's rows (limited to 10) the arrows don't request the next set of data, like I thought it would. so I need to kick off another query for those buttons, with just the DisplayStart variable modified. I was assuming there'd be a DataTables way to handle this, rather than overriding the button's click event myself.

fbas

that sEcho was mine from a parameter-less fetch of the page. I've looked at the debugger and the system is returning sEcho: 1 (this is the same php code from your example.. isset($_GET['sEcho']) ? $sEcho = intval($_GET['sEcho']) : $sEcho = 0;)

fbas

ahh, I got it sorted out. changed line 130 of the php code

[code]$sQuery = "
SELECT FOUND_ROWS()
";[/code]

to
[code] $sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable $sWhere
";
[/code]

returning the number of records available, irrespective of the LIMIT parameters

you should update this listing.

allan

If FOUND_ROWS() failed then likely SQL_CALC_FOUND_ROWS wasn't included in the first query, or the custom limit upset it. As you can see in the demos on this site the script works okay. The intention with SQL_CALC_FOUND_ROWS was to save the need for a COUNT() thus reducing the overhead.

Allan

fbas

FOUND_ROWS was succeeding, but it was based on the number of rows included with DisplayStart and DisplayLength factored in, which was always just that one page worth of data, rather than all eligible rows. so I took out the $sLimit when calculating the number of rows (and $sOrder was meaningless in the SELECT COUNT() query as well):

[code]
$sQuery = "
SELECT ".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()
"; // change this to SELECT COUNT(*) FROM $sTable $sWhere
[/code]

this view from MySQL command line illustrates this:
[code]

mysql> select qaid from qabook limit 10;
+-------+
| qaid |
+-------+
| A_1 |
| A_10 |
| A_100 |
| A_101 |
| A_102 |
| A_103 |
| A_104 |
| A_105 |
| A_106 |
| A_107 |
+-------+
10 rows in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 10 |
+--------------+
1 row in set (0.00 sec)
[/code]