Populating SELECT with server-side processing

barraclm

You can see my site at https://michaelbarraclough.uk/mab/index.html

Although my database is not large, I want to use server-side processing so that I can access my data from anywhere, and any type of client. Everything is going well, except that I cannot figure out how to populate the Select Box at the bottom of column 5 (Type) from all the values in that database column, and not just the values showing on the client side. I suspect that it may be something to do with select.cumulative but I cannot find any examples of showing how to do this. Any pointers would be much appreciated.

This is my configuration

    // Initialize DataTables API object and configure table

    var table = $('#dataList').DataTable({
        columns: [
            { orderable: false },
            null,
            null,
            null,
            { orderable: false }, 
            { orderable: false }, 
            { orderable: false }, 
            { orderable: false }, 
            { orderable: false }, 
            { orderable: false }, 
            { orderable: false },
        ], 

        pageLength: 5,
        processing: true,
        serverSide: true,
        ajax: "fetchData.php",
        order: [[1, 'desc']],
        
        layout: {
            
            top1End: { searchBuilder: { liveSearch: false } },
            
            topStart: {
                pageLength: {
                    menu: [5, 10, 15, 20, 25, 50, { label: 'All', value: -1 }]
                }
            },
            
            topEnd: {
                search: {
                    placeholder: 'Type search here',
                    text: 'Global Search'
                }
            },
            
            bottomStart: "info",
            
            bottomEnd:  "paging"
            
        },
        
        initComplete: function () {
        
            this.api().columns([4]).every(function () {
                var column = this;
 
                // Create select element and listener
                var select = $('<select><option value=""></option></select>')
                    .appendTo($(column.footer()).empty())
                    .on('change', function () {
                        column
                            .search($(this).val(), {exact: true})
                            .draw();
                    });
 
                // Add list of options
                column
                    .data()
                    .unique()
                    .sort()
                    .each(function (d, j) {
                        select.append(
                            '<option value="' + d + '">' + d + '</option>'
                        );
                    });
                    
            }); // end of columns([4])
            
            this.api()
            .columns([1, 2, 3, 5, 6, 7, 8, 10])
            .every(function () {
                var column = this;
                var title = column.footer().textContent;
 
                // Create input element and add event listener
                $('<input type="text" placeholder="Search ' + title + '" />')
                    .appendTo($(column.footer()).empty())
                    .on('keyup change clear', function () {
                        if (column.search() !== this.value) {
                            column.search(this.value).draw();
                        }
                    });
            }); // columns([1, 2, 3, 5, 6, 7, 8, 10])

        }   // end of initComplete function

    });  // end of dataList initialization

kthorngren

I want to use server-side processing so that I can access my data from anywhere

You don't need to enable server side processing for this. If your data set is small you can just use the ajax option to fetch the data from the server.

from all the values in that database column, and not just the values showing on the client side.

When using server side processing the only data at the client or the rows shown on the page. So the code in line 62 will only be able to process the rows displayed on the page. Again remove serverSide: true, and all the data will be fetched to the client.

If your dataset is too large and you need server side processing then you will need to add code to the server side processing script to select all the unique records from the DB to return in a separate object in the JSON response. Look for the draw parameter being 1 (meaning the initial Datatable load) to fetch the select options from the DB. See the SSP protocol docs for more info about the parameters sent. The JSON response would look something like this:

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [ .... ],
  "select": [ ..... ]
}

The select object is additional data added as described above. The second parameter of initComplete is the JSON response. Something like this code should work but I haven't test it.

    initComplete: function ( settings, json ) {

        this.api().columns([4]).every(function () {
            var column = this;
 
            // Create select element and listener
            var select = $('<select><option value=""></option></select>')
                .appendTo($(column.footer()).empty())
                .on('change', function () {
                    column
                        .search($(this).val(), {exact: true})
                        .draw();
                });
 
            // Add list of options
            for (let i = 0; i < json.select.length; i++) {
                   let d = json.select[i];
                   select.append(
                        '<option value="' + d + '">' + d + '</option>'
                    );
            } 
                 
        }); // end of columns([4])
         
....
 
    }   // end of initComplete function

Kevin