Proper Loading and Draw of DataTable

Larry27

https://www.baldwinproject.ca/app/sched40-ws-sql.php
https://debug.datatables.net/umapiw
Seems to be an Ajax Error
It appears my code to load the table is partially working, as you can see when you load the sample page above. However, it doesn't seem to load all components. The search pane doesn't populate, and the pagination doesn't work, nor does the built-in searchbox.

Also, once I initially load data, I am trying to reloading different data, based on the users selection in the select box. That does generate my data, but doesn't seem to completely load all related features, just as the initial load. At the moment I am only concerned about the function select box, but later I will also try to integrate the year select box.

Any help you can offer, would be greatly appreciated.

Thanks,

Larry

allan

Hi Larry,

You have server-side processing enabled. That means that the server side script is responsible for all search and sort, as well as getting the current page of data. If you have this mode enabled, you need to implement server-side processing per this documentation. The logic for SearchPanes also needs to be implemented.

Our Editor PHP libraries can be used to do that, as they have server-side processing support built in. You don't say in your post, but it is possible you are using those libraries already? Normally the Editor libraries are setup to expect POST data (in the ->process() method call). You are currently sending a GET, so either change the request to be a POST (see this example, or change the ->process() call to use $_GET.

One other thing - you've loaded both jquery.dataTables.min.css and dataTables.bootstrap5.css. That's why the paging control looks odd. You should load only one of those two files.

Allan

Larry27

Hi Allan,

I've fixed the issue with the paging controls, and I have changed back to client-side, so my searchpanes are working properly also.

My one remaining issue is that I cannot figure out where to put reload() and draw() in my function which triggers when a user changes the value in a select box.

Any help you can offer would be greatly appreciated. This is my code;

            <script>

            $(document).ready(function(){

                $('#selFunction').on("change", function(e) {

                    var valLine = $(this).val();

                    $("#FunctionNameYear").text(valLine);
                    var txtLine = $("#selFunction option:selected").text();

                    $("#FunctionNameYear").text(txtLine);

                    var Year = 2018;
                    var LineNum = valLine;

                    var mytable = $('#sched40-ws').DataTable({
                    "destroy": true,
                    "serverSide": true,
                    "processing": true,
                    "ajax": {

                        "url":("sched40-ws-sql-GetData-TestArray.php?q="+LineNum,true)

                    }

                    });

                });

            });

            </script>

allan

What I would suggest doing is changing your code a little so you only initialise the DataTable once. Then use ajax.data to send the request to the server with the selected value. All that is needed then is to call ajax.reload().

Here is a little update:

$(document).ready(function () {
  var Year = 2018;
  var LineNum = '';

  $('#selFunction').on('change', function (e) {
    var valLine = $(this).val();

    $('#FunctionNameYear').text(valLine);
    var txtLine = $('#selFunction option:selected').text();

    $('#FunctionNameYear').text(txtLine);

    LineNum = valLine;
    mytable.ajax.reload();
  });

  var mytable = $('#sched40-ws').DataTable({
    processing: true,
    ajax: {
      url: 'sched40-ws-sql-GetData-TestArray.php',
      data: function (d) {
        d.q = LineNum;
      }
    }
  });
});

I'm not entirely sure what the change event handler is doing, but I've matched that logic and just changed the DataTables part.

Allan

Larry27

Thanks for your help so far Allan, I feel like I am making some progress.

I still have 2 remaining issues;

1. When I initially load my page it doesn't load any recordset, which is what I expect. When I select a line from the select box it does appear to load the correct recordset, however I doesn't populate the table.
2. I call a .js page at the bottom of my page, which formats the table fields and searchpanes etc. But I see a cannot 'reinitialize' the datatable. I assume I am calling that page improperly, but for the life of me I cannot see how to correct this error.

Your help is so greatly appreciated.

Larry

allan

Looks like you have initialisation for the sched40-ws table both in the main PHP file and in sched40-ws-sql.js. That's why you are getting the error:

DataTables warning: table id=sched40-ws - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

on page load.

I would suggest you move it all into sched40-ws-sql.js - combine the logic for the two (specifically the Ajax option).

Also, in sched40-ws-sql.js you still have server-side processing enabled. I think you want to loose that.

Allan

Larry27

I have now moved, at least I think, all my table configuration related code to the external .js file (https://www.baldwinproject.ca/app/on/js/sched40-ws-sql.js). By default the .js file assigns default values for line number (9910) and year (2018). This all seems to work very well for me.

I would like to be able to call my .js file from the select box on my main page (https://www.baldwinproject.ca/app/sched40-ws-sql.php) and pass a different line number and year to the external .js file, so the user can change those values and return a new recordset into the datatable.

Your continued help is really appreciated.

Larry27

I'm also wondering if there is an easy way to make the searchpane modal? I worry that users will not be able to visually understand they must interact with the searchpane and dismiss it in order for their selections to be applied to the datatable.

allan

I would like to be able to call my .js file from the select box on my main page

Look at it the other way around - the JS file should and an event listener to the select element. That's what you have with $('#selFunction').on('change', function (e) { - that code just needs to be in the JS file.

I'm also wondering if there is an easy way to make the searchpane modal?

See this example.

Allan

Larry27

I honestly didn't know you could have an event listener on another page like that. That knowledge opens a lot of doors now, thanks for that

I've now implemented that, and sure enough, the listener does work. I may not have set it up properly though because I keep getting a cannot reinitialize error when I select a different line number. When the form opens, I am currently loading the total line (9910) by default. After that, I want users to be able to click the select box to display a different line to view the records associated with that line.

I've done a lot of reading, but have yet to find the solution to that particular issue. If you have time to look at https://www.baldwinproject.ca/app/on/js/sched40-ws-sql.js and point me in the right direction, I would be grateful.

Thanks for all your help to date

Larry

Larry27

Hi Allan,

I've been able to make some real progress on my datatable. I still have other things to fine-tune, but for now I won't need any further help on this thread. You can close it on your end.

I also want to say, thank you, I really appreciate the help you provide to all of us who are struggling to present data in a manner helpful to our users. You have an amazing product

allan

Hi Larry,

Awesome great to hear that you have been able to make progress. Looks like you are picking up Javascript quickly

Allan