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

th3t1ck

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.

allan

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

th3t1ck

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

th3t1ck

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.

th3t1ck

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

th3t1ck

Well I can't see anything wrong. Help!

allan

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

th3t1ck

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.

th3t1ck

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...".

colin

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

th3t1ck

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".

th3t1ck

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