Updating grids' data when combo box value changes...

jQuery_Newbie

I am new at JavaScript programming. Today, someone told me about DataTables. I decided to try to use it in my test web page.

My page has a combo box. Underneath the combo box, there are two tabs. Each tab holds a grid which is populated with data from a MySQL query. My goal is to filter the grids' data based on the current combo box value.

Here is my code:

<script>
    $(document).ready(function () {
        // Initialize tabs
        $("#tabs").tabs();

        // Fetch data when the page loads and when the combo box value changes
        fetchData($('#comboBox').val());

        // When the combo box value changes, reload the data
        $('#comboBox').change(function () {
            fetchData($(this).val());
        });

        function fetchData(selectedValue) {
            // Show loading indicator (optional)
            $('#grid1 tbody').html('<tr><td colspan="5">Loading...</td></tr>');
            $('#grid2 tbody').html('<tr><td colspan="5">Loading...</td></tr>');

            // Use AJAX to fetch the data for both grids based on the selected value
            $.ajax({
                url: 'getData.php',
                method: 'GET',
                data: { selected_id: selectedValue },
                dataType: 'json',
                success: function (response) {
                    console.log(response); // Log the response to check the data

                    // Fill grid 1
                    let grid1Html = '';
                    if (response.grid1 && response.grid1.length > 0) {
                        response.grid1.forEach(function (item) {
                            const itemNameLink = `<a href="${item['Link']}" target="_blank">${item.RoseName}</a>`;
                            grid1Html += `<tr><td>${itemNameLink}</td><td>${item.RoseType}</td><td>${item.RoseColor}</td><td>${item.RoseYear}</td><td>${item.RoseHybridizer}</td></tr>`;
                        });
                    } else {
                        grid1Html = '<tr><td colspan="5">No data available</td></tr>';
                    }
                    $('#grid1 tbody').html(grid1Html);  // Update the grid1 tbody with the HTML

                    // Fill grid 2
                    let grid2Html = '';
                    if (response.grid2 && response.grid2.length > 0) {
                        response.grid2.forEach(function (item) {
                            const itemNameLink = `<a href="${item['Link']}" target="_blank">${item.RoseName}</a>`;
                            grid2Html += `<tr><td>${itemNameLink}</td><td>${item.RoseType}</td><td>${item.RoseColor}</td><td>${item.RoseYear}</td><td>${item.RoseHybridizer}</td></tr>`;
                        });
                    } else {
                        grid2Html = '<tr><td colspan="5">No data available</td></tr>';
                    }
                    $('#grid2 tbody').html(grid2Html);  // Update the grid2 tbody with the HTML

                    // Destroy existing DataTable instance for grid1 and grid2 before reinitializing
                    if ($.fn.dataTable.isDataTable('#grid1')) {
                        $('#grid1').DataTable().clear().destroy();
                    }

                    if ($.fn.dataTable.isDataTable('#grid2')) {
                        $('#grid2').DataTable().clear().destroy();
                    }

                    // Ensure table content is rendered before initializing DataTable
                    setTimeout(function () {
                        // Initialize DataTable for grid1 if it has rows
                        if ($('#grid1 tbody tr').length > 0) {
                            $('#grid1').DataTable({
                                paging: true,               // Enable pagination
                                searching: true,            // Enable searching
                                order: [],                  // Disable initial ordering
                                pageLength: 10,             // Set the number of rows per page (adjust as needed)
                                lengthChange: false,        // Disable the option to change the number of rows per page
                                info: true,                 // Optionally, disable the information text (e.g., "Showing 1 to 10 of 50 entries")
                                dom: 'lrtip'                // Remove the length drop-down (l), search box (f), and other elements as needed
                            });
                        }

                        // Initialize DataTable for grid2 if it has rows
                        if ($('#grid2 tbody tr').length > 0) {
                            $('#grid2').DataTable({
                                paging: true,               // Enable pagination
                                searching: true,            // Enable searching
                                order: [],                  // Disable initial ordering
                                pageLength: 10,             // Set the number of rows per page (adjust as needed)
                                lengthChange: false,        // Disable the option to change the number of rows per page
                                info: true,                 // Optionally, disable the information text (e.g., "Showing 1 to 10 of 50 entries")
                                dom: 'lrtip'                // Remove the length drop-down (l), search box (f), and other elements as needed
                            });
                        }
                    }, 100);  // Add a slight delay to ensure the content is fully rendered before initializing DataTable
                },
                error: function () {
                    $('#grid1 tbody').html('<tr><td colspan="5">Error loading data</td></tr>');
                    $('#grid2 tbody').html('<tr><td colspan="5">Error loading data</td></tr>');
                }
            });
        }

        // Custom column filters
        $('#filterRoseName1').on('keyup', function () {
            $('#grid1').DataTable().columns(0).search(this.value).draw();
        });
        $('#filterRoseType1').on('keyup', function () {
            $('#grid1').DataTable().columns(1).search(this.value).draw();
        });
        $('#filterRoseColor1').on('keyup', function () {
            $('#grid1').DataTable().columns(2).search(this.value).draw();
        });

        $('#filterRoseName2').on('keyup', function () {
            $('#grid2').DataTable().columns(0).search(this.value).draw();
        });
        $('#filterRoseType2').on('keyup', function () {
            $('#grid2').DataTable().columns(1).search(this.value).draw();
        });
        $('#filterRoseColor2').on('keyup', function () {
            $('#grid2').DataTable().columns(2).search(this.value).draw();
        });
    });
</script>

My issue is that destroying existing DataTable instance for grid1 and grid2 before reinitializing wipes out the data. My grids have nothing to display. How do I fix this issue?

kthorngren

I'm guessing the reason the tables are cleared is due to using clear() here:

                    if ($.fn.dataTable.isDataTable('#grid1')) {
                        $('#grid1').DataTable().clear().destroy();
                    }
 
                    if ($.fn.dataTable.isDataTable('#grid2')) {
                        $('#grid2').DataTable().clear().destroy();
                    }

Try removing .clear() to keep the table data.

Probably a better option is to move this code before you populate the table. Maybe something like this:

                    console.log(response); // Log the response to check the data
 
                    if ($.fn.dataTable.isDataTable('#grid1')) {
                        $('#grid1').DataTable().destroy();
                    }
 
                    if ($.fn.dataTable.isDataTable('#grid2')) {
                        $('#grid2').DataTable().destroy();
                    }

                    // Fill grid 1
                    let grid1Html = '';

This way Datatbles is destroying a the tables before any changes are made to them.

Kevin

jQuery_Newbie

Thanks Kevin. Combined, both of your suggestions solved my issue.