data() won't return ALL data from datatable (but only data for current page) - Serverside and paging

dudanesk

Hi everyone,

I have 4 datatables on my webpage, but will focus on 2 of them. I have a datatable "country" and a datatable "state". Both datatables are built in a very similar way: 2 columns (1st column: checkbox, 2nd column: name (of the country, or of the state), with pagination active). Both datatables are fed from a database through server side processing. All rows in each datatable are selected by default upon initialization. So far, everything works as expected. The rows (AKA states) displayed in datatable 'state' depends on the rows (AKA countries) selected in datatable "country". I use data() to retrieve the IDs (for selected rows) from the datatable "country" and use those IDs to feed datatable "state". And here is my issue: data() only returns the IDs of the current page, not the IDs from the whole "country" datatable. According to the documentation, I undertand that data() should return ALL data. I think I need a fresh pair of eyes to solve this issue.

HTML:

                    <table id="filterCountry" class="display" width="175px">
                        <thead>
                            <tr>
                                <th width="25px"></th>
                                <th width="175px">Country</th>
                            </tr>
                        </thead>
                    </TABLE>
                </td>
                <td class="blackFilter"><br>
                    <table id="filterState" class="display">
                        <thead>
                            <tr>
                                <th width="25px"></th>
                                <th width="175px">State</th>
                            </tr>
                        </thead>
                    </TABLE>

JAVASCRIPT:

        var countryTable = $('#filterCountry').DataTable( {
            "processing": true,
            "serverSide": true,
            "info": false,
            "bLengthChange": false,
            "pagingType": "numbers",
            'order': [[1, 'asc']],
            'columnDefs': [
                {
                    'targets': 0,
                    'checkboxes': {'selectRow': true},
                    'createdCell':  function (td, cellData, rowData, row, col){
                        this.api().cell(td).checkboxes.select();
                    },
                },
                {'targets': 1, 'orderable': false},
            ],
            select: {
                style: 'multi'
            },
            "ajax": {
                "url": "m_country.php",
                "type": "POST",
                "data": function ( d ) {
                    var retrieveIDs="";
                    var myIDs = continentTable.rows('.selected').data().toArray();
                    for ( var i=0 ; i<myIDs.length ; i++ ){
                        retrieveIDs += myIDs[i][0]+",";
                    }
                    d.continentIDs = retrieveIDs;
                }
            },
        } );
        
        var stateTable = $('#filterState').DataTable( {
            "processing": true,
            "serverSide": true,
            "info": false,
            "bLengthChange": false,
            "pagingType": "numbers",
            'order': [[1, 'asc']],
            'columnDefs': [
                {
                    'targets': 0,
                    'checkboxes': {'selectRow': true},
                    'createdCell':  function (td, cellData, rowData, row, col){
                        this.api().cell(td).checkboxes.select();
                    },
                },
                {'targets': 1, 'orderable': false},
            ],
            select: {
                style: 'multi'
            },
            "ajax": {
                "url": "m_state.php",
                "type": "POST",
                "data": function ( d ) {
                    var retrieveIDs="";
                    var myIDs = countryTable.rows('.selected').data().toArray();
                    for ( var i=0 ; i<myIDs.length ; i++ ){
                        retrieveIDs += myIDs[i][0]+",";
                    }
                    d.countryIDs = retrieveIDs;
                    //alert(countryTable.rows( { selected: true } ).data().length);                 
                }
            },
        } );

Here is link to the webpage:
http://one-world.comeze.com/m_test.php

kthorngren

The data() docs state "Get the data for the whole table". This means the table in the client. With server side processing the only data in the client is the page being displayed. In order to get all the data from your database the data() would have to fetch the all the data from your server's database.

Are the data sets you have too large to turn off server side processing?

Kevin

dudanesk

Well, quite frankly, my database is not that large and probably never will be. But I'm new to datatables (and not that good at coding in the first place), and when I looked for exemples of datatables fed from a database (which is my case), I could only find (or understand) server-side processing stuff. If I switch to client side now, what are the basics steps for me to follow. I obviously tried to just turn server-side processing off without changing anything else, but as expected, nothing works anymore and no data is being loaded. Do I pretty much have to start from scratch again?

dudanesk

Okay, so I started again using client-side processing. I'm learning from scratch again. I did this very simple example that works but I would love if someone could have a look at it and tell me whether that's the way to do it, because I have serious doubts on my ajax script, even though it works:

HTML:

                    <table id="filterContinent" class="display" width="175px">
                        <thead>
                            <tr>
                                <th width="25px"></th>
                                <th width="175px">Continent</th>
                            </tr>
                        </thead>
                    </TABLE>

JAVASCRIPT:

    $(document).ready(function() {

        var continentTable = $('#filterContinent').DataTable( {
            "ajax": 'c_continent.php',
            'columnDefs': [
                {
                    'targets': 0,
                    'checkboxes': {'selectRow': true},
                    'createdCell':  function (td, cellData, rowData, row, col){
                        this.api().cell(td).checkboxes.select();
                    },
                },
            ],
        } );
        
    } );

AJAX SCRIPT ('c_continent.php):

<?php

    //Include database configuration file
    include('dbConfig.php');
    
    $data = '{"data":[';
    
    //get continent rows
    $queryContinent = $db->query("SELECT * FROM continent");
    if($queryContinent->num_rows > 0){
        while($rowContinent = $queryContinent->fetch_assoc()){
            $data .= '[' . $rowContinent['id'] . ',"' . $rowContinent['continent_name'] . '"],';
        }
    }
    $data = substr($data, 0, -1);
    $data .=  "]}";
    echo($data);

?>

allan

Yeah the Ajax script will work, but its fragile. Don't built the JSON string yourself - simply do:

<?php

    //Include database configuration file
    include('dbConfig.php');
     
    $queryContinent = $db->query("SELECT * FROM continent");
    echo json_encode( array(
      "data" => $queryContinent->fetch_all();
    ) );
?>

Then in your client-side DataTable configuration use the columns.data option to tell DataTables the column name from which you want to display data from in each column.

Documentation for that is available here.

You aren't alone in jumping straight to server-side processing. I'd appreciate your insight as to why you did that, so I can hopefully update the documentation to stop that happening unless people actually need it. Server-side processing is a lot more complicated than client-side.

Allan

dudanesk

That looks a lot better, thank you.

As to why I chose server-side in the first place. Datatables is entirely new to me, and when I get into something new like that, the best way for me to learn is through examples. I identify my need, try to find example online that is a match, go through the example and use the documentation to understand that example and then adapt it to my particular case. Here, I need my 4 datatables to be fed from my database and I have a parent/child relationship between all of them. But, when I look for such example, I always get a server-side case, I couldn't find an example of client-side datatable that involved database and sql queries (which is not say there are none after all...). Or I find an example for parent/child relationship but it involves editor mode which I don't want in my case and makes it a lot harder for me to understand the whole thing. I'm not good enough to start coding from scratch by myself, I need to follow an example, or more often a mix of different examples. But make no mistakes, my issue has to do with my lack of knowledge, because you guys did a fabulous work here with this datatable plugin, there's a lot of documentation and the support community is awesome. I just need to build better skills and unfortunately for you guys, I need to ask many questions :-)

allan

Thanks for the feedback. I've got thoughts about how to introduce people to DataTables (i.e. starting out programming with it) that I think would help in such cases. Hopefully I'll get a chance to put them in place!

Allan

dudanesk

A sort of datatables for dummies :-)

allan

More a learning course! Not sure exactly what shape it will be yet, but I'm actually quite excited about it ('cos I'm nerdy like that).

Allan