Mergin two functions in datatables

Lerster

I have two functions inside of my datatables script. They are both using different ajax calls.

My table (name: product_data) and scripts are working as expected but I get this error message with the first page load:

"DataTables warning: table id=product_data - Cannot reinitialise DataTable"

I think this error comes because I have a mistake in mergin these two functions together in one script. Could you help me how to get these functions together?

$(document).ready(function() {

  // Start Function 1

  load_data();

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

  // Script for Function 1 //

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


  // Start Function 2

  fetch_data('no');

  function fetch_data(is_date_search, start_date = '', end_date = '') {
    var dataTable = $('#product_data').DataTable({
      "processing": true,
      "serverSide": true,
      "order": [],
      "ajax": {
        url: "fetch.php",
        type: "POST",
        data: {
          is_date_search: is_date_search,
          start_date: start_date,
          end_date: end_date
        }
      }
    });
  }

  // Script for Function 2 //

  $('#search').click(function() {
    var start_date = $('#start_date').val();
    var end_date = $('#end_date').val();
    if (start_date != '' && end_date != '') {
      $('#product_data').DataTable().destroy();
      fetch_data('yes', start_date, end_date);
    } else {
      alert("Both Date is Required");
    }

  });

  // Search Field

  var datatable = $('#product_data').DataTable();
  $('#searchfield').on('keyup', function() {
    datatable.search(this.value).draw();
  });

});

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

colin

You're calling load_data(), followed by fetch_data() and both are initialising $('#product_data'). It doesn't look like you're initialising any other tables.

Colin

Lerster

Thanks for your help. I´ve improved the code. The problem now is that I get an empty table output:

No matching records found
Showing 0 to 0 of 0 entries (filtered from 20 total entries)

Could you please have a look over it again:

These are the input fields:

 <input type="number" name="start_date" id="start_date" class="form-control" />
 <input type="number" name="end_date" id="end_date" class="form-control" />
 <input type="number" name="search" id="search" value="Search" class="btn btn- 
 info" />

the improved table script:

$(document).ready(function() {

  // Start Function 1
  function load_data() {

  //first recive all inputs here 
  let is_category = $("#category").val();
  let is_date_search = $("#is_date_search").val();
  let start_date = $('#start_date').val();
  let end_date = $('#end_date').val();

 //and initialise datatables once only
 var dataTable = $('#product_data').DataTable({
     "processing": true,
     "serverSide": true,
     "sDom": "rtipl",
     "order": [],
     "ajax": {
                  url: "fetch.php",
                  type: "POST",
                  data: {
                             is_date_search: is_date_search,
                             is_category: is_category,
                             start_date: start_date,
                             end_date: end_date
                            }
     },
     "columnDefs": [{
     "targets": [2],
     "orderable": false,
    }, ],
  });
}

  //calling the function on document load
  load_data();

  $(document).on('change', '#category', function() {
  $('#product_data').DataTable().destroy();
  load_data();
  });

   $('#search').click(function() {

  if (start_date != '' && end_date != '') {
  $('#product_data').DataTable().destroy();
  load_data();
  } else {
  alert("Both Date is Required");
  }
  });

    // Search Field
   var datatable = $('#product_data').DataTable();
   $('#searchfield').on('keyup', function() {
   datatable.search(this.value).draw();
   });

})

and this is the part of the **fetch.php **where I look if "is_date_search" or "is_category" exist:

if($_POST["is_date_search"] == "yes")
{
 $query .= 'order_id BETWEEN "'.$_POST["start_date"].'" AND 
 "'.$_POST["end_date"].'" AND ';
 }

 if(isset($_POST["is_category"]))
{
$query .= "order_item = '".$_POST["is_category"]."' AND ";
}

colin

Could you link to your page, please, it would help us debug it,

Colin

Lerster

I´ve set up a test page with the datatable and I´ve sent you the website link with a private message. Thanks for your help.

colin

Your code on that site looks exactly the same as before:

  // Start Function 1
 
  load_data();
 
  function load_data(is_category) {
    var dataTable = $('#product_data').DataTable({
       ...
    });
  }
 
    ....
 
  fetch_data('no');
 
  function fetch_data(is_date_search, start_date = '', end_date = '') {
    var dataTable = $('#product_data').DataTable({
       .....
    });
  }

Bpth load_data() and fetch_data() are initialising the same table, product_data - and are called one after the other - so my first comment still stands,

Colin

Lerster

I´ve revised the script and the fetch.php. Could you have a look on the site again, please.

Now I get the following message:

No matching records found

$(document).ready(function() {

  function load_data() {

 //first recive all inputs here 
 let is_category = $("#category").val();
 let start_date = $('#start_date').val();
 let end_date = $('#end_date').val();

var dataTable = $('#product_data').DataTable({
   "processing": true,
   "serverSide": true,
   "sDom": "rtipl",
   "order": [],
   "ajax": {
     url: "fetch.php",
     type: "POST",
     data: {
       is_category: is_category,
       start_date: start_date,
       end_date: end_date
     }
   },
   "columnDefs": [{
    "targets": [2],
    "orderable": false,
    }, ],
  });
}

  //calling the function on document load
  load_data();


 $(document).on('change', '#category', function() {
  $('#product_data').DataTable().destroy();
  load_data();
 });

  $('#search').click(function() {

  if (start_date != '' && end_date != '') {
  $('#product_data').DataTable().destroy();
  load_data();
  } else {
  alert("Both Date is Required");
  }
});

// Search Field
var datatable = $('#product_data').DataTable();
$('#searchfield').on('keyup', function() {
datatable.search(this.value).draw();
 });

})

And the fetch.php part:

if(isset($_POST['start_date'], $_POST['end_date'])) {
$query .= 'order_id BETWEEN "'.$_POST["start_date"].'" AND 
"'.$_POST["end_date"].'" AND ';
 }

 if(isset($_POST["is_category"])) {
$query .= "order_item = '".$_POST["is_category"]."' AND ";
}

Lerster

Hi colin, did you already found time to look over the code? Thanks.

colin

I just took a look and there are several console errors stopping the page from loading. Also, clicking on other elements give console errors too. Those errors don't look like DataTables issues, so it would be worth asking on a more generic site such as StackOverflow.

Colin

Lerster

Hi Colin,

the general console errors´ are fixed now. You don´t get a console error now if you are entering the page.

But if you are yousing the datatable, for example the select box, the min/ max search or the global search you will see these console errors:

Uncaught TypeError: Cannot set property '_DT_CellIndex' of undefined
Uncaught TypeError: Cannot read property 'parentNode' of null

Could you help me where an error in my datatable script is?

Thank you.

allan

Have you since resolved this? If I enter 2 into the max input and click search, I'm not getting any errors.

Thanks,
Allan

p.s. Colin PMed me the link (we both work for SpryMedia, the company behind DataTables, in case that wasn't clear ).

Lerster

Hi allan, thanks for your help. No I didn´t managed to resolve the errors completely.

1. As you can see with the page load datatables shows me an empty table with the message:

No matching records found

I don´t know where the error is that I don´t get any data results loaded. The server sided script (fetch.php) is working so far.

When I enter 2 in the min input and 10 in the max input and click on "Search" i get this error:

When I click on an entry of the select box I get this error in the console:

Any help appreciated.

Lerster

Hi Allan,

did you found a bit of time to look into that? I already sent you the link to the problem.

Thanks a lot.

kthorngren

Uncaught TypeError: Cannot set property '_DT_CellIndex' of undefined

There are lots of thread with this error, like this one. Usually its an issue with the HTML table. Have you verified your table HTML meets the documented requirements?

Kevin

Lerster

Yes, the table form fits the requirements.
The problem is definately the combination between the global and range search. I think I did some mistake there. I will send you the link to my testpage (with a private message). There you can see the error.

Thanks for your help.

kthorngren

Instead of using destroy() and reinitializing the Datatable my suggestions is to use ajax.reload(), like this:

  $('#search').click(function() {
  
    if (start_date != '' && end_date != '') {
      //$('#product_data').DataTable().destroy();
      //load_data();
      $('#product_data').DataTable().ajax.reload()
    } else {
      alert("Both Date is Required");
    }
  });

You will need to change your ajax.data to be a function so it can retrieve the input values for each ajax.reload(). See the examples in the docs.

        data: {
          is_category: is_category,
          start_date: start_date,
          end_date: end_date
        }

Kevin

Lerster

Thanks for the tips. I´ve made a huge step forward.

As you´ve described I´m using ajax.reload(). Also I´ve made a function for the ajax.data.

Now I see the data results in the table and the global search works.

Sadly I get the following error when I´m trying to use the "Min" and "Max" Range Search and the "Select list":

DataTables warning: table id=product_data - Invalid JSON response. For more 
information about this error, please see http://datatables.net/tn/1

kthorngren

Start with the troubleshooting link provided:
http://datatables.net/tn/1

Let us know what you find.

Kevin

Lerster

So I´ve looked in the troubleshooting page and made the steps:

• when using the min/max range search or the select list no json is generated
• when using the global search the json is generated correctly

Probably there is a mistake in the data function which I´ve set in combination with ajax reload.

  "data": function () {
        is_category = $("#category").val();
          start_date = $('#start_date').val();
          end_date = $('#end_date').val();
}

min/max range search

$('#search').click(function() {

  if (start_date != '' && end_date != '') {
      $('#product_data').DataTable().ajax.reload()
      } else {
            alert("Both Date is Required");
           }
   });

select list

 $(document).on('change', '#category', function() {
    $('#product_data').DataTable().ajax.reload()
 });

The complete code is updated on the website.

kthorngren

Take a closer look at the first function example in the ajax.data docs. You will want something more like this:

  "data": function ( d ) {. // make sure to add the `d` parameter
        //. Make sure to use `d.` to add the values to the parameters object sent.
        d.is_category = $("#category").val();
          d.start_date = $('#start_date').val();
          d.end_date = $('#end_date').val();
}

Kevin

Lerster

First of all thanks for all your effort.

You´re right. I forgot to add the "d" paramater. I´ve updated the script and the fetch.php with this parameter.

The sad part is that there is still no json generated with the min/max range search or the select list.
The global search works fine.

kthorngren

The sad part is that there is still no json generated with the min/max range search or the select list.

Your PHP script will need to be debugged to track down the issue.

Kevin