Add colunms filter to an existing server-side DataTables

Couin

HJi,

I built a table using DataTables in server-side mode.

Here is the script section :

$(document).ready(function() {
    var dataTable = $('#data-grid').DataTable( {
        "pagingType": "input",
        "pageLength": 25,   
        "processing": true,
        "serverSide": true,     
        "language": {
        "info": "Affichage des résultats _START_ à  _END_ sur _TOTAL_",
        "infoEmpty": "",
        "infoFiltered": "(filtrés sur un total de _MAX_ résultat)",
        "thousands": "",
        "loadingRecords": "Chargement...",
        "processing": "",
        "search": "Recherche:",
        "zeroRecords": "Aucun résultat trouvé",
        "first": "Premiere",
        "last": "Derniere",
        "paginate": {
        "first": "|<",
        "previous": "<<",
        "next": ">>",
        "last": ">|"
        },
        "lengthMenu": 'Afficher <select>'+
        '<option value="10">10</option>'+
        '<option value="25">25</option>'+
        '<option value="50">50</option>'+
        '<option value="100">100</option>'+
        '<option value="500">500</option>'+
        '<option value="-1">Tous</option>'+
        '</select> résultats par page'
        },
        columnDefs: [
        { className: "dt-body-nowrap", "targets": [ 0,1,2,3,4 ] }           
        ],
        "order": [ 0, "desc" ],
        "ajax":{
            url :"data.php", // json datasource
            error: function(){  // error handling
                $(".data-grid-error").html("");
                $("#data-grid").append('<tbody class="data-grid-error"><tr><th colspan="6">Aucun résultat trouvé sur le serveur</th></tr></tbody>');
                $("#data-grid_processing").css("display","none");
            }
        }
    } );
} );

Here is the table head :

<table id="data-grid"  cellpadding="0" cellspacing="0" border="1" class="display" width="99%">
        <thead>
            <tr>
            <th align="center" width="10">Date</th>
            <th align="center" width="10">Client</th>
            <th align="center" width="10">Version</th>
            <th align="center" width="10">Utilisateur</th>
            <th align="center" width="10">#SN</th>
            <th align="center">Evenement</th>
            </tr>
        </thead>
</table>

Here is the data.php code :

    header('Content-Type: text/json; charset=UTF-8');
    [...]Database connexion[...]
    $requestData= $_REQUEST;
    $columns = array( 
        0 =>'dt', 
        1 => 'client',
        2=> 'version',
        3=> 'user',
        4=> "serial",
        5=> 'event'
    );
    
    $sql = "SELECT COUNT(id) AS totalData FROM events";
    $query=mysqli_query($dbwrite, $sql);
    while( $row=mysqli_fetch_array($query) ) {  
    $totalData = $row["totalData"];
    }
    $totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.
    
    $sql = "SELECT * FROM events LEFT JOIN users ON events.user = users.trigramme ORDER BY events.id DESC";
    $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']." ";
    $query=mysqli_query($dbwrite, $sql);
    $data = array();
    while( $row=mysqli_fetch_array($query) ) {  // preparing an array
        $nestedData=array(); 
        $id = $row["id"];
        $nestedData[] = $row["dt"];
        $nestedData[] = $row["client"];
        $nestedData[] = $row["version"];
        $nestedData[] = $row["username"];
        $nestedData[] = $row["serial"];
        $nestedData[] = $row["event"];
        $data[] = $nestedData;
    }
    
    $json_data = array(
                "draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
                "recordsTotal"    => intval( $totalData ),  // total number of records
                "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
                "data"            => $data   // total data array
                );
    echo json_encode($json_data);  // send data as json format

Here is a sql file to test with some datas :

https://cdn.fbsbx.com/v/t59.2708-21/156349061_465269861322154_6738949693148684510_n.sql/basedonnees.sql?_nc_cat=104&ccb=3&_nc_sid=0cab14&_nc_ohc=hACGm4B4t8EAX9zDD58&_nc_ht=cdn.fbsbx.com&oh=875c08c4e89669238c6dfbdf936658cd&oe=60431F9B&dl=1

I would add an input text field to search by column, I search through a lot of topics but I found only anwsers that quotes link to https://datatables.net/examples/api/multi_filter.html but in the example, it looks to be client-side.

I tried somes things to adapta to server side but of course, any things was not good (nothing happens).

Is there a server-side example with multiple input filters ?

Thanks
Couin

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

kthorngren

The solution in the example uses column().search() which supports both client and server side processing. The example works with server side processing.

I'm not sure about your PHP code (I don't use PHP) but it doesn't look like you added anything for column searches. Take a look at the Datatables sample SSP script ssp.class.php to see what is used in the Server Side examples.

Also look at the Server Side Processing Protocol docs to see how the column searches are passed to the server.

Kevin

allan

Just to follow on from what Kevin has said, this is the bit of the SSP class that you would want to base any filtering code that you put into your own script on.

That said, I think you'd be better off using the SSP script, or Editor's libraries to start you the bother of writing the code.

Allan

Couin

Hi friends,

Thanks for answers

First, sorry for bad message formating, Colin, but I got some difficulties to obtain the good presentation

I succeeded (not without difficulties lol) to a first result with SSP class.

I will probably have to hard customize/rewrite some code because :
- I need to get some data results from a SQL request with LEFT JOIN with 2 others tables.
- Datas with accentued characters are not displayed (not just missing accentued characters but the whole data).

Also, is there a way to set a different width for each search input fields ?

Thanks tfor your great help
Couin

colin

For the width, this thread should help, as it's CSS suggesting methods to achieve that.

Colin

Couin

Hi Colin,

I'll check the thread, otherwise I managed like this :

// Setup - add a text input to each footer cell
$('#example tfoot th').each( function () {
var title = $('#example thead th').eq( $(this).index() ).text();
if ( title == 'Date' ) { iwidth = 180; }
if ( title == 'Client' ) { iwidth = 100; }
if ( title == 'Version' ) { iwidth = 60; }
if ( title == 'Utilisateur' ) { iwidth = 100; }
if ( title == '#SN' ) { iwidth = 100; }
if ( title == '#Evenement' ) { iwidth = 300; }
$(this).html( '<input type="text" style="width:'+iwidth+'px" />' );
} );

PS : I definitivly have problems to format "Code" areas