How to requery database from standalone select input value and refresh datatable

How to requery database from standalone select input value and refresh datatable

th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

I have a standalone select input on my site that the user can pick a year from and only the records with that year should be displayed in the datatable. Using JS I can get the year selected by the user into a JS variable. I am not sure how I can send that variable to my PHP script and have the datatable reflect the selection. I don't want to load all the records and filter them by year. I would like to only display the records that are found in the database.
Should I do an ajax call? Or is there some simple way to get that variable to my PHP script?

My client side select that is outside the datatable init...

// create year select input
print"
                <select class='select-year' id='yearSelect' name='year' onChange='getYearSelected()'>
                    <option value='' disabled selected>SELECT YEAR</option>";
                    $uYears=getUniqueYears();
                    foreach( $uYears as $val ) {
                    print"<option value='".$val."'>".$val."</option>";
                    }

print"
                </select>

            </div>
        </td>
    </tr>
</table>

<table id='cases' class='table table-striped table-bordered dt-responsive no-wrap'>
...etc.

The getYearSelected function...

<script>
    function getYearSelected() {
        var year = document.getElementById('yearSelect').value;
        alert('You chose ' + year);
    } 
 </script>

My PHP Script...

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;
 
Editor::inst( $db, 'cases', 'case_number' )
    ->field( ... bunch of fields...

)

        ->leftJoin( ... 9 joins ... )

->where( function ( $q ) use ( $year ) {
            $q->where( 'start_date', $year.'-%', 'LIKE');
        } )

    ->debug( true )
    ->process( $_POST )
    ->json();

I've been working on this for a couple days now without any luck.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

    Use ajax.data to send data to the server. Probably best to use it as a function in this case and you get just do:

    data: function (d) {
      d.yearSelect = $('yearSelect').val();
    }
    

    Then on the server-side simply check to see if yearSelect is an empty string or not. It is, return echo json_encode(['data' => []]); i.e. an empty data array. If it isn't empty, then carry on with your Editor class.

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited December 2020

    That helps Allen. I'll give it a shot.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    So the value 'yearSelect' is being passed on the initial loading of the page but when I select another year after it loads nothing happens. No data is passed to PHP script and the datatable does not refresh with a new query for yearSelect.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I'll look at my events that are/should be happening.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Well I can't see anything wrong. Help!

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    d.yearSelect = $('yearSelect').val();

    Typo in there - sorry:

    d.yearSelect = $('#yearSelect').val();
    

    No data is passed to PHP script and the datatable does not refresh with a new query for yearSelect.

    Do you have a change event listener on the yearSelect element that will trigger ajax.reload()?

    Allan

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Ok I fixed the typo. I added the following ajax.reload() but it puts the page into an endless loop.

    var table=$('#cases').DataTable( { etc...
    
    ajax: {
                url: 'cases_test-con.php',
                type: 'POST',
                data: function (d) {
                        d.yearSelect = $('#yearSelect').val();
                },
                success: function() {
    **//                table.ajax.reload(null, false);**
                }
            },
    

    My select input...

    // create year select input
    print"
                    <select class='select-year' id='yearSelect' name='year'>
                        ";
                        $uYears = getUniqueYears();
                        foreach( $uYears as $val ) {
                        print"<option value='".$val."'>".$val."</option>";
                        }
    
    print"
                    </select>
    etc...
    

    I did have an onChange event in the <select> tag but it simply called a function to get the yearSelect which seems to be happening above anyway so I removed it. I don't know how to get this working. It seems to be very close though.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited December 2020

    So with the above code I can see the current year being passed and the query looks correct but the table does not render. It just states "Loading...".

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

    One problem there is the success within your ajax - as the manual says it must not be overridden as it is used internally in DataTables.

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Thank you Colin. I've seen you mention that in other posts as well. I was just taking a crack at it.

    So I added an onChange to my select input ...

    <select class='select-year' id='yearSelect' name='year' onChange='foo()'>
    

    And created foo() ...

    function foo() {
        var year = $('#yearSelect').val();
      $.post('cases_test-con.php', { yearSelect: year }, function (html) {
        $('#cases')
          .DataTable()
          .ajax.reload()
      });
    }
    

    I can now see the value being passed is correct in the browsers tools. I also see the query is correct also. Just stuck trying to get the table to refresh and display the table. Datatable still says "Loading".

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Got it working now. I removed success: and datasrc: from the ajax declaration and it started working. Thank you for the suggestions and comments.

This discussion has been closed.