Custom Range Search - Server Side

Custom Range Search - Server Side

pettedemonpettedemon Posts: 38Questions: 6Answers: 0

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

Replies

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    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

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    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....

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    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

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0
    edited July 2018

    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

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    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

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736

    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

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    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

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0
    edited July 2018

    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.

This discussion has been closed.