Custom Range Search - Server Side

pettedemon

Hi,
I create a server side table.
I add search on every column.

this is how I add the search

            var table =   $('#canzoni_player').DataTable( {
        "processing": true,
        "serverSide": true,
                "autoWidth": false,


        "ajax": "scripts/tabella_playlist.php",
            "lengthMenu": [[18, 50,100, 250, 500, -1], [18, 50,100, 250, 500, "Tutti"]],
        "language": {
            "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Italian.json"
        },




        initComplete: function() {
        var api = this.api();




      // Apply the search
      api.columns([0, 1, 3, 4]).every(function() {
        var that = this;



        $('input', this.footer()).on('keyup change', function() {
          if (that.search() !== this.value) {
            that
              .search(this.value)
              .draw();
          }
        });
      });
    }

So I want to add a range filter only in one column
- I add the text input

<tbody><tr>
            <td>Minimo BPM</td>
            <td><input type="text" id="min" name="min"></td>
        </tr>
        <tr>
            <td>Massimo BPM</td>
            <td><input type="text" id="max" name="max"></td>
        </tr>
    </tbody>

I add the extended search for the column 3

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var min = parseInt( $('#min').val(), 10 );
        var max = parseInt( $('#max').val(), 10 );
        var age = parseFloat( data[3] ) || 0; // use data for the age column

        if ( ( isNaN( min ) && isNaN( max ) ) ||
             ( isNaN( min ) && age <= max ) ||
             ( min <= age   && isNaN( max ) ) ||
             ( min <= age   && age <= max ) )
        {
            return true;
        }
        return false;
    }
);

So I have to add the code to initialize the search

    $('#min, #max').keyup( function() {
        table.columns( [3] )
        table.search(this.value);
        table.draw();
        } );

I add this after

        initComplete: function() {
        var api = this.api();

but it doesn't work. It search on the column 1,2 and not a range

colin

Hi @pettedemon ,

I'm guess you based the code on this example.

The problem with your code is in the keyup handler. You don't need lines 2 or 3 as in the example above, it only needs the api draw(). As the table is drawn, because of the search extension, it'll now check on the values of the two input cells.

Hope that helps,

Cheers,

Colin

pettedemon

Hi @colin ,
I found a solution

here the min & max range

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var min = parseInt( $('#min').val(), 10 );
        var max = parseInt( $('#max').val(), 10 );
        var age = parseInt( data[3] ) || 0; // use data for the age column

        if ( ( isNaN( min ) && isNaN( max ) ) ||
             ( isNaN( min ) && age <= max ) ||
             ( min <= age   && isNaN( max ) ) ||
             ( min <= age   && age <= max ) )
        {
            return true;
        }
        return false;
    }
);

here the server-side

            var table =   $('#canzoni_player').DataTable( {
        "processing": true,
        "serverSide": true,
                "autoWidth": false,


        "ajax": "scripts/tabella_playlist.php",
            "lengthMenu": [[18, 50,100, 250, 500, -1], [18, 50,100, 250, 500, "Tutti"]],
        "language": {
            "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Italian.json"
        },

here the column search

initComplete: function() {
        var api = this.api();


        $('#min, #max').keyup( function() {
      table
        .columns(3)
        .search( this.value )
        .draw();
        } );


      // Apply the search
      api.columns([0, 1, 3, 4]).every(function() {
        var that = this;



        $('input', this.footer()).on('keyup change', function() {
          if (that.search() !== this.value) {
            that
              .search(this.value)
              .draw();
          }
        });
      });
    }

So i have an other problem.
the min & max work , but not perfect, if I set min=110 & max=120 the system show me the result from 112...

Thanks

pettedemon

Hi,
so the problem is it search only the second value, 120, and in my case it found 112 and 120 because in these numbers there the "12" number...
So my custom range doesn't work....

pettedemon

Hi,
if I use only

        initComplete: function() {
        var api = this.api();


        $('#min, #max').keyup( function() {
      table

        .draw();
        } );


      // Apply the search
      api.columns([0, 1, 3, 4]).every(function() {
        var that = this;



        $('input', this.footer()).on('keyup change', function() {
          if (that.search() !== this.value) {
            that
              .search(this.value)
              .draw();
          }
        });
      });
    }

it doesn't work

colin

Looking at the above block of coder, you've got that search() on line 21 still. If you look at my first comment above, I was saying you don't need to have that.

Cheers,

Colin

pettedemon

Hi colin,
I try comment the .search but it doesn't work.
At now with this code

`
$.fn.dataTable.ext.search.push(
function( settings, data, dataIndex ) {
var min = parseInt( $('#min').val(), 10 );
var max = parseInt( $('#max').val(), 10 );
var BPM = parseInt( data[3] ) || 0; // use data for the age column

        if ( ( isNaN( min ) && isNaN( max ) ) ||
             ( isNaN( min ) && BPM <= max ) ||
             ( min <= BPM   && isNaN( max ) ) ||
             ( min <= BPM   && BPM <= max ) )
        {
            return true;
        }
        return false;
    }
);


        $(document).ready(function() {


            $('#canzoni_player tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Cerca '+title+'" />' );
    } );







            var table =   $('#canzoni_player').DataTable( {
        "processing": true,
        "serverSide": true,
        "autoWidth": false,
    //  "scrollY":        '70vh',
     //   "scrollCollapse": false,


        "ajax": "scripts/tabella_playlist.php",
            "lengthMenu": [[18, 50,100, 250, 500, -1], [18, 50,100, 250, 500, "Tutti"]],
        "language": {
            "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Italian.json"
        },




        initComplete: function() {
        var api = this.api();


    $('#min, #max').keyup( function() {
      table
        .columns(3)
        .search(this.value)
        .draw();
        } );



      // Apply the search
      api.columns([0, 1, 3, 4]).every(function() {
        var that = this;



        $('input', this.footer()).on('keyup change', function() {
          if (that.search() !== this.value) {
            that
            .search(this.value)
              .draw();
          }
        });
      });
      // end the search
    }







} );

`

it doesn't work.
So at the "keyup" on #min it search the min value, but when I type on #max it search the max value

Thanks

pettedemon

Hi,
maybe the problem is the server side function?
I try without server-side and it works, but with server side there is this problem.
thanks

kthorngren

The range search is a client side solution. For server side processing you need to have your server script perform the search and return the appropriate results.

Kevin

pettedemon

Hi,
so I cannot use the server side search, I have to do a custom ajax process with custom query?
Is there any example?
At now I use the array with the ssp.class.php
thanks

pettedemon

Hi,
I search and I found a solution
I do a custom function

// inizializzo la funzione per il recupero della tabella a zero come se non fosse impostato nessun filtro
        fetch_data('no');

        // funzione per ordinare la tabella con i bpm
        $('#ordina_bpm').click(function(){

            // recupero il valore degli input
            var min = $('#min').val();
            var max = $('#max').val();



            if(min != '' && max !='')
              {
                //distruggo la tabella corrente
                $('#canzoni_player').DataTable().destroy();
                //resetto i valori di ogni colonna
                $('#canzoni_player tfoot input').val('').change()
                // setto a yes la funzione e passo le variabili
                fetch_data('yes', min, max);
              }
              else
              {
               alert("Inserisci il valore minimo e massimo");
              }
        });

        function fetch_data(range, min='', max='')
            {

                var table =   $('#canzoni_player').DataTable( {
                    "processing": true,
                    "serverSide": true,
                    ajax: {
                            url:  "scripts/tabella_playlist.php",
                            type: 'GET',
                            data: {range:range, min:min, max:max },
                        },
                        .....

then in my .php file I write a simple condition

if ($_GET["range"] == "yes") {

    
    $whereAll = "bpm BETWEEN '".$_GET['min']."' AND '".$_GET['max']."'  ";

} else {

    $whereAll = "";


}

Maybe is useful for someone.