DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Datatable - Need to display all pagination value for total number of records.

Datatable - Need to display all pagination value for total number of records.

vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0
in Free community support

I am using datatable in my project. I have 50,000 records in database tables name is contact.
Using php and ajax i want to show this in my web page.
when page load i am showing 10 records only.
My problem is i want to display all pagination links. currently only 1 link is display for pagination for 10 records.
i need 1 2 3 4 5 6.........and more for all the records 50,000.

Is there any hardcoded setting we can do for total records and using this hardcoded value pagination will display automatically for all records.

This question has accepted answers - jump to:

«1

Answers

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    This example should help - it's showing alternative paging. If it doesn't, look at other options with option pagingType, or you can create your own paging plugin,

    Colin

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    pagingType and example did not work.
    I am trying with pagin plugin.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    plugin also not working

  • kthorngrenkthorngren Posts: 21,685Questions: 26Answers: 5,020

    Please provide a link to your page or a test case showing the issues so we can help.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    I am using php and ajax for showing records from database to datatable.

    I have 50,000 records in database.

    if we show 50,000 records at a time then it is taking too much time.
    so what i did, i am fetching only 10 records from database and showing into datatable.

    Now First Scenario:
    -> if we show 50,000 records at a time, then all pagination links at the bottom will display.
    Like: 1 2 3 4 5 6 7 8 9 10........5000

    Second Scenario:
    -> If we show only 10 records at a time the pagination show only 1 link
    Lik: 1

    What I want:
    when page will load 10 records will display(same as scenario 2)
    But i need pagination same as Scenario 1:
    Like: 1 2 3 4 5 6 7 8 9 10 ......5000

    Is it possible ?

    Can you please give me your email id so i can share the files.

  • kthorngrenkthorngren Posts: 21,685Questions: 26Answers: 5,020
    edited October 2020

    Sorry I wasn't understanding your question. Server Side Processing mode is used for server side paging. Here are some examples. The protocol has builtin parameters for fetching the total records and will display the paging and information fields correctly. The ssp.class.php is used in the examples and can be used by you if you wish.

    Kevin

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    ssp.class.php worked for me. Thanks you so much :smile:

    But below problem is there:

    in ssp.class.php i am using complex() function and in this when we pass where parameter me getting below error:
    column CON_TY.field_213 not found.

    my code is:
    $filter_val_html = 'Product Quality';
    //$supplier_id = $REQUEST['supplier_id'];
    $filter_val_query = ucwords(str_replace('    ', ' ', $filter_val_html));

    $whereResult="";
    if($filter_val_html != "all_contacts")
    $whereResult = " CON_TY.field_213 = '$filter_val_query'";

    $where_query = '';
    echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns , $whereResult, $where_query)
    );

    Also how to pass variable value from view page to server.php page.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0
    edited October 2020

    I have used my own custom query inside complex function.

    My query is join query, which join four tables in the database.

    After adding custom query inside complex function, search functionality also not working. It is giving me error "Invalid Json"

    Can you please help me into this ?

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    I have added my custom select query inside complex function.

  • allanallan Posts: 64,032Questions: 1Answers: 10,555 Site admin

    That sounds like the right why to handle it. The SSP class is really only meant to be a basic functional demo. For more complex use cases, including simple joins, you need to modify the script.

    Allan

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    ok....Thank you so much for your support.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Allan,

    Now i am facing one small issue with same. Requesting to you please guide me.

    I am using ssp.class.php file.

    Scenario 1:

    --> I have 10,000 records in database.

    --> When i am using select * from customer table query, pagination looks like below:

    --> 1 2 3 4 5 6 7 8....1000

    --> This scenario is correct.

    Scenario 2 (This is problem)

    --> I have 10,000 records in database.

    --> when i am using select * from customer where salary > $50, which returns 20 records from database. pagination looks like below:

    --> 1 2 3 4 5 6 7 8....1000 which is wrong.

    it should be -> 1 2

    Solution which i am trying:

    "recordsTotal" => intval( $recordsTotal ),
    "recordsFiltered" => intval( $recordsFiltered ),

    If i give hardcode value for $recordsFiltered 20 then pagination looks good for second scenario.

    but if i store value 20 in variable and pass this variable to here "recordsFiltered" => intval( $recordsFiltered ), when page load pagination look good but when i click second link pagination which 2 then pagination disappear.

    Can you please guide me, how to solve this issue.

    I need only 2 pagination links for 20 records.

  • colincolin Posts: 15,240Questions: 1Answers: 2,599
    Answer ✓

    '''
    --> when i am using select * from customer where salary > $50, which returns 20 records from database. pagination looks like below:

    --> 1 2 3 4 5 6 7 8....1000 which is wrong.

    it should be -> 1 2
    '''

    Have you confirmed the query is only returning the expected data?

    If so, can you post the JSON returned from that script here, please,

    Colin

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    I am using join query.(joining 4 four tables for query)

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    yesterday i have sent comment but its still not approved.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Allen need urgent help, i am getting "can not read propetry of 'lengh' of undefined"

    "ajax": {
    url: "server.php",

                "dataSrc": function ( d ) {
                    console.log(d);
                    //var return_data = JSON.parse( d );
                    //var return_data = JSON.parse( d );
                    //console.log(return_data);
            }

            },
  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    {"draw":1,"recordsTotal":66625,"recordsFiltered":66625,
    "data":[["1","2","0005001","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","509189700","DFHG","SSJA9R","Sweden","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","India","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","India","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","India","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""]]}

    {"draw":2,"recordsTotal":66625,"recordsFiltered":66625,
    "data":[["1","2","8884444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","0005101","sdfsdf","4686","S54","SSJA9R","Sweden","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","8884444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","0005101","sdfsdf","4686","S54","SSJA9R","Sweden","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","8884444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"]]}

    {"draw":3,"recordsTotal":66625,"recordsFiltered":66625,"data":[]}

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Colin,

    Many thanks for your quick reply.

    Here is below JSON returned data:

    When i click on 1 first link on pagination below json data return:

    {"draw":1,"recordsTotal":66625,"recordsFiltered":66625,
    "data":[["1","2","0005001","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","509189700","DFHG","SSJA9R","Sweden","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","India","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","India","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","4444","India","","SOL","BBJHG7","India","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","2222","sdfsd","4686","DFHG","SSJA9R","India","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""]]}

    when i click on 2 second pagination link below json data return:

    {"draw":2,"recordsTotal":66625,"recordsFiltered":66625,
    "data":[["1","2","8884444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","0005101","sdfsdf","4686","S54","SSJA9R","Sweden","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","8884444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"],
    ["1","2","0005101","sdfsdf","4686","S54","SSJA9R","Sweden","Admin","S35729","Christer","Johansson","sdfsd","1","2","09\/28\/2020 14:04",""],
    ["1","2","8884444","India","","SOL","BBJHG7","Sweden","Admin","S33212","vishal","Troka","sdfsd","1","2","09\/28\/2020 08:29","10\/21\/2020 09:21"]]}

    When i click on 3 pagination link below json data return:

    {"draw":3,"recordsTotal":66625,"recordsFiltered":66625,"data":[]}

    if only 2 pagination link have data then no need to display other pagination links.

    Total paginations links are:

    1 2 3 4 5 6 7 8.......6625

    I need only 1 2 pagination links. Because only 2 pagination links have data.

    How to do this ?

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    Yep, your recordsFiltered":66625 is still showing the full number, so that will be used for the paging controls. That should be 20 if only 20 records are returned.

    The protocol is discussed here. Also see examples here.

    Cheers,

    Colin

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Thanks Colin for your reply.....i will check and update You.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Colin,

    I want to set $columns variable value as per the condition.

    $columns = array(
    array( 'db' => 'Action1', 'dt' => 0 ),
    array( 'db' => 'Action', 'dt' => 1 )
    ,);

    if($value=="2"){
    $columns[] = array( 'db' => 'field_213', 'dt' => 2);
    }
    $columns[] = array( 'db' => 'con_uname', 'dt' => 3 );

    But it is not working.

    when value == 2,it is giving me below error:

    "Requested unknown parameter 2 for row 0, column 8"

  • allanallan Posts: 64,032Questions: 1Answers: 10,555 Site admin

    If $value is 2, then you aren't getting any data from the server for column index 2. Which is exactly what the error is saying. Checking the JSON response from the server should confirm that.

    You might want to set columns.defaultContent for that column to be an empty string.

    Allan

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Allen,

    Thanks for your reply.

    I have below query for search functionality:

    My data is coming from four different tables, so i am using join query.

    Now when i am going to search anything then it is giving me below error:

    "Unknown column "field_213" in where clause "

    Can you please guide me here.

    Also, can you please provide me link of example where search with join query available.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Allen,

    I have solved above problem using custom search query.

    Thanks

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Allen,

    Sorting column functionality not working in my application.

    when i click on ascending arrow for 2nd column in $_GET[] method i am getting below values.

    order[0][column]: 2
    order[0][dir]: asc

    but i need actual table column name instead of value 2. So i can create sorting query dynamically.

    How can i get table column name ?

    Thanks

  • colincolin Posts: 15,240Questions: 1Answers: 2,599
    Answer ✓

    All of the server-side processing parameters back to the server are on the column number. You could use IDs, as in this example, and map the data attribute from the columns array,

    Colin

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    Hi Colin,

    Many thanks for your reply.

    Now, I am facing one CSS issue with th column width.

    <th> column width is different and all below column width is different.

    This issue is because below css automatically added to th column:
    element.style{
    width: 44 px;
    }

    I have used below solution when page load it's work, but still it is not working after click on ascending and descending arrow:

    setTimeout(function() {
    $(".sorting_asc").css({width: 'auto'});
    }, 100)

    Please guide me here.

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0

    i have missed one line in above code
    "drawCallback": function( settings ) {
    setTimeout(function() {
    $(".sorting_asc").css({width: 'auto'});
    }, 100)
    },

  • allanallan Posts: 64,032Questions: 1Answers: 10,555 Site admin

    Please link to a page showing the issue. Width issues are basically impossible to fix without being able to see what is going on.

    Thanks,
    Allan

  • vishalkalbandevishalkalbande Posts: 22Questions: 1Answers: 0
    edited November 2020

    website is not public. Unable to share the link.
    Please suggest any other way, we can resolve this issue.

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.