Help with pagination

Help with pagination

dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • prashdeeplprashdeepl Posts: 5Questions: 2Answers: 0

    I am also looking for the exact same problem.

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

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

    Allan

  • prashdeeplprashdeepl Posts: 5Questions: 2Answers: 0

    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.

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • prashdeeplprashdeepl Posts: 5Questions: 2Answers: 0

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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    Sure - help yourself :-).

    Allan

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    Please link to a test page showing the problem.

    Allan

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    Hello Allan,

    Thank you for your reply. Debug code is ewehef

    Dhyanesh

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    edited June 2014 Answer ✓

    You're initializing your table like this:

    $('#tbl_invoices').dataTable( {

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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    Helllo again,

    I managed to resolve the pagination and server side processing

    Thank you for your help.

    Dhyanesh

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0
    edited July 2014

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    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

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    Hello again,

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

    Dhyanesh

  • dhyaneshdhyanesh Posts: 30Questions: 8Answers: 0

    Hello Allan,

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

    Thank you for your help.

    Dhyanesh

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

This discussion has been closed.