Help with pagination

dhyanesh

Greetings,

I have used Bootstrap SB Admin v2.0 that is fetching results from a database. The table from which the results are fetched has more than 2000 records. Due to the volume of records fetched, I am getting "Internal Server Error" as the SB Admin v2.0 datatables plugin is first fetching all the records prior to paginating it.

Is there a possibility that the plugin or the code can be written whereby the table is instantly populated with the first 10 records from the table and then I can click Next to navigate to other pages?

Thank you for assistance on this.

Dhyanesh Ramaiya

prashdeepl

I am also looking for the exact same problem.

allan

Sounds like you want server-side processing. Have a look at the description of the two processing modes DataTables supports in the manual.

Allan

prashdeepl

Hi Allan, In my case, I do not have the codebase of the server to make the server side changes. All I can do is to call the Rest API from the client. The server sends 500 records at a time even if it have more records. In this case how to make a call to the server on every next or previous button click. Can we override the functionality of the next and previous buttons and handle the ajax call and redraw the screen? Please let me know.

allan

Does the API provide the information needed for DataTables server-side processing (total number of records before and after filtering)? If so, you can simply provide an abstraction layer between your API and DataTables. Tell DataTables to use server-side processing (serverSide) but use ajax to use a custom function which you will have to make your API call and transform the data into the form that DataTables wants.

Allan

prashdeepl

Can I write a nodejs app to build the abstraction layer between the API and datatables as mentioned in your reply?

allan

Sure - help yourself :-).

Allan

dhyanesh

Hello Allan / Prashdeepl,

I am still struggling with pagination and request for your help if you could guide me or show me an example to achieve pagination.

I am a newbie trying to use Datatables with Bootstrap. I have a table that has about 4000 records (increasing about 500 every weeks) and I am trying to display these records in a table. I have tried to add server-side processing to the PHP page but the results are displayed which are not paginated and the search box at the top of the table is disappearing.

My php script that is accessed through the web browser contains below:
$(document).ready(function() { $('tbl_test').dataTable( { processing: true, serverSide: true, ajax: '/scripts/server_processing_test.php' }); });

server_processing_test.php contains below: <?php /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Easy set variables */

// DB table to use $table = 'tbl_test';

// Table's primary key $primaryKey = 'TestID';

// Array of database columns which should be read and sent back to DataTables. // The db parameter represents the column name in the database, while the dt // parameter represents the DataTables column identifier. In this case simple // indexes $columns = array( array( 'db' => 'TestID', 'dt' => 0 ), array( 'db' => 'TestCarrier', 'dt' => 1 ), array( 'db' => 'EmailAdd', 'dt' => 9 ), );

// SQL server connection information $sql_details = array( 'user' => 'test_db', 'pass' => 'xxxxxxxxxxx', 'db' => 'test_test', 'host' => 'localhost' );

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

require( 'ssp.class.php' );

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

Please let me know what am I missing to make this work.

Thank you for your help.

Dhyanesh

allan

Please link to a test page showing the problem.

Allan

dhyanesh

Hello Allan,

Is there a possibility that I can send you a screenshot of the page as a personal message with details of the behavior that I am seeing with and without server-side processing?

Thank you once again for your assistance.

Dhyanesh

allan

I think most likely I would need to be able to see the page. The debugger might give the information required, but I'm not sure.

Allan

dhyanesh

Hello Allan,

Thank you for your reply. Debug code is ewehef

Dhyanesh

allan

Debugger reports no tables on the page, so likely there is a Javascript error during the initialisation of the table. Is that the case? I think we will need a link to the page.

Allan

dhyanesh

Hello Allan,

Here is the link to the sample page which has server side processing enabled.
http://core.nyota.co.tz/rpt-sample.php

This is how the server side processing is enabled on the page:

I thank you and appreciate for your help.

Dhyanesh

tangerine

You're initializing your table like this:

$('#tbl_invoices').dataTable( {

but your HTML table has the id "dataTables-example".

dhyanesh

Hello Tangerine,

Thank you for your response. I corrected the error. However, the pagination is still not working. All the results are returned and also the search is not returning the row that is being searched for.

The assistance so far is highly appreciated.

Dhyanesh

dhyanesh

Helllo again,

I managed to resolve the pagination and server side processing

Thank you for your help.

Dhyanesh

dhyanesh

Hello again,

I checked the pagination and server side processing and need to understand a bit more about it. I see that the initial page load is still taking a long time. It is noticeable on slower connections. Is the below initialization correct for server side processing? Is there something more that I need to do so as to optimize and reduce page loading times?

<

script>
$(document).ready(function() {
$('#dataTables-example').dataTable( {
"processing": true,
"serverSide": true,
"order": [[ 0, "desc" ]],
"ajax": "/scripts/server_processing_sample.php",

        "dom": 'T<"clear">lfrtip',
        "tableTools": {
            "sSwfPath": "/scripts/copy_csv_xls_pdf.swf",

            "aButtons": [
            "copy",
            "print",
            {
                "sExtends":    "collection",
                "sButtonText": "Save",
                "aButtons":    ["csv", "xls", "pdf"]
            }
            ]
        }     
     });
});

I thank you for your help.

Dhyanesh

dhyanesh

Hello alan / tangerine,

I come back to you again for help regarding server side processing and pagination.

I have enabled server side processing. However, I started getting 500 Internal Server Error again once the records exceeded a certain number. At present, the table has some 8200+ records.

Please assist to point me in the direction which will allow me to identify the root cause of this so that I do not need to increase the memory limit on php.ini from time to time when the number of records increase.

Thank you very much.

Dhyanesh

allan

There is no way you should be getting a memory error when using server-side processing and only 8200 rows.

The link you gave before appears to be working. Can you please link to a page showing the issue now.

Allan

dhyanesh

Hello Allan,

The page started working after I increased the memory limit on mod_security config on the server.

I see that the initial page load is still taking a long time. It is noticeable on slower connections. Is the below initialization correct for server side processing? Is there something more that I need to do so as to optimize and reduce page loading times?

May be there is still something that I am doing incorrectly.

<script>    
    $(document).ready(function() {
        $('#dataTables-example').dataTable( {
            "processing": true,
            "serverSide": true,
            "order": [[ 0, "desc" ]],
            "ajax": "/scripts/server_processing_invoices.php",
            
            "dom": 'T<"clear">lfrtip',
            "oTableTools": {
                "sSwfPath": "/scripts/copy_csv_xls_pdf.swf",
                
                "aButtons": [
                "xls",
                {
                    "sExtends": "pdf",
                    "sPdfOrientation": "landscape"
                }]
            }            
         });
    });    
    </script>

I would also like to know if there is a way to copy or save all the records to pdf, xls or csv instead of just saving the records that are displayed on the screen. At present, if there are 10 records shown on the screen, and if I copy or save, then it only exports those 10 records.

Your support is very much valued.

Thank you.

Dhyanesh

allan

Is the below initialization correct for server side processing?

Looks fine to me.

I would also like to know if there is a way to copy or save all the records to pdf, xls or csv instead of just saving the records that are displayed on the screen

Not with server-side processing enabled. TableTools is client-side and so it can only export the records that are known on the client-side. When server-side processing is enabled, that is only the rows displayed.

If you want all rows exported you either need to disable server-side processing, or have the server create the file and download it to the client.

Allan

dhyanesh

Hello Allan,

Thank you for your response. If the initialization is correct, what could still be causing the Internal Server Error? Could it be the server-side processing script?

Dhyanesh

allan

what could still be causing the Internal Server Error? Could it be the server-side processing script?

Yes. You would need to check the server's error log to see what the error is. The client-side initialisation will have little effect on, for example, a syntax error at the server-side.

Allan

dhyanesh

Hello Allan,

I was waiting for the problem to recur again. Below is what I am getting in the error log:

[Wed Aug 27 23:57:50 2014] [error] [client 1.2.3.4] ModSecurity: Rule 3468768 [id "1500171"][file "/usr/local/apache/conf/modsec2.user.conf"][line "361"] - Execution error - PCRE limits exceeded (-8): (null). [hostname "x.y.z"] [uri "/scripts/server_processing_invoices.php"] [unique_id "U-5GTtQS4CoAAGooPMcAAAAF"]

Please let me know what else can I check to resolve this issue. Increasing the memory limit in mod_security configuration resolves the problem (temporarily)

Thank you for your help.

Dhyanesh

dhyanesh

Hello again,

It is actually the ResponseBodyLimit that is increased to solve the Internal Server Error.

Dhyanesh

dhyanesh

Hello Allan,

Requesting your help again on this one. What else do I check to resolve this issue?

Thank you for your help.

Dhyanesh

allan

Hi Dhyanesh,

I had thought you had resolved this issue by increasing the ResponseBodyLimit. That isn't an issue with DataTables, but rather than server configuration. Are you still having problems with that? You could try using POST (rather than GET) which will allow a lot more data to be updated (again, depending on your server configuration).

If you could link me to the page I would be happy to give you a quote for configuring the server or suggesting any other modifications once I can see and diagnose the issue.

Allan