Update Table after server side filter

seularts

I made a nice search filter that sorts my table out. The issue is that it calls a function when I select an option and if I try to update, delete or add an entry it does not update it in the table because it gets stuck in the function. Here is my current code:

        var dataTable = $('#user_data').DataTable({
            "processing":true,
            "serverSide":true,
            "order":[],
            "ajax":{
                url:"fetch.php",
                type:"POST"
            },
            "columnDefs":[
                {
                    "targets":[2, 3],
                    "orderable":false,
                },
            ],

        });

    function load_data(options){
        var dataTable = $('#user_data').DataTable({
            "processing":true,
            "serverSide":true,
            "order":[],
            "ajax":{
                url:"fetch.php",
                type:"POST",
                data: { options:options }
            },
            "columnDefs":[
                {
                    "targets":[2, 3],
                    "orderable":false,
                },
            ],

        });
    }

    $('#options').on('change', function(){
        var options = $(this).val();
        /* alert(options); */
        $('#user_data').DataTable().destroy();
        if(options != '') {
            load_data(options);
        }else{
            load_data();
        }
    });

And this is what happens in the php side:

$query = '';
$output = array();
$query .= "SELECT * FROM users ";
if(isset($_POST["options"]))
{
 $query .= "WHERE lastName = '".$_POST["options"]."' AND ";
}
else{ $query .= "WHERE "; }
if(isset($_POST["search"]["value"]))
{
    $query .= '(firstName LIKE "%'.$_POST["search"]["value"].'%" ';
    $query .= 'OR lastName LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(isset($_POST["order"]))
{
    $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
    $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();

When I first load the page everything updates correctly on edit, add or remove. Only after I select an option from the drop-down select list and I destroy the initial table I get stuck in limbo! How can I correct this issue!?

seularts

Better yet, how do I point dataTable.ajax.reload(); to the new function in order to work?

colin

Hi @seularts ,

Probably the better way to go would be to have ajax.data as a function, see the second example on that page. There you could decide to set option in a test.

Would that work?

Cheers,

Colin

seularts

Indeed, that seems to be a better solution, but how do I change the value of options in this function:

ar dataTable = $('#user_data').DataTable({
            "processing":true,
            "serverSide":true,
            "order":[],
            "ajax":{
                url:"fetch.php",
                type:"POST",
                data: function ( d ) {
                    d.options = $('#options').val();
                }
            },
            "columnDefs":[
                {
                    "targets":[2, 3],
                    "orderable":false,
                },
            ],

        });

so the information can be passed as options:options? I'm stuck on this step. I tied to implement a function parameter like this:

       function load_data(options) {
        options;
    }

    $(document).on('change', '#options', function(){
        var options = $(this).val();
        if(options != '') {
            load_data(options);
        }else{
            load_data();
        }
    });

and replaced d.options = $('#options').val(); with d.options = load_data(); which does give me the required selection, but it does not pass on to my fetch.php post options.

colin

You could just use standard jQuery there, something like $('#options').val()

seularts

Sorry, yeah.. I overcomplicated things. I ended up with this little code:

       var opt = '';

    $('#options').on('change', function(){
        opt = $(this).val();
        dataTable.ajax.reload();
    });

    var dataTable = $('#user_data').DataTable({
            "processing":true,
            "serverSide":true,
            "order":[],
            "ajax":{
                url:"fetch.php",
                type:"POST",
                data: function ( d ) {
                    d.options = opt;
                    /* alert(d.options); */
                }
            },
            "columnDefs":[
                {
                    "targets":[2, 3],
                    "orderable":false,
                },
            ],

        });

and I altered my php to be like this: if(isset($_POST["options"]) && $_POST["options"] != '') in case anyone runs in a similar situation!

seularts

Thank you for your help awesome colin ^_^

colin

Perfect, looks a good fit. Glad all working.