Lazy loading (chunk) of data server side script ...

bpmspace

Hello

I have successfully integrated server side processing following this example.
https://datatables.net/examples/data_sources/server_side.html
Thanks again for the help I already had.

However, I am afraid that my data will be loaded about 3500 at a time as the PHP script given in the example has no parameter for offset or limit.
I thought that the parameter “length” would do this automatically for me. So I Thought with the following settings 10 items ("pageLength": 10) would be displayed after total of a 100 ( "length": 100) where loaded from the server.
Since the right initial ordering is already done from the PHP Script (the view selected) this would be perfect for me.

The rest (more then 3400 records) is then loaded when the first 100 were displayed.

But this is NOT how it is working, or?

I must then consequently also assume that e.g. the search is also not performed on the server since also a parameter for WHERE in the PHP script is missing...

Do I have a thinking error? And if not where can I find an example or tutorial with a more complex php script example?

….

     $(document).ready(function() {
         $('#participant').DataTable({
                "processing": true,
                "serverSide": true,
                "ajax": "/api/Particicpant_ServerSide.php",
                "length": 100,
            …
                "pageLength": 10
                }
                );

….

PHP script

….
require( 'ssp.class.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

…

Thanks rob

I also found this , but they are OLD ....
https://datatables.net/forums/discussion/29923/how-to-use-a-lazy-loading-while-having-bulk-data-in-datatable
https://www.datatables.net/forums/discussion/3947/data-lazy-loading

allan

Hi Rob,

I think the key missing ingredient here might be that you need ssp.class.php which is available here: https://github.com/DataTables/DataTablesSrc/blob/master/examples/server_side/scripts/ssp.class.php . It does do the limit / offset / where / etc as needed to implement server-side processing.

If that isn't working for you, can you link to your page so we can help debug the issue.

Allan

bpmspace

Hi Allan
thanks i can confirm it is working - I switched on "loging" on the MariaDB

• SET global general_log = 1;
• SET global log_output = 'table';

SELECT argument FROM mysql.general_log where argument like "%v_comsII_participant_serverside%" and argument NOT like "%mysql.general_log%";

Gives me

SELECT state_action, ....
FROM v_comsII_participant_serverside
ORDER BY coms_participant_id DESC
LIMIT 0, 10

when Pagination is set to 10 **and when set to **100

SELECT state_action, ....
FROM v_comsII_participant_serverside
ORDER BY coms_participant_id DESC
LIMIT 0, 100

when I search something then no limit is set

AMAZING & PERFECT ! CREATE TOOL !

I don't know hwo "you" do that since I see no $_get or $_post handling on the Server Side Script nor in the ssp.class .. so i belive this is ajax magic - at least for me! Can you give me hint, how you sent the parameters from Client to server?

There is one thing i saw in the log you might be interrested in. I can see TWO IDENTICAL entries in the mariaDB log after the "main Select" query with the limit was sent.

SELECT COUNT(coms_participant_id)
FROM v_comsII_participant_serverside

I think one should be enought, but ...
cu rob

DonT forget to switch of logging
SET global general_log = 0;

bpmspace

don't forget to switch of logging
SET global general_log = 0;

allan

I don't know hwo "you" do that since I see no $_get or $_post handling on the Server Side Script nor in the ssp.class .

In the code you showed in your first post:

SSP::simple( $_GET, [...] )

That's how the $_GET parameters are being passed to the SSP class. Furthermore, the data structure sent by DataTables to the server is documented here (should anyone want to implement their own server-side processing script).

There is one thing i saw in the log you might be interrested in. I can see TWO IDENTICAL entries in the mariaDB log after the "main Select" query with the limit was sent.

One is for filtering, one for no filtering. That would be a good optimisation to just not use the second if there is no filter though - thanks for pointing that out.

Allan