Server Side Processing, more info
Server Side Processing, more info
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.
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.
This discussion has been closed.
Replies
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
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
{"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.
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
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.
[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
[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]