Sending multiple values via ajax.data() to $editor->where() end looping through where()

Capamania

I want send multiple values via ajax.data() // by e.g. selecting multiple input values // to $editor->where() and then loop through the where condition. How can this be done?

table = $('#my-table').DataTable( {
            dom: "Blfrtip",
            ajax: {
                    url: "/data.php",
                    type: "POST",
                    data: function (d) {
                          d.select = $('.select-status').val(); 
                             } 
        },
            serverSide: true,
            processing: true,
            ...
});

in data.php

->where( function ( $q ) {
                $q->where( 'country', 'DE' );
                $q->or_where( function ( $r ) {
                        $r->where( 'country', 'US' );
                        $r->or_where( function ( $s ) {
                                $s->where( 'country', 'GB' );
                            });
                    });
            })

The above where() filters 'DE', 'US' and 'GB' from the database and renders it.

Capamania

Anybody an idea?

allan

To check I understand correctly, the .select-status is a select with multiple set? If so, it will submit an array, so you would just loop over that array:

->where( function ($q) {
  if ( is_array( $_POST['select'] ) ) {
    for ( $i=0, $ien=count($_POST['select']) ; $i<$ien ; $i++ ) {
      $q->or_where( 'country', $POST['select'][$i] );
    }
  }
} )

Allan

Capamania

Thanks Allan. The suggested loop is returning all the values though.

Yes, the .select-status is a multiple select set:

<select class="select-status form-control" name="character" multiple="multiple">
            <option value="">All Countries</option>
            <option value="US" selected="selected">United States</option>
            <option value="GB">Great Britain</option>
            <option value="DK">Denmark</option>
            <option value="SE">Sweden</option>
            <option value="NO">Norway</option>
            <option value="DE">Germany</option>
            <option value="LU">Luxembourg</option>
</select>

... and after the .DataTable() instance I .ajax.reload() the table:

    $('.select-status').on( 'change', function() {
        table.ajax.reload();
    });

A alert test:

var selectAlert = $('.select-status').val();
window.alert(selectAlert);

... shows that the data is thrown back as:
US,GB,DK,SE,NO,DE,LU
... if I select the values

allan

Are you able to give me a link to the page please? I'm not sure what you mean by returning all of the values I'm afraid. Do you mean that the WHERE condition is not being applied? Is the loop executing?

Allan

Capamania

Hi Allan. Now it's working ... almost :-) An underline '_' was missing in the example above. Now the loop is working if it's the only condition:

->where( function ($q) {
    if ( is_array( $_POST["select"] ) ) {
        for ( $i=0, $ien=count($_POST["select"]) ; $i<$ien ; $i++ ) {
            $q->or_where( 'country', $_POST["select"][$i] );
        }
    }       
})

Many thanks for that. Yet, if I use further conditions (which work if I use them without the loop above) like ...

->where( 'id', $_POST["minID"], '>' )
->where( 'id', $_POST["maxID"], '<' )
->where( 'id', $_POST["minRange"], '>' )
->where( 'id', $_POST["maxRange"], '<' )
->where( 'last_updated_date', $_POST["minDate"], '>' )
->where( 'last_updated_date', $_POST["maxDate"], '<' )

the loop still works, but then the other conditions won't work (they do filter, but only some very limited filtering is done). Is it possible to use the loop and all the other conditions together?

allan

Good to hear you are getting closer. The issue now is likely the grouping - you need to group the OR conditions together. For that you can use the where_group method.

->where( function ($q) {
    $q->where_group( true );
    ... condition
    $q->where_group( false );     
})

for example.

Allan

Capamania

Brilliant! It's working like a charm, thanks a lot!