SQL error when $editor->where condition in the loop and no value selected

Capamania

I have another issue with the $editor->where condition in a loop below. Based on https://datatables.net/forums/discussion/33433/sending-multiple-values-via-ajax-data-to-editor-where-end-looping-through-where

If no value is selected or if I deselect all of them, I get the following error message:

DataTables warning: table id=my-table - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')AND(status = 'ACTIVE' )AND id > '0' AND id < '500000' AND id > '' at line 1

How can I prevent this error and this message? And is it safe to prevent this message but still have this error? Or how can I render all values if no option is selected or if I deselect all (and as a default)? This is the loop ...

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

Capamania

With ...

$.fn.dataTable.ext.errMode = 'throw'; 

I can change DataTables' error reporting mechanism to throw a Javascript error to the browser's console, rather than alerting it. https://www.datatables.net/manual/tech-notes/7

But is this the best solution? Or how can I render all values if no option is selected or if I deselect all (and as a default)?

allan

Changing the error mode isn't the solution here - there is still an error occurring which you want to avoid.

This is the code where where_group was used - is that correct? If so, only use where_group if $_POST['select'] is submitted would appear to be the solution.

Allan

Capamania

Hi Allan, yes that's correct. The error occurs only when I deselect all values within the where_group e.g.:

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

I thought about this. But how exactly can I "only use where_group if $_POST['select'] is submitted"? If I select no values, ajax.data: function (d) { d.select = $('.select-status').val(); } still sends an empty array to $_POST and this causes the error ... correct?

allan

How about:

if ( is_array( $_POST["select"] ) ) {
  $r->where_group( true );
  ...
}

Allan

Capamania

Perfect! :-) Working like a charm! Thanks a lot!