serverSide and paging problem

marwi

Hi
I'm using option pageLength: 20 and it works fine: At the bottom of the table it shows:
Showing 1 to 20 of 426 entries

Now I have tried to switch to server side processing, so I have added the option serverSide: true . And now, the data table shows all the 426 records on one page and at the bottom it shows:
Showing 0 to 0 of 0 entries (filtered from NaN total entries)

Is there anything I need to consider when using serverSide true regarding to paging?

Regards,
marwi

colin

Hi @marwi ,

Take a look at the Ajax tab on this example here. The server-side script needs to return the expected data based on the request - I suspect your script isn't returning what's expected.

Cheers,

Colin

marwi

Hi Colin,
I am currently using the default PHP library provided here. Do I have to create a custom PHP script as shown in the example you have linked to?
Regards,
marwi

colin

My link was the standard one used on the live site, so guess it'll be same for you

C

allan

Is there anything I need to consider when using serverSide true regarding to paging?

Are you sending the data to the server from DataTables as a POST request?

If not, then try changing it - I don't know what your server-side script looks like at the moment, but normally Editor scripts use POST data.

If that doesn't help, can you link to a page showing the issue please.

Allan

marwi

temporary Link to test page

colin

Hi @marwi ,

Yep, on that link I sent before, if you look at the Ajax response, you'll see the header to the returned data:

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [
    [
      "Airi",
      "Satou",
      "Accountant",
      "Tokyo",
      "28th Nov 08",
      "$162,700"
    ],
<snip>

Your response doesn't contain the draw, recordsTotal or recordsFiltered, which is needed for serverSide processing. This is yours here:

{"data":[{"DT_RowId":"row_1","shippingdms" <snip>

If you add that, it should sort you out.

Cheers,

Colin

marwi

The PHP script on your example page looks totally different to the one I'm currently using (based on DataTables's Generator's output):

```
<?php
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'shippingdms', 'id' )
->fields(
Field::inst( 'shippingdms.id' )->set(false),
Field::inst( 'shippingdms.paymnt_status' ),
Field::inst( 'shippingdms.acct_status' ),
Field::inst( 'shippingdms.invoice' ),
Field::inst( 'shippingdms.downloads' ),
Field::inst( 'shippingdms.customer' ),
Field::inst( 'shippingdms.factory' ),
Field::inst( 'shippingdms.inspection' ),
Field::inst( 'shippingdms.mi_amount' ),
Field::inst( 'shippingdms.created' ),
Field::inst( 'shippingdms.revised' )
)
->process( $_POST )
->json();

<?php > ``` ?>

Do I have to add some option here? Do I somehow need this SSP:simple() call?

Regards,
marwi

colin

Hi marwi,

It looks like the issue is that you are using ->process( $_POST ) but the DataTable's Ajax request is a GET. As Allan said above, you need to use that POST.

Take a look at this example here, in particular line 37 of the code shown below the table. This shows how to use a POST.

Cheers,

Colin

marwi

OK, I have added type: 'POST', like in line 37. In my example (see above temporary link; this page is using MySQL DB), the records count on the bottom of the table seems to work fine now. But it seems to ignore the option pageLength: 2, as it shows me all the 5 records.

If I do the same on the production environment (using SQL Svr DB), I'm getting the following error message:

DataTables warning: table id=shippingdms - 
SQLSTATE[42000]: 
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]
Incorrect syntax near 'OFFSET'.

allan

What version of SQL Server are you using? The server-side processing support in the Editor PHP libraries for SQL Server requires 2012 or newer (which added the OFFSET syntax).

Allan

marwi

Oh I see.. our version is 2008 R2. :-(
Therefore this feature is not possible without amending the library scripts